Tuesday, August 21, 2007

AMO Script for Automating SSAS Database Backups

By Bryan C. Smith

I apologize in advance, but I'm afraid I need to get up on my soapbox for this one. Bear with me.


I have not seen folks pursuing regular backups of multidimensional databases as aggressively as they do relational databases. Part of this is an attitude that multidimensional databases are simply redundant copies of their relational data sources. Still another part of this is a lack of familiarity with the SSAS technology and the tools available to support regular backups. Whatever the reason, the "zero data loss" mentality has not spread to the multidimensional landscape.

So, let's clearly state a few things. Multidimensional databases do occasionally get corrupted, developers do introduce errors, and systems do fail. Projects housing the database definition do get lost and corrupted as well, even in Visual Source Safe and other related systems.

Though you may have a good set of project files available with which to rebuild a multidimensional database, important changes can be introduced through management activities that are not reflected in the BIDS project. Furthermore, the time it takes to reprocess a cube following a failure is often not acceptable to end-users, especially when those databases support critical business processes.

It is important we insure regular backups are taken of our multidimensional databases and are managed in a manner commensurate with our relational data sources.


So, I've taken a look at a few means for handling regular backups of multidimensional databases. In the end, I find AMO the most appealing tool for this.

Below is a sample script that you may wish to explore for your own purposes. This was implemented in the Script task of an SSIS package. If you wish to test the script out, be sure to add a reference to the Analysis Management Objects library in your script editor.

Also, I need to note that this is not intended to be deployed to production as is. If you wish to use this script in a production setting, please thoroughly test it, add appropriate error handling, etc. If you use all or part of this script, you assume responsibility for its behavior. Sorry, but I've got to say it.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Class ScriptMain

Sub Main()

'Get TimeStamp in form YYYYMMDDhhmmss


Dim MyTimestamp As

MyTimestamp = CStr(Year(Now()) * 10000 + _

Month(Now()) * 100 + Day(Now()))

MyTimestamp += CStr(Hour(Now()) * 10000 + _

Minute(Now()) * 100 + Second(Now()))


'Connect to SSAS Instance


Dim MyServer As
New Server



'Backup Databases


Dim BackupFileName As

Dim AllowOverwrite As
Boolean = True

Dim BackupRemotePartitions As
Boolean = False

Dim MyLocation() As BackupLocation

Dim ApplyCompression As
Boolean = True


Each MyDatabase As Database In MyServer.Databases

BackupFileName = "c:\temp\" + MyDatabase.Name + _

"_" + MyTimestamp + ".abf"


BackupFileName, AllowOverwrite, _

BackupRemotePartitions, MyLocation, _




'Disconnect from SSAS Instance



Dts.TaskResult = Dts.Results.Success



1 comment:

Sam Kane said...

Here are this and some other articles on SSAS Backup and Restore: