FIX: How to Migrate Repository from Meta Data Format to SQL Server Database Format

For each Analysis server, Microsoft(r) SQL Server(tm) 2000 Analysis Services creates a repository, called the Analysis Services repository, to store meta data for the objects of the Analysis server (cubes, dimensions, and so on). By default, this repository is a Microsoft Access (.mdb) database on the server computer where Analysis Services is installed.

You can use the Migrate Repository Wizard to migrate the Analysis Services repository to a Microsoft SQL Server 7.0 database or a SQL Server 2000 database on the same server, another server, or to the SQL Server 2000 Meta Data Services repository format. Microsoft recommends that you choose the SQL Server database format if you are planning to migrate the repository.

However, if you have already migrated the repository to the Meta Data Services repository format and you are now planning to migrate it back to a SQL Server 7.0 OLAP Services format, you must follow the steps outlined in the "More Information" section.

To resolve this problem, obtain the latest service pack for the Microsoft SQL Server 2000 (the Analysis Services Components - Sql2kasp1.exe). For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

Microsoft has confirmed that this is a problem in SQL Server 2000 Analysis Services version 8.0. This problem was first corrected in the Analysis Services Components of Microsoft SQL Server 2000 Service Pack 1.

How to Migrate Repository to SQL Server 7.0 OLAP Services Format

1. Make sure that you make a backup of your msdb database if you used the default database. Otherwise, back up the database that you used to host your repository.
2. Make sure that you archive each individual OLAP database. If you have a database that contains a data file larger than 2 gigabytes (GB), refer to the following article in the Microsoft Knowledge Base:
   249698  (http://support.microsoft.com/kb/249698/EN-US/ ) FIX: OLAP Addins: Archive Fails with More than 2 GB of Data
3. Make sure that the OLAPObjects table exists, and that it also has no rows.
4. Make sure that no other users are connected to the Analysis Server server.
5. Create or identify the SQL Server database to which you want to migrate the Analysis Services repository.
6. Open Analysis Manager.
7. In the Analysis Manager tree pane, expand the Analysis Servers folder.
8. Right-click the server whose Analysis Services repository you want to migrate, and then click Migrate Repository.
9. Follow the Migrate Repository wizard to migrate your repository to SQL Server 7.0 OLAP Services format, and then select the database that you created or identified in step 8.

A known issue exists regarding the migration of an Analysis Services repository from Meta Data format to SQL Server format when the target database is in SQL Server 7.0. For additional information, click the article number below to view the article in the Microsoft Knowledge Base: