INF: How to Automate an Archive or Backup of OLAP Database

This article was previously published under Q294287
This article has been archived. It is offered "as is" and will no longer be updated.
Microsoft SQL Server 2000 Analysis Services provides functionality to archive and restore Analysis Services databases. Each database is archived to one or more .cab files, which you can reserve for restoration requirements or migrate to other server computers. To automate the archiving of the OLAP database, you can use the msmdarch command.
The following command archives the sample FoodMart 2000 database included in Analysis Services:
"\Program Files\Microsoft Analysis Services\Bin\msmdarch" /a myserver "\Program Files\Microsoft Analysis Services\Data\" "FoodMart 2000" "\My archives\server myserver\FoodMart"				
If you use the /r switch instead of the /a switch in the preceding command line, you restore the Foodmart 2000 database to myserver.

You can also execute the msmdarch command from a .bat file. If you want to schedule execution of a .bat file, Microsoft recommends that you use Data Transformation Services (DTS) to create an Execute Process task that runs the batch file. The msmdarch command returns an exit code of 1 if it fails. If you choose to use DTS, please make sure to pay attention to the following items to avoid the two most common mistakes in scheduling a DTS package to perform the task:
  • Start the SQLServerAgent service on an account that is a member of the local OLAP Administrators group of the server computer.
  • Type the msmdarch command on a single line. Do not use any hard carriage returns between characters.
If you are looking for a way to programmatically archive or restore your databases, the following Microsoft Visual Basic code example shows how you can shell out to execute the msmdarch command to archive or restore an Analysis Services database. There is no return code; use and check the SQL Server error log file to track success or errors:
    Dim Servername As String    Dim DataPath As String    Dim ExePath As String    Dim CabFileName As String    Dim DatabasenName As String    Dim LogFileName As String    Dim Execstr As String    Servername = "ServerName"    DataPath = """C:\Program Files\Microsoft Analysis Services\Data\"""    ExePath = """C:\Program Files\Microsoft Analysis Services\Bin\"    DatabaseName = ""                         ' leave this blank when restoring    CabFileName = """C:\temp\FoodMart"""  ' Backup File    LogFileName = """C:\temp\FoodMart 2000.log"""  ' LogFile    Execstr = ExePath & "MSMDARCH.EXE"" /r " & Servername & " " & DataPath & " " & DatabaseName & " " & CabFileName & " " & LogFileName    Shell Execstr, vbHide				
As an extra precaution, Microsoft recommends that you use the following backup procedure in addition to using the archive and restore utilities. Before you perform the archive and restore, make sure that no one is connecting to the Analysis Server server, and that the MSSQLServerOLAPService service is stopped.

Steps to Backup Analysis Services Database

  1. To back up your Analysis Services databases, make a backup copy of your Analysis Services\Data folder, which is installed by default under the C:\Program Files folder.
  2. If you have not migrated the repository, please make a backup copy of the Msmdrep.mdb file that is located in the Analysis Services\Bin folder.
  3. If you have migrated the repository to SQL Server 7.0 OLAP Services format, use the SQL Server backup utility to back up your repository database.
  4. Save a copy of your Analysis Services registry entries by running regedit, and by using the Export Registry File item on the Registry menu to export this key to a backup file: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server
suggest suggestion recommendation OLAP

Article ID: 294287 - Last Review: 02/28/2014 04:27:03 - Revision: 4.3

  • Microsoft SQL Server OLAP Services
  • Microsoft SQL Server 2000 Analysis Services
  • kbnosurvey kbarchive kbinfo KB294287