Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to restore a SQL Server Analysis Services Database


By:   |   Last Updated: 2009-12-22   |   Comments (4)   |   Related Tips: > 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



Last Updated: 2009-12-22


next webcast button


next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, June 24, 2015 - 10:30:05 AM - Paresh Motiwala Back To Top

thnx for this information. This was really useful. 

However, these days lot of backup products are based on quiescing a  db or a file system. 

so question is, how would I be able to take the files of a database by quiescing them, copy them to a different server and then attach them at the new location with a script>

Has anybody been able to work this ?


Monday, May 04, 2015 - 1:35:16 PM - Sam Back To Top

Hello,

After talking to a lot of folks and researching on how to create analysis service database in MS Sql Server 2014 I came across your acidently!

It truly worked and this is exactly I was loooking for. A million thanks for the help and keep writing such a hard to find good technical tips. 

Unfortunately, those who call themselves an author of the book does shitty job when they write the book but here you did provide what I was mising in an expensive books.

Truly GREAT JOB.

Sam

 


Friday, November 07, 2014 - 12:07:04 AM - Anoop Back To Top

I am getting an error backup store version 150 is not supported when trying to restore .abf file. I am trying to restore it on SQLSERVER2008R2. I'm not sure which version is the backup since I have got the zip file. please let me know what can be the issue.


Tuesday, April 17, 2012 - 6:56:10 PM - Prema Back To Top

A cube was incorrectly restored on top of another cube. Can the data be restored using the data directory.  If so what are the steps. Please help.


Learn more about SQL Server tools