Backup your SQL Server Analytical Database - Part I - Page 2

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]

Let us create the following folder C:\OLAPBackup. [Refer 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

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

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]

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