SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

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.

How to Restore Analysis Services Databases

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.

Restore OLAP Database in SQL Server

Trending Analysis Services Articles and Tips

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.

XMLA Script to Restore an Analysis Services Database
<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.

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Add comment

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement