How to backup MS Analysis Services OLAP db with ascmd
I’m not sure why Microsoft doesn’t ship compiled version of ascmd with SQL Server 2005!? Ascmd as you might know is command lined tool that you can use to execute xmla scripts in addition to MDX queries and DMX statements.
My only interest at present is to use ascmd to backup OLAP mode database from command line (if you’re storing data as ROLAP or HOLAP, then make sure you read Backup Strategies with SQL Server 2005 Analysis Services).
- Readme For Ascmd Command-line Utility Sample
- I found compiled version of ascmd at Project Real homepage, more precisely in package ProjectREALRefImpl_10.zip. Unpack ProjectREALSampleCodeAndTools.msi from zip file and install the package, then search for ascmd.exe. I picked up binary for x64.
Copy ascmd to your OLAP server (like ?:\MSSQL\MSSQL.1\OLAP\bin). All that you need now is xmla script that can be generated within MS SQL Server Management Studio and simple batch file, such as:
SET PATH=?:\MSSQL\MSSQL.1\OLAP\bin;%PATH% ascmd -i BackupOLAP.xmla if errorlevel 0 echo Backup BACKUPOLAP= OK > ?:\OLAPBackup\BackupOLAP.log if errorlevel 1 echo Backup BACKUPOLAP= ERROR > ?:\OLAPBackup\BackupOLAP.log
and BackupOLAP.xmla might look like this:
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <File>?:\OLAPBackup\BackupOLAP.abf</File> <AllowOverwrite>true</AllowOverwrite> </Backup>
…replacing ? with the appropriate drive letter, of course.