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.

START SOAPBOX

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.

END SOAPBOX

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

Public
Class ScriptMain



Public
Sub Main()



'Get TimeStamp in form YYYYMMDDhhmmss


'------------------------------------------------------


Dim MyTimestamp As
String

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

MyServer.Connect("localhost")


 

'Backup Databases


'------------------------------------------------------


Dim BackupFileName As
String


Dim AllowOverwrite As
Boolean = True


Dim BackupRemotePartitions As
Boolean = False


Dim MyLocation() As BackupLocation


Dim ApplyCompression As
Boolean = True


 

For
Each MyDatabase As Database In MyServer.Databases

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

"_" + MyTimestamp + ".abf"

MyDatabase.Backup(_

BackupFileName, AllowOverwrite, _

BackupRemotePartitions, MyLocation, _

ApplyCompression)

Next

 

'Disconnect from SSAS Instance


'------------------------------------------------------

MyServer.Disconnect()


Dts.TaskResult = Dts.Results.Success


End
Sub

End
Class