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

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

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 “How to Backup Database in SQL Server” to know the steps you need to follow to backup an SQL Server Database and to understand different types of database backups supported in SQL Server.

Steps to Backup Analysis Services Database Using SQL Server Management Studio

Step 1: Start SQL Server Management Studio and connect to the 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 Service Database in SQL Server Using SQL Server Management Studio

Step 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 database backup in SSMS

Learn How to Automate Backup of Analysis Services Database Using SQL Server Agent Job

Trending Analysis Services Articles and Tips

Step 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.

BackUp OLAP Database in SQL Server

Step 4: In the Backup Database dialog box there are options available to Allow file overwriteApply 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.

Chetna Bhalla


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.