How to Restore Analysis Services Database in SQL Server Using SQL Server Management Studio

Click here to read “How to Restore Analysis Services Database in SQL Server Using SQL Server Management Studio” article from the beginning.

Step 5: The Restore Storage Location can be specified by click the Browse… button in the Restore Database Dialog Box next to Storage Location. This will open up Browse for Remote Folder dialog box as shown in the snippet below. In Browse for Remote Folder dialog box, you need to specify the location where you would like to save the analysis services database. Once the SSAS database restoration path is specified click OK to save the changes and to return to the parent Restore Database Dialog Box.

Step 6: Under Options of Restore Database dialog box, there will be two options available namely Allow database overwrite and Overwrite security information. Select the check box for Allow database overwrite in case you want to over write the existing analysis services database. Select the check box Overwrite security information and choose Copy All from the drop down list to overwrite the security settings for the cubes with that of the settings in the analysis services backups.

Step 7: If at all your analysis services backup was encrypted during the time of backup then you need to provide the password under Encryption to decrypt the backup file and to restore the analysis services database successfully.

Step 8: Finally, click OK to restore the Analysis Services Database. In the snippet below you could see that AdventureWorksDW database is successfully restored.

Trending Analysis Services Articles and Tips

• How to Detach SSAS Database in SQL Server
• How to Attach SSAS Database in SQL Server
• How to Synchronize Analysis Services Database Using Synchronize Database Wizard in SSMS
• How to Restore Analysis Services Database in SQL Server Using SQL Server Management Studio
• How to Automate Backup of Analysis Services Database Using SQL Server Agent Job
• How to Backup Analysis Services Database in SQL Server Using SQL Server Management Studio
• Database Consistency Checker DBCC CHECK For Analysis Services Database in SQL Server 2016 for Tabular and Multidimensional Databases

XMLA Script to Restore an Analysis Services Database

You can generate the XMLA script to restore an analysis services database once all the options are configured successfully by clicking Script Action to Clipboard under Script as shown in the below snippet. Or else you can click CTRL + SHIFT + C to generate the script action to clipboard.

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

Recommendation: Perform Database Consistency Checker (DBCC CHECK) for Analysis Services Database in SQL Server 2016 for both Tabular and Multidimensional Databases after successfully restoring the SQL Server Analysis Services (SSAS) Database to ensure there are no corruption issues .

Conclusion

In this article you have seen how easily you can restore an analysis services database using SQL Server Management Studio.