SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Here is simple script that will backup one Microsoft SQL Server Analysis Services (SSAS) database and will include timestamp in backup file name. Also this script will delete backup files that have date of creation older than specified number of days.

Please note that most of this script comes in from readme file for ascmd utility. I just added backup timestamp (code comes from another example of the same document) and added logic to delete older backup files.

To run this script you need to download source code for ascmd utility and then compile it to executable.

This script has 2 parts:

• backup.xmla file is XMLA code to do SSAS database backup. This script uses 3 parameters: $(ascmddbname),  $(backupFolder) and $(timestamp). First parameter $(ascmddbname) is system reserved script variable that is available in XMLA scripts used by ascmd utility. Last 2 parameters $(backupFolder) and $(timestamp) are passed as variables in ascmd execution command
• backup.bat - is DOS batch file that executes ascmd.exe command with proper parameters and also deletes old backup files.

You must modify backup.bat file before execution. You must setup values for SSASBackupFolder, SSASScriptFolder, SSASDBName and KeepBackupDays (names are self-explanatory). If any of above listed parameters have a space (if folder or DB Name), then you will need to include string values in the double quotes. It is also very likely that you will need to change how you generate timestamp as this depends on your regional date format.

After successful backup this script will delete backup files from the same folder that are older than KeepBackupDays parameter value. Backup file age will be based not on the timestamp included in the file name, but on file creation date.

You can schedule execution of this script from SQL Server Agent.

Below is source code for both scripts

backup.xmla

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <Object>
      <DatabaseID>$(ascmddbname)</DatabaseID>
   </Object>
   <File>$(backupFolder)\$(ascmddbname)_$(timestamp).abf</File>
</Backup>

backup.bat

@echo off
REM Script by Vidas Matelis: http://www.ssas-info.com
REM Creates backup for one SSAS database and deletes backups that are older than x days
REM Before executing change values below. Strings with spaces should be between double quotes
REM ----------------------------------------------------------------
set SSASBackupFolder=c:\SQL\SSASBackup\iw72bIdsDM
set SSASScriptFolder=C:\SQL\SQLScripts
set SSASDBName="Adventure Works DW"
set KeepBackupDays=5
REM ----------------------------------------------------------------

call :generate-timestamp
%SSASScriptFolder%\ascmd.exe -S localhost -d %SSASDBName% -i %SSASScriptFolder%\backup.xmla -o %SSASBackupFolder%\backup-out.xml -Tl high -Tf csv -T %SSASBackupFolder%\backup-trace.csv -Td , -v timestamp=%timestamp% backupFolder=%SSASBackupFolder%
if ERRORLEVEL 1 goto errseen
echo Backup completed sucessfully!

ForFiles /P %SSASBackupFolder% /M %SSASDBName%*.abf /D -%KeepBackupDays% /C "cmd /c del @path"

goto :EOF
:errseen
echo ** Error seen in processing
goto :EOF

REM Generating timestamp. Assumption is that date format is DDD MM/DD/YYYY. You might need to adjust this for your regional settings!!!
:generate-timestamp
set now_date=%date%
set now_time=%time%
set now_Year=%now_date:~10,4%
set now_Month=%now_date:~4,2%
set now_Day=%now_date:~7,2%
REM If your date format is MM/DD/YYYY the following lines should be used
REM set now_Year=%now_date:~6,4%
REM set now_Month=%now_date:~0,2%
REM set now_Day=%now_date:~3,2%
set now_Hour=%now_time:~0,2%
set now_Min=%now_time:~3,2%
if "%now_Hour:~0,1%"==" " set now_Hour=0%now_Hour:~1,1%
set timestamp=%now_year%%now_month%%now_day%%now_hour%%now_min%
goto :EOF

Use at your own risk!

 Tips:

• If you encounter any problems executing this script, edit backup.bat file and add "echo " before line that starts with "%SSASScriptFolder%\ascmd.exe". This way instead of executing ascmd, you will print to the display actual command. 
• To see your date format in command prompt enter "date" and hit "ENTER". You need to do this on the machine where you will schedule your script.