mssqltips logo

SQL Server Analysis Services Backup and Restore



By:
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.

Execute a SQL Server Analysis Services backup in Management Studio

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

Review the SSAS backup file in the specified 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.

Last Update: 4/7/2016




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, July 23, 2018 - 10:59:45 AM - Prathap Back To Top

Hi,

Can someone please let me know on how to verify SSAS database restore history and which file used?

Regards,

Prathap.



download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools