Migrating a cube in SQL Server Analysis Services 2005 to SQL Server Analysis Services 2008

You should consider migrating your cubes with one of 2 methods below:
1. Method Backup / Restore (recommended)
2. By copying the DATA directory on the second server while ensuring the SSAS service is stopped on both servers.

CautionYou can not move your cube (SQL Server Analysis Services 2005) via a sync to your new server (SQL Server Analysis Services 2008). If you try, you get the error message: Errors in the OLE DB provider. The server returned the following error: (The BackupLog element at line 1, column 904 (namespace ) cannot appear under Envelope/Body/Execute/Command/InternalSynch.)Backup and restore errors: An error occurred while synchronizing the MyDB' database, Database synchronization failed. . The synchronization works only with the same versions of SQL server and the same service pack level.


Example with the method Backup/Restore:

Step 1 : Backup of the database on the instance SSAS 2005

Method using the Wizard
- In Management Studio, connect to your instance SSAS 2005
- Click right on database backups
- Then click on « Back up… »
- Then select the location of the backup file and click OK

Method using a script XMLA
- In Management Studio, connect to your instance SSAS 2005
- Click on "File" >> "New" >> "Analysis Services XMLA Query"
- Then execute the code below :

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<
Object>
<
DatabaseID>MyDB</DatabaseID>
</
Object>
<
File>C:\MyDB.abf</File>
</
Backup>


Step 2 :
Move the file. Abf to the server that hosts your new instance SSAS 2008

Step 3 : The last step is to restore the backup file in step 1.

Method using the Wizard
- In Management Studio, connect to your instance SSAS 2008
- Click right on «Databases »
- Then click on « Restore… »
- Then select the location of the file to restore and click OK

Method using a script XMLA
- In Management Studio, connect to your instance SSAS 2008
- Click right on "File" >> "New" >> "Analysis Services XMLA Query"
- Then execute the code below :

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<
File>C:\MyDB.abf</File>
<
AllowOverwrite>true</AllowOverwrite>
</
Restore>

For more information: Managing Backing Up and Restoring (Analysis Services 2008)

Michel Degremont| Microsoft EMEA
Product Support Services Developer -SQL Server Core Engineer |