:::: MENU ::::

Backup and Recovery plans for SQL Server Analysis Services

  • May 13 / 2009
  • 0
Analysis Services SSAS, Backup and Restore, dbDigger

Backup and Recovery plans for SQL Server Analysis Services

Before you back up your Analysis Services data, you must ensure that Analysis Services is not processing any dimensions, partitions, or mining models. Because Analysis Services performs some processing tasks as background processes, determining when all processing has been completed can sometimes be difficult. Also, you must ensure that another administrator is not changing any of the meta data while you are performing a backup. One way to ensure Analysis Services is quiescent is to stop Analysis Services before you perform the backup
Backup Options Analysis Services provides two techniques for backing up an Analysis Services database: archiving and copying files.


You can archive an Analysis Services database and the repository to one or more .cab file using the msmdarch command (msmdarch.exe), either from within Analysis Manager or from a command prompt. Msmdarch uses .cab storage algorithms, which limits the size of any single .cab file to 2 GB. As a result, no individual file in the Data folder (such as any single partition) can exceed 2 GB, or else msmdarch cannot be used for backup.
When using msmdarch, always specify a log file location to capture any messages generated during the archive process. If the archive process fails, these messages can help you determine why the archive process failed. However, msmdarch does not back up the query log. To back up the query log, perform a file-based backup of the SMDQLOG.mdb file. If you do not, a new query log is created from scratch when you start a restored instance.

Copying Files

You can use Analysis Manager to copy the meta data for an Analysis Services database from one instance of Analysis Services to another Analysis Services instance, provided that both instances are registered in Analysis Manager. Because only the meta data is copied to the target server using this method for release management, you will need to process the Analysis Services database on the destination server (after updating the data source properties, if necessary) before users can query the data in the new location. Because copying and pasting is so easy and quick, and because you are frequently only working with a subset of data in the development environment, copying and pasting is generally the quickest way to deploy an Analysis Services database on a different server. The downside is that it requires all of the dimensions, cubes, and partitions to be processed (which must be incurred anyway if the data sets are different). To determine your preferred approach, you need to compare the time and overhead of fully reprocessing the database with the msmdarch.exe archive and restore time. In most cases, full reprocessing is the fastest method. But you will find that it varies based on the underlying infrastructure.

If you cannot use msmdarch, you can use a file copy program, such as Windows Backup, to back up all the files in the Data folder. With a file copy backup, you back up all databases on the server. With msmdarch, you can back up a single Analysis Services database. In addition, the file copy technique does not back up the repository or the query log file. If you use the file copy technique, you must back up the repository at the same
time you back up the Data folder, to ensure that the repository and the data in the Data folder remain synchronized. You must also back up the query log (the SMDQLOG.mdb file), or begin capturing query information from scratch. While it is possible to back up just an individual database (by copying the contents of its Data sub folder and the .DBO file). You cannot back up individual portions of the repository. The repository is needed on a full restore, or if the meta data has changed since the last backup.

File-Based Backup and Restore

If neither of the previous two methods is suitable for your situation, you can also use a file-based backup program to back up the entire Data folder and then restore it to the destination folder. With this method, you must deploy all databases within an Analysis Services instance, rather than a single database. If you use this method, you must also back up the repository and then restore the repository on the destination server. While the repository is technically not required to run the OLAP service, it is required to run Analysis Manager and thus to properly administer the server. The meta data in the repository must match the contents and structure of the Data folder. If you have data in remote partitions (a feature that is rarely used) and in writeback tables, you must first back up the data in remote partitions using a file-based backup method and the writeback tables using SQL Server backup, and then restore them before you bring your database back online.

Consult us to explore the Databases. Contact us