SQL Server Analysis Services Backup and Restore

By: Siddharth Mehta

Overview

Given that SQL Server Analysis Services (SSAS) is installed and configured, security is fine-tuned, logging is active, and users have been provisioned with the deployed databases, the next logical task in an administrator's task list would be backups and restores of databases. In this chapter we are going to discuss how to complete SQL Server Analysis Services database backups and restores.

Explanation

Before we begin discussing backup and restore in SSAS, there are a set of properties which are necessary for an administrator to be aware of. These properties are related to different directories that are used as paths for different purposes. Below is a list of the directories. These properties are editable from the SSAS instance properties tab in SQL Server Management Studio (SSMS).

• AllowedBrowsingFolders - This property holds the list of paths that would be available for browsing through SSMS to host its data on the disk. Any other paths would not be accessible via any dialogs on SSMS for any SSAS data storage operation.
• DataDir - This property holds the default path where the files would be stored.
• LogDir - This property holds the default path for log files in general.
• TempDir - This property holds the default path for the temp files that SSAS creates during processing of database objects.
• BackupDir - This property holds the list of paths which would be allowed to store backup files. So make sure this path can be browsed from the source and destination server otherwise you would have to manually copy the backup files to the backup folders of the respective server.

SSAS Database Backup

Creating a backup is relatively a simple process in SSAS, much like we backup relational databases in SQL Server in SSMS. Right-click on the SSAS database in the object explorer window of SSMS, and select Back Up. This should bring up a window as shown in the below screenshot. Provide the backup file name, and select the options for compression as well as encryption. Provide a strong password if you are selecting the option to encrypt the backup file. Press OK and the backup file should get created with an *.abf file extension in the selected backup directory.

Once the backup has been created, you should be able to see the same file in the backup directory.

SSAS Database Restore

To restore the SSAS database, right-click on the SSAS database in Object Explorer window of SSMS, and select Restore. This should bring up the dialog as seen in the below screenshot. Select the relevant options for the file being restored and once done click the OK button, and you should have the backed-up database restored on the target server.

Additional Information

• For a detailed information on each and every database backup and restore option, considering reading this article.