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
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 2000.cab"
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
Article ID: 294287 - Last Review: February 28, 2014 - Revision: 4.3
- Microsoft SQL Server OLAP Services
- Microsoft SQL Server 2000 Analysis Services
|kbnosurvey kbarchive kbinfo KB294287|