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:
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 2000.cab""" ' 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
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.
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.
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.
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