Backup your SQL Server Analytical Database - Part I

December 7, 2005

After the release of SQL Server 7.0, SQL Server is not just an RDBMS. It also provides services like Extraction Transformation and Loading, Online Analytical processing etc. This evolution of SQL Server added additional responsibilities to the database administrative tasks.

One of the many tasks of the SQL Server Database administrator is to take and maintain backups of SQL Server databases. This includes taking backups of analytical databases as well.

This article demonstrates how to backup an Analysis service database.


a.  The Client portion of SQL Server 2000 Analysis Service should be installed on the machine where we are going to run this batch file. [Refer Fig 1.1 and 1.2]

Click for larger image

Fig 1.1

b. Make sure the msmdarch.exe file is installed. [Refer Fig 1.2]

Click for larger image

Fig 1.2

c. The account used to run the script should have access to the data folder of the analysis service machine.

Method 1

Method 1 illustrates how to backup an analysis database manually.

Step 1 Let us use the MS-DOS prompt and navigate to the following folder:

C:\Program Files\Microsoft Analysis Services\Bin>

Step 2 Type and execute the following command. Refer [Fig 1.3]

msmdarch.exe /?

Click for larger image

Fig 1.3

Once executed, you would see the following message. [Refer Fig 1.4]

Click for larger image

Fig 1.4

Step 3

Create a folder C:\BACKUP, as shown in Fig 1.5.

Fig 1.5

Step 4

Execute the following command.

Msmdarch.exe /A VMAnalysis "\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data" 
olapdb_manual c:\backup\olapdb_manual_bak


VMAnalysis is the Analysis server name

"\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data” is the data folder

olapdb_manual is the OLAP database

c:\backup\ is the backup folder

olapdb_manual_bak is the backup file name

This command would take a backup of the OLAP database olapdb_manual from the analysis server, VMAnalysis. [Refer Fig 1.6]

Fig 1.6