SSIS package to backup all Analysis Services databases on one instance
User Rating: / 15
PoorBest 
Written by Vidas Matelis   
Tuesday, 17 July 2007 19:51

Here is SSIS packaga that creates backup for each database in one SSAS instance. You can specify backup folder and server name by passing SSIS package variables through command line:

dtexec /FILE "C:\DTSX_Scripts\DWBackupOLAPDBs.dtsx" /Set \package.variables[BackupLocation].Value;c:\NewBackupLocation\ /Set \package.variables[SSASServerName].Value;NewSSASServerName

Use it at your own risk.

 
Comments (4)
4 Wednesday, 16 April 2014 19:59
G Patel
I want take one database back up not whole database please help me
3 Thursday, 20 December 2012 07:28
Raj Kumar
Cool little package, thanks!

NOTE:
The script in the package doesn't seem to work on a 64-bit 2005 SSAS instance but there is a solution: Run the package in 32-bit mode ("C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /FILE "D:\SSIS\Packages\BackupAllOLAPDBs.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E)

Also note that you cannot simply use the /x86 switch (which is the same as enabling the 32-bit option in the SQL job in SSMS 2008) in SQL Server 2005 because unlike in SQL Server 2008 there are 2 separate DTEXEC commands --one each for 32-bit and 64-bit. In SQL Server 2008 there is only one DTEXEC and it takes the /x86 switch, which makes the package execute in 32-bit context.
2 Thursday, 19 April 2012 09:49
Sacha79
Thanks a lot !
1 Sunday, 30 March 2008 00:18
Brian
Thanks for the Backup Script.

 

Pyramid Analytics