» Database Journal Home

» Database Articles

» Database Tutorials

MS SQL

Oracle

DB2

MS Access

MySQL

» RESOURCES

SQL Scripts & Samples

Tips

» Database Forum

» Slideshows

Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Advertiser Disclosure

Featured Database Articles

MS SQL

Posted Dec 7, 2005

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

By Muthusamy Anantha Kumar aka The MAK

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

Go to page: Prev 1 2

MS SQL Archives

0 Comments (click to add your comment)

Comment and Contribute

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

Advertiser Disclosure: Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.