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).

Essential references:

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.

Posted on 24.06.2008, in MS Windows and tagged . Bookmark the permalink. Comments Off on How to backup MS Analysis Services OLAP db with ascmd.

Comments are closed.