Backup your SQL Server Analytical Database – Part I

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.

Pre-requisite

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]

b. Make
sure the msmdarch.exe file is installed. [Refer 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 /?

Once
executed, you would see the following message. [Refer 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

Where

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

Latest Articles