How to restore a SQL Server Analysis Services Database

By: Ashish Kumar Mehta   |   Comments (5)   |   Related: > Analysis Services Administration

Problem

One of the Junior DBA in my company approached me yesterday with a dilemma. He was assigned a task to restore an Analysis Service SQL Server 2008 Database on a development server. At first I started to tell him, but figured it would be smarter to document the steps which he needs to perform to successfully restore an analysis service database from an encrypted analysis service database backup on an SQL Server 2008 Analysis Services Instance.

Solution

In this tip, you will go through the steps which a DBA needs to perform to successfully restore an analysis service database to a SQL Server 2008 Analysis Service Instance.  I will show you how this can be done via SSMS and also using an XMLA query.

Restoring Analysis Service Database using SQL Server Management Studio GUI

1. Connect to SQL Server 2008 Analysis Service Instance using SQL Server Management Studio
2. In Object Explorer, right click the Databases node and select the Restore... option from the drop down list as shown in the snippet below

3. In the Restore Database window, you need to click the Browse... button next to Backup file text box under Restore Source; this will open up the Locate Database Files window as shown in the below snippet.

4. In the Locate Databases Files window, you need to specify the path where the analysis service database backup file is stored, in our case ProductAnalysisDB, as shown in the below snippet and click OK to save the Restore Source information.

5. Next within the Restore Database window, you need to click the Browse... button next to Storage Location text box under Restore Target; this will open up Browse for Remote Folder window as shown in the below snippet. Here, you need to select the path where the Analysis Service database needs to be restored; click OK to save the changes and to return to the Restore Database window.

6. Finally, you need to choose one of the options Allow database overwrite or Overwrite security information. If you have an encrypted backup of the analysis service database, then you need to provide the encryption password else this filed can be left blank. Click OK to go ahead and restore the Analysis Service Database.

7. Once the Analysis Service database is successfully restored you can make necessary configuration changes by changing the Data Source property of the restored database.

Restore Analysis Service Database Using XMLA Query

1. Connect to SQL Server 2008 Analysis Service Instance using SQL Server Management Studio
2. In Object Explorer, right click Analysis Server Node and select New Query ... XMLA option from the drop down list as shown in the snippet below

3. This will open up XMLA query window where you can copy and paste the below mentioned XML code and execute the same to perform the restore of ProductAnalysisDB analysis service database. You will need to change for your database.

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>C:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Backup\ProductAnalysisDB.abf</File>
<DatabaseName>ProductAnalysisDB</DatabaseName>
<AllowOverwrite>true</AllowOverwrite>
<Password>Backup</Password>
<DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">C:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Data\</DbStorageLocation>
</Restore> 

4. Once the analysis service database is successfully restored you will be able see the below mentioned success message.

Executing the query ...
<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>C:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Backup\ProductAnalysisDB.abf</File>
<DatabaseName>ProductAnalysisDB</DatabaseName>
<AllowOverwrite>true</AllowOverwrite>
<Password/>
<DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">C:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Data\</DbStorageLocation>
</Restore>
Restore started.
Restore finished.

Next Steps

• Read these other Analysis Services tips
• Refer to these related tips:
  • Automating Backups for SQL Server 2005 Analysis Services Databases

About the author

Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips