SQL Server Performance Forum – Threads Archive
Is there an automated solution to backup AS db?Hi, I currently archive (backup)my Analysis Service database manually (right click and then selecting Archive). Is there an automated solution to achieve the same (which must also override any exisiting database of the same name)?
Yes. Set up a command file (i.e. batch file) with the msmdarch utility in it and schedule it through Windows Scheduled Tasks (or in SQL Server Agent if you have SQL Server on the same server as Analysis Services). I do this, with Windows Scheduled Tasks, and it works a treat. I don’t know what you mean about "overwriting any existing database", but msmdarch does overwrite any existing database archive of the same name in the same location (puts it all in a *.CAB file). From BOL:- Syntax
["command-path]msmdarch["] /a Server "OLAPDataPath" "DatabaseName" "BackupFileName" ["LogFileName" ["TempDirectory"]] Example (which is what you’d put in your command file) "Program FilesMicrosoft Analysis ServicesBinmsmdarch" /a myserver
"Program FilesMicrosoft Analysis ServicesData" "FoodMart 2000"
"My archivesserver myserverFoodMart 2000.cab"
DBA, Oxfam GB
Thanks Tom. Will try it out.