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]
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]
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
» 
