dcsimg

Backup your SQL Server Analytical Database - Part II

December 20, 2005

Part I of this article provided general guidelines on how to backup a SQL Server 2000 Analytical database manually. Part 2 examines how to automate this backup process of Analytical databases from various servers.

Pre-requisite

a.  Client portion of SQL Server 2000 Analysis Service is installed on the machine where we are going to run this batch file. [Refer Fig 1.0 and 1.1]

Click for larger image

Fig 1.0

b. Make sure the msmdarch.exe file is installed. [Refer Fig 1.0a]

Click for larger image

Fig 1.0a

c. The account used to run the script should have access to the data folder of the analysis service machine.

Step 1

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


Fig 1.1

Let us assume that we still have the following folder C:\OLAPBackup. [Refer Fig 1.2]

Click for larger image

Fig 1.2

Step 2

Let us create C:\OLAPBackup\ServerDB.txt and copy and paste the following database names. [Refer Fig 1.3]

VMAnalysis,OLAPCubeManual,\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data,C:\Backup
VMAnalysis,OLAPDatabase,\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data,C:\Backup
VMAnalysis,OLAPDB_Manual,\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data,C:\Backup2
VMAnalysis,OLAPDB_Manual2,\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data,C:\Backup
VMAnalysis,OLAPHierarchy,\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data,C:\Backup2


Fig 1.3

  • VMAnalysis is the Analysis server name
  • OLAPCubeManual, OLAPDatabase, OLAPDB_Manual, OLAPDB_Manual2 and OLAPHierarchy are OLAP databases.
  • \\vmanalysis\c$\program Files\Microsoft Analysis Services\Data is the Data File path.
  • C:\Backup and C:\Backup2 are folders where the backup files are going to be stored.

Note: Please change the Servername, databasename , the data file path and the backup folder names according to your environment.

Step 3

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

Please download the code from here.

REM Backup OLAp databases
REM Created by : MAK
REM Created Date: Nov 19, 2005
set HH=%time:~0,2%
set MMM=%time:~3,2%
set SS=%time:~6,2%
set MM=%date:~4,2%
set DD=%date:~7,2%
set YYYY=%date:~10,4%
for /f "tokens=1,2,3,4 delims=," %%i in (C:\OLAPBackup\Serverdb.txt) 
  do "C:\Program Files\Microsoft Analysis Services\Bin\msmdarch.exe" 
  /A %%i "%%k" %%j %%l\%%j_bak_%YYYY%_%MM%_%DD%


Fig 1.4

Step 4

When this batch file, C:\OLAPBackup\backupolap2.bat, is executed, it backs up all the databases from all of the servers listed in the ServerDB.txt to C:\backup folder. [Refer Fig 1.5, 1.6 and 1.7]


Fig 1.5


Fig 1.6


Fig 1.7

Notice that when backing up the Analytical database, it suffixes the file name with current date in yyyymmdd format.

This also stores the log information 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>set HH= 5 
C:\OLAPBackup>set MMM=05 
C:\OLAPBackup>set SS=18 
C:\OLAPBackup>set MM=11 
C:\OLAPBackup>set DD=21 
C:\OLAPBackup>set YYYY=2005 
C:\OLAPBackup>for /F "tokens=1,2,3,4 delims=," %i in (C:\OLAPBackup\Serverdb.txt) 
  do "C:\Program Files\Microsoft Analysis Services\Bin\msmdarch.exe" 
  /A %i "%k" %j %l\%j_bak_2005_11_21 
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_2005_11_21 
   placed file 'OLAPCubeManual\MyCube3.mdl_' (size 3112) 
   on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB'
   placed file 'OLAPCubeManual\MyCube3^Calendar.Time.dim_' (size 3970) 
   on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB'
   placed file 'OLAPCubeManual\MyCube3^Calendar.Time.dimcr_' (size 30) 
   on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB'
   placed file 'OLAPCubeManual\MyCube3^Calendar.Time.dimprop_' (size 9) 
   on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB'
   placed file 'OLAPCubeManual\MyCube3^Calendar.Time.dimtree_' (size 52928) 
on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3^Mak Inc. Product.dim_' (size 2006) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3^Mak Inc. Product.dimcr_' (size 30) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3^Mak Inc. Product.dimprop_' (size 9) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3^Mak Inc. Product.dimtree_' (size 360975) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3^Mak.Store.dim_' (size 2412) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3^Mak.Store.dimcr_' (size 30) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3^Mak.Store.dimprop_' (size 2925)
on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3^Mak.Store.dimtree_' (size 3034) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\sql2000 - FoodMart.src_' (size 406)
on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3\MyCube3.1.fact.map_' (size 6120) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3\MyCube3.2.fact.map_' (size 25654) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3\MyCube3.3.fact.map_' (size 178) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3\MyCube3.fact.data_' (size 49010) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3\MyCube3.pdr_' (size 197) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPCubeManual\MyCube3\MyCube3.prt_' (size 4802) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPDB.REP' (size 12972) on cabinet 'OLAPCubeManual_bak_2005_11_21.CAB' placed file 'OLAPDB.INF' (size 1491) on cabinet 'OLAPCubeManual_bak_2005_11_21.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_2005_11_21 placed file 'OLAPDatabase\Cube1.mdl_' (size 2880) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Product.dim_' (size 2502) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Product.dimcr_' (size 30) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Product.dimprop_' (size 9) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Product.dimtree_' (size 76484) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Region.dim_' (size 2614) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Region.dimcr_' (size 30) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Region.dimprop_' (size 9) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Region.dimtree_' (size 19578) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\SQL2000 - foodmart.src_' (size 440) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Time.dim_' (size 3874) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Time.dimcr_' (size 30) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Time.dimprop_' (size 9) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Time.dimtree_' (size 256601) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Cube1\Cube1.1.agg.rigid.map_' (size 0) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Cube1\Cube1.1.fact.map_' (size 472) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Cube1\Cube1.2.agg.rigid.map_' (size 0) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Cube1\Cube1.2.fact.map_' (size 6850) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Cube1\Cube1.3.agg.rigid.map_' (size 0) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Cube1\Cube1.3.fact.map_' (size 9054) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Cube1\Cube1.agg.rigid.data_' (size 13429) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Cube1\Cube1.fact.data_' (size 44406) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Cube1\Cube1.pdr_' (size 1284) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDatabase\Cube1\Cube1.prt_' (size 12938) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDB.REP' (size 17875) on cabinet 'OLAPDatabase_bak_2005_11_21.CAB' placed file 'OLAPDB.INF' (size 1509) on cabinet 'OLAPDatabase_bak_2005_11_21.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:\Backup2\OLAPDB_Manual_bak_2005_11_21 placed file 'OLAPDB_Manual\sql2000 - FoodMart.src_' (size 406) on cabinet 'OLAPDB_Manual_bak_2005_11_21.CAB' placed file 'OLAPDB.REP' (size 5581) on cabinet 'OLAPDB_Manual_bak_2005_11_21.CAB' placed file 'OLAPDB.INF' (size 183) on cabinet 'OLAPDB_Manual_bak_2005_11_21.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_2005_11_21 placed file 'OLAPDB_Manual2\MyCube.mdl_' (size 1786) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB_Manual2\sql2000 - FoodMart.src_' (size 406) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB_Manual2\Time.Calendar Time.dim_' (size 2830) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB_Manual2\Time.Calendar Time.dimcr_' (size 30) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB_Manual2\Time.Calendar Time.dimprop_' (size 9) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB_Manual2\Time.Calendar Time.dimtree_' (size 94455) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB_Manual2\MyCube\MyCube.1.agg.rigid.map_' (size 0) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB_Manual2\MyCube\MyCube.1.fact.map_' (size 0) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB_Manual2\MyCube\MyCube.agg.rigid.data_' (size 216) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB_Manual2\MyCube\MyCube.fact.data_' (size 2157) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB_Manual2\MyCube\MyCube.pdr_' (size 245) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB_Manual2\MyCube\MyCube.prt_' (size 3268) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB.REP' (size 9245) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' placed file 'OLAPDB.INF' (size 903) on cabinet 'OLAPDB_Manual2_bak_2005_11_21.CAB' C:\OLAPBackup>"C:\Program Files\Microsoft Analysis Services\Bin\msmdarch.exe" /A VMAnalysis "\\vmanalysis\c$\program Files\Microsoft Analysis Services\Data" OLAPHierarchy C:\Backup2\OLAPHierarchy_bak_2005_11_21 placed file 'OLAPHierarchy\HumanResource_Dim.HR.dim_' (size 1844) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB' placed file 'OLAPHierarchy\HumanResource_Dim.HR.dimcr_' (size 30) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB' placed file 'OLAPHierarchy\HumanResource_Dim.HR.dimprop_' (size 9) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB' placed file 'OLAPHierarchy\HumanResource_Dim.HR.dimtree_' (size 278986) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB' placed file 'OLAPHierarchy\MyCube1.mdl_' (size 1942) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB' placed file 'OLAPHierarchy\sql2000 - FoodMart.src_' (size 406) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB' placed file 'OLAPHierarchy\MyCube1\MyCube1.1.fact.map_' (size 20636) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB' placed file 'OLAPHierarchy\MyCube1\MyCube1.fact.data_' (size 10495) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB' placed file 'OLAPHierarchy\MyCube1\MyCube1.pdr_' (size 167) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB' placed file 'OLAPHierarchy\MyCube1\MyCube1.prt_' (size 1517) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB' placed file 'OLAPDB.REP' (size 4261) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB' placed file 'OLAPDB.INF' (size 774) on cabinet 'OLAPHierarchy_bak_2005_11_21.CAB'

Conclusion

This article provided general guidelines on how to automate the backup process of Analytical databases from various servers and store the files in time stamped file names. This batch file can be scheduled using Windows Scheduler.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers