Pages

Tuesday, February 18, 2014

SQL Server Agent Jobs to Backup / Restore SSAS Cube

This post demostrates a few scripts which can be used within SQL Server Agent Jobs.

XMLA Script to backup a cube
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Company UAT Cube</DatabaseID>
  </Object>
  <File>Company UAT Cube.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
  <Password>AABB01</Password>
</Backup>



XMLA Script to restore a .ABF file

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <File>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Company Test Cube.abf</File>
  <DatabaseName>Company Test Cube</DatabaseName>
  <AllowOverwrite>true</AllowOverwrite>
  <Password>AABB01</Password>
</Restore>

SSAJ - operating system command to copy a file
copy "D:\FTP Root\Test Cube.abf" "D:\Microsoft SQL Server 2005\Analysis Services\MSSQL.2\OLAP\Backup\Test Cube.abf" /Y

SSAJ - operating system command to run a batch file
cmd.exe /c "C:\PharmaAnalytics\Daiichi Sankyo Europe Instance\System\Scripts\CopyTestCubeToSrerver1ABFBackups.bat"

Batch script to FTP an ABF file
@echo off
echo user USERNAME PASSWORD>ftp.ftp
echo put "C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube Test.abf">>ftp.ftp
echo bye>>ftp.ftp
ftp -n -s:ftp.ftp 94.1.1.2
del /q ftp.ftp