How to back up and restore Analysis Services databases

Here I'll discuss backup and restore functionality in both MSAS 2000 and MSAS 2005.

Archiving and restoring cubes using Analysis Manager

Why would you want to back up cubes (multidimensional data representations)? MSAS database files can be damaged by hardware failure, so having a recent backup is prudent. Recreating cubes and populating them from relational data sources would take considerably more time than restoring an MSAS database from a backup. Furthermore, you could copy your production cube backups to a development or test environment to troubleshoot issues and develop enhancements.

Within MSAS 2000, backup functionality is referred to as database archiving. To back up a database, right click the database (not an individual cube) within Analysis Manager and choose Archive Database. MSAS lets you save your backup to a location you specify. To restore a previously archived MSAS database, right click on the server within Analysis Manager and choose Restore Database.

Occasionally you will need to restore the MSAS database backup with a different name. For example, you may find it handy to have two copies of the same database on the same server. This way you can make changes to the database copy that only you can access before modifying the production cubes.

For example, suppose you use Foodmart database on your production server and you want to restore a recent backup on a development server. To differentiate between the development and production copies of the same database, you want the two versions to have different names. Unfortunately, MSAS 2000 won't let you restore a backup to a database with a different name. To work around this limitation follow these steps:

1. Right click on the existing Foodmart database on the development server and choose copy.
2. Right click on the same Analysis Server and choose paste.
3. Analysis Manager will detect that a database with the same name already exists on the current server. You will be asked to specify a different name for a copy of the database. At this point you can specify name such as "Foodmart-Development" to create a copy.
4. Restore the production copy of Foodmart (as "Foodmart").

Automating MSAS database backups

I've shown you how to back up and restore cubes manually using Analysis Manager. But what if you process your cubes at night? You may not wish to stay up and take a manual backup after cube processing is complete. Fortunately, there is a way to automate cube backups. You can use MSMDARCH command to back up the MSAS database from the command line or from a batch file. The executable file MSMDARCH.exe is found in BIN directory under Analysis Services installation folder, so you will have to change your path to "D:\ Program Files\Microsoft Analysis Services\Bin" (if MSAS is installed on D drive) before calling this command.

The syntax of MSMDARCH is shown below:

msmdarch /a server_name "path to the MSAS data directory" "database_name" "backup_file path and name" "log file name" "temp directory path"

Specifying log file name and temporary directory path is optional.

Let's suppose my server name is NewServer and I'd like to make an archive of MyTestDatabase. I would execute the following command:

"d:\program files\Microsoft Analysis Services\bin\msmdarch" /a NewServer "g:\Microsoft Analysis
 Services\Data" "MyTestDatabase" "I:\MyTestDatabase.cab"

This command would generate a backup file on "I:" drive called MyTestDatabase.cab.

To automate a backup, you can create a batch file that contains a command similar to the one above. Then create a data transformation services (DTS) package that calls the batch file through Execute Process -- a task that only needs to know where the batch file is saved. Finally, you would schedule the DTS package to execute as often as needed.

Back up and restore in MSAS 2005

Analysis Services 2005 offers numerous improvements over its predecessor, including simplified backups. Backup, restore and rename functionality is available directly in SQL Server 2005 Management Studio, a single tool to replace Enterprise Manager, Query Analyzer and Analysis Manager.

To change the existing database name, simply right click MSAS database in Management Studio and choose Rename. In addition, you can invoke backup and restore options from the right-click menu. Each option opens a respective dialog where you can specify the details of backup or restore.

You can easily script the BACKUP or RESTORE statement from a Backup Database/Restore Database dialog. Analysis Services 2005 even allows you to encrypt and compress backup files. Both backup and restore statements are implemented through XML for Analysis (XMLA) and look similar to the following:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>test</DatabaseID>
  </Object>
  <File>c:\test.abf</File>
  <ApplyCompression>false</ApplyCompression>
</Backup>

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <File>c:\test.abf</File>
  <DatabaseName>NewTest</DatabaseName>
</Restore>

Note that backup files have the extension of ABF (Analysis Backup File) with MSAS 2005. You can schedule BACKUP/RESTORE commands to execute on schedule using SQL Server jobs. SQL Server 2005 has a new job step type -- Analysis Services command -- which allows you to execute XMLA statements, including BACKUP and RESTORE.

About the author: Baya Pavliashvili is a DBA manager with HealthStream Inc. -- a leader in online healthcare education. In this role, he oversees database operations supporting more than one million users. Pavliashvili's primary areas of expertise include performance tuning, replication and data warehousing. He can be reached at baya.pavliashvili@healthstream.com.