Using xp_cmdshell to backup AS databases

Question

Using xp_cmdshell to backup AS databases

Roust_m

SSCoach

Points: 17410
More actions
August 24, 2004 at 2:22 am

#109222

Hi!
Trying to automate AS databases backup with xp_cmdshell. How should I
rewrite the following command to use it with xp_cmdshell?
"C:\Program Files\Microsoft Analysis Services\Bin\msmdarch" /a
MY_OLAPSERVER_NAME "D:\olap_data\" "CUBE_DB_NAME"
"F:\BACKUP\OLAP\CUBE_DB_NAME\CUBE_DB_NAME.cab"
"F:\BACKUP\OLAP\CUBE_DB_NAME\CUBE_DB_NAME.log"
I need something like this:
Declare @cmd varchar(2000)
Declare @dbname varchar(50)
select @dbname = 'MyDB'
select @cmd = 'C:\Program Files\Microsoft Analysis Services\Bin\msmdarch /a' + ...
exec master.dbo.xp_cmdshell @cmd

Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

trey_johnson Mr or Mrs. 500 Points: 568 More actions · Answer 1

Hi.

I haven't really ever seen restoring CAB files work better than just reprocessing the OLAP database. Personally, I would focus my energy on having on hand an OLAP Schema that is empty that you can restore and reprocess as needed.

If you do need to do the OLAP backups, you might consider wrapping your command line call in a DTS package along with the requisite SQL to backup your RDBMS.

Just some food for thought.

Thanks!

Trey Johnson | Chief Business Intelligence Architect | Cizer Software (www.cizer.com)

Who? - Cizer - http://www.cizer.com/about.htm - Blog - http://www.sqlserverbi.com/
What? - Products enhancing Microsoft Business Intelligence - http://www.cizer.com/products.htm
Wow! - Empower your Developers.... NEW Drop In Reporting - http://www.cizer.com/cnr-drop-in-reporting.htm
How? - BI Training - http://www.cizer.com/training.htm - Cizer Solutions - http://www.cizer.com/solutions.htm

Steven L Durfey SSC Veteran Points: 240 More actions · Answer 2

set @Command = ('"C:\Program Files\Microsoft Analysis Services\Bin\msmdarch" /a SERVname f:\OLAP\data\ CUBEname \\backup\dbremotebackups\SERVname_CUBEname.cab \\backup\dbremotebackups3\SERVname_CUBEname.log')

will do the trick.