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

MORE INFORMATION

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

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