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="">
    <DatabaseID>Company UAT Cube</DatabaseID>
  <File>Company UAT Cube.abf</File>

XMLA Script to restore a .ABF file

<Restore xmlns="">
  <File>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Company Test Cube.abf</File>
  <DatabaseName>Company Test Cube</DatabaseName>

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
del /q ftp.ftp

No comments:

Post a Comment