Trending SQL Server Tips

How to Backup an Analysis Services Database Using SQL Server Management Studio

By: Editor
March 10, 2010


In this article we will take a look at the steps which a database administrator needs to follow to backup an analysis services database. The steps mentioned below are applicable on SQL Server 2005 and higher versions. Read the following article titled "How to Backup Database in SQL Server" to know the steps you need to follow to backup an SQL Server Database.

Recommendation: Become an Analysis Service Expert: Learn from SQL Server Analysis Services Administration Articles, Tips, News, Tools, How to, WhitePapers etc

Backup Analysis Services Database Using SQL Server Management Studio

1. Start SQL Server Management Studio and connect to an analysis services instance. Right click the analysis services database which you want to backup and choose the option Back Up…. from the popup menu as shown in the snippet below.

Backup Analysis Services Database Using SQL Server Management Studio

2. In the Backup Database dialog box, there are a number of settings which you have to choose to configure the analysis services database backup.

Analysis Services Backup Database

3. Click the Browse… button to provide the location where you need to save the analysis database backup file and click OK to save the backup location.

4. In the Backup Database dialog box there are options available to Allow file overwrite, Apply compression and to Encrypt backup files. If you choose to encrypt the backup file then you need to choose Encrypt backup file option and specify a password. Finally click OK in the Backup Database dialog box to start the analysis services backup. Important thing you need to note is it will backup metadata for ROLAP partitions, metadata and aggressions for HOLAP partitions, metadata, source data and aggressions for MOLAP partitions. This is because backup is not applied on the source data contained in the relational database.


In this article you have seen how easily you can take a backup of analysis services database using SQL Server Management Studio.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…

Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect you privacy and you can unsubscribe at any time."