Backup and Restore of an Analysis Services Database

Restoring Analysis Services Database Using XMLA Command

1.       Connect to the Analysis Services Instance using SSMS.

2.       Right click the analysis services instance and choose the New Query… XMLA option from the drop down list as shown in the snippet below.

3.       Copy and Paste the XMLA command which was generated when you clicked on the Script Action to New Query Window option in the Restore Database window in the XMLA query window. Finally execute the command by pressing F5 to restore the analysis services database.

<Restore xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>

  <File>D:SSASBackupsAdventure Works DW 2008.abf</File>

  <DatabaseName>Adventure Works DW 2008</DatabaseName>

  <AllowOverwrite>true</AllowOverwrite>

  <DbStorageLocation xmlns=”http://schemas.microsoft.com/analysisservices/2008/engine/100/100″>D:Program FilesMicrosoft SQL ServerMSAS10.SQL2008OLAPData</DbStorageLocation>

</Restore>

Conlusion

In this article you have seen how to backup and restore an analysis service database using SQL Server Management Studio or by using XMLA command. It is always good to take the backup of analysis services database once you have completed the processing of a  cube.