dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.
News Via RSS Feed
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

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]

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



MS SQL Archives
Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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