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