Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 7, 2005

Backup your SQL Server Analytical Database - Part I

By Muthusamy Anantha Kumar aka The MAK

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]

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


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM