Backup your SQL Server Analytical Database - Part I

Wednesday Dec 7th 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

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. Read on to learn how to backup an Analysis service database.

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]

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

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

Method 2

Method 2 illustrates how to backup multiple analytical databases manually.

Step 1

Let us assume the Analysis server, VMAnalysis, has the following databases that are to be backed up daily. [Refer Fig 1.7]


Fig 1.7

Let us create the following folder C:\OLAPBackup. [Refer Fig 1.8]

Click for larger image

Fig 1.8

Step 2

Let us create C:\OLAPBackup\databases.txt and copy and paste the following database names into it. [Refer Fig 1.9]

OLAPCubeManual
OLAPDatabase
OLAPDB_Manual
OLAPDB_Manual2
OLAPHierarchy

Click for larger image

Fig 1.9

Step 3

Let us create C:\OLAPBackup\backupolap.bat and copy and paste the below code into the file. [Refer Fig 2.0]

Please download backupolap.bat_

REM Backup OLAp databases
REM Created by : MAK
REM Created Date: Nov 19, 2005
for /f "tokens=1" %%i in (C:\OLAPBackup\databases.txt)  do "C:\Program  
Files\Microsoft Analysis Services\Bin\msmdarch.exe" /A VMAnalysis  
"\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data" %%i  
c:\backup\%%i_bak

Click for larger image

Fig 2.0

Step 4

When this batch file C:\OLAPBackup\backupolap.bat is executed, it backs up all of the databases listed in the databases.txt to C:\backup folder. [Refer Fig 2.1 and 2.2]


Fig 2.1


Fig 2.2

This also stores the log information on to C:\OLAPBackup\log.txt as shown below.

C:\OLAPBackup>REM Backup OLAp databases 
C:\OLAPBackup>REM Created by : MAK 
C:\OLAPBackup>REM Created Date: Nov 19, 2005 
C:\OLAPBackup>for /F "tokens=1" %i in (C:\OLAPBackup\databases.txt) 
do "C:\Program Files\Microsoft Analysis Services\Bin\msmdarch.exe" 
/A VMAnalysis "\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data" 
%i c:\backup\%i_bak 
C:\OLAPBackup>"C:\Program Files\Microsoft Analysis Services\Bin\msmdarch.exe" 
/A VMAnalysis "\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data" 
OLAPCubeManual c:\backup\OLAPCubeManual_bak 
   placed file 'OLAPCubeManual\MyCube3.mdl_' (size 3112) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Calendar.Time.dim_' (size 3970) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Calendar.Time.dimcr_' (size 30) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Calendar.Time.dimprop_' (size 9) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Calendar.Time.dimtree_' (size 52928) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Mak Inc. Product.dim_' (size 2006) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Mak Inc. Product.dimcr_' (size 30) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Mak Inc. Product.dimprop_' (size 9) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Mak Inc. Product.dimtree_' (size 360975) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Mak.Store.dim_' (size 2412) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Mak.Store.dimcr_' (size 30) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Mak.Store.dimprop_' (size 2925) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3^Mak.Store.dimtree_' (size 3034) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\sql2000 - FoodMart.src_' (size 406) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3\MyCube3.1.fact.map_' (size 6120) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3\MyCube3.2.fact.map_' (size 25654) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3\MyCube3.3.fact.map_' (size 178) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3\MyCube3.fact.data_' (size 49010) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3\MyCube3.pdr_' (size 197) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPCubeManual\MyCube3\MyCube3.prt_' (size 4802) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPDB.REP' (size 12974) on cabinet 'OLAPCubeManual_bak.CAB_'
   placed file 'OLAPDB.INF' (size 1493) on cabinet 'OLAPCubeManual_bak.CAB_'
C:\OLAPBackup>"C:\Program Files\Microsoft Analysis Services\Bin\msmdarch.exe" 
/A VMAnalysis "\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data" 
OLAPDatabase c:\backup\OLAPDatabase_bak 
   placed file 'OLAPDatabase\Cube1.mdl_' (size 2880) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Product.dim_' (size 2502) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Product.dimcr_' (size 30) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Product.dimprop_' (size 9) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Product.dimtree_' (size 76484) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Region.dim_' (size 2614) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Region.dimcr_' (size 30) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Region.dimprop_' (size 9) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Region.dimtree_' (size 19578) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\SQL2000 - foodmart.src_' (size 440) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Time.dim_' (size 3874) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Time.dimcr_' (size 30) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Time.dimprop_' (size 9) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Time.dimtree_' (size 256601) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Cube1\Cube1.1.agg.rigid.map_' (size 0) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Cube1\Cube1.1.fact.map_' (size 472) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Cube1\Cube1.2.agg.rigid.map_' (size 0) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Cube1\Cube1.2.fact.map_' (size 6850) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Cube1\Cube1.3.agg.rigid.map_' (size 0) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Cube1\Cube1.3.fact.map_' (size 9054) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Cube1\Cube1.agg.rigid.data_' (size 13429) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Cube1\Cube1.fact.data_' (size 44406) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Cube1\Cube1.pdr_' (size 1284) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDatabase\Cube1\Cube1.prt_' (size 12938) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDB.REP' (size 17877) on cabinet 'OLAPDatabase_bak.CAB_'
   placed file 'OLAPDB.INF' (size 1511) on cabinet 'OLAPDatabase_bak.CAB_'
C:\OLAPBackup>"C:\Program Files\Microsoft Analysis Services\Bin\msmdarch.exe" 
/A VMAnalysis "\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data" 
OLAPDB_Manual c:\backup\OLAPDB_Manual_bak 
   placed file 'OLAPDB_Manual\sql2000 - FoodMart.src_' (size 406) on cabinet 'OLAPDB_Manual_bak.CAB_'
   placed file 'OLAPDB.REP' (size 5582) on cabinet 'OLAPDB_Manual_bak.CAB_'
   placed file 'OLAPDB.INF' (size 185) on cabinet 'OLAPDB_Manual_bak.CAB_'
C:\OLAPBackup>"C:\Program Files\Microsoft Analysis Services\Bin\msmdarch.exe" 
/A VMAnalysis "\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data" 
OLAPDB_Manual2 c:\backup\OLAPDB_Manual2_bak 
   placed file 'OLAPDB_Manual2\MyCube.mdl_' (size 1786) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB_Manual2\sql2000 - FoodMart.src_' (size 406) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB_Manual2\Time.Calendar Time.dim_' (size 2830) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB_Manual2\Time.Calendar Time.dimcr_' (size 30) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB_Manual2\Time.Calendar Time.dimprop_' (size 9) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB_Manual2\Time.Calendar Time.dimtree_' (size 94455) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB_Manual2\MyCube\MyCube.1.agg.rigid.map_' (size 0) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB_Manual2\MyCube\MyCube.1.fact.map_' (size 0) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB_Manual2\MyCube\MyCube.agg.rigid.data_' (size 216) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB_Manual2\MyCube\MyCube.fact.data_' (size 2157) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB_Manual2\MyCube\MyCube.pdr_' (size 245) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB_Manual2\MyCube\MyCube.prt_' (size 3268) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB.REP' (size 9247) on cabinet 'OLAPDB_Manual2_bak.CAB_'
   placed file 'OLAPDB.INF' (size 905) on cabinet 'OLAPDB_Manual2_bak.CAB_'
C:\OLAPBackup>"C:\Program Files\Microsoft Analysis Services\Bin\msmdarch.exe" 
/A VMAnalysis "\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data" 
OLAPHierarchy c:\backup\OLAPHierarchy_bak 
   placed file 'OLAPHierarchy\HumanResource_Dim.HR.dim_' (size 1844) on cabinet 'OLAPHierarchy_bak.CAB_'
   placed file 'OLAPHierarchy\HumanResource_Dim.HR.dimcr_' (size 30) on cabinet 'OLAPHierarchy_bak.CAB_'
   placed file 'OLAPHierarchy\HumanResource_Dim.HR.dimprop_' (size 9) on cabinet 'OLAPHierarchy_bak.CAB_'
   placed file 'OLAPHierarchy\HumanResource_Dim.HR.dimtree_' (size 278986) on cabinet 'OLAPHierarchy_bak.CAB_'
   placed file 'OLAPHierarchy\MyCube1.mdl_' (size 1942) on cabinet 'OLAPHierarchy_bak.CAB_'
   placed file 'OLAPHierarchy\sql2000 - FoodMart.src_' (size 406) on cabinet 'OLAPHierarchy_bak.CAB_'
   placed file 'OLAPHierarchy\MyCube1\MyCube1.1.fact.map_' (size 20636) on cabinet 'OLAPHierarchy_bak.CAB_'
   placed file 'OLAPHierarchy\MyCube1\MyCube1.fact.data_' (size 10495) on cabinet 'OLAPHierarchy_bak.CAB_'
   placed file 'OLAPHierarchy\MyCube1\MyCube1.pdr_' (size 167) on cabinet 'OLAPHierarchy_bak.CAB_'
   placed file 'OLAPHierarchy\MyCube1\MyCube1.prt_' (size 1517) on cabinet 'OLAPHierarchy_bak.CAB_'
   placed file 'OLAPDB.REP' (size 4263) on cabinet 'OLAPHierarchy_bak.CAB_'
   placed file 'OLAPDB.INF' (size 776) on cabinet 'OLAPHierarchy_bak.CAB_'

Conclusion

This article provided general guidelines on how to backup a SQL Server 2000 analytical database manually. Part II of this article will provide guidelines on how to automate this backup process of Analytical databases on various servers.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved