How to Automate Analysis Services Database Backup?

Analysis Service Database can be backed up from SQL Server Management Studio (SSMS). This process is pretty much UI based. In this post, I explain two different techniques to automate the backup of an Analysis Service Database.

Simple Backup

The first method is fast and easy technique to automate backup. This technique uses XMLA script. The easiest way to create the XMLA backup script is from the Management Studio.

1. From Management Studio, connect to your Analysis Server. Right click on the relevant Analysis Server Database and select Back Up. (shown in Figure 1).

Figure 1 - Backup Option from Management Studio

 2. Select all the relevant backup options like- filename, overwrite option, compression option etc. Then hit the arrow beside Script Button and select Script Action to New Query Window (as shown in Figure 2).

Figure 2 - Script To New Window

 

 3. The XMLA script for Backup will be generated as shown in Figure 3. You can choose to specify a backup folder as shown in the screen capture. If you choose not to specify a backup folder, the backup will still happen and the backup file be created in the folder specified in BackupDir setting of your Analysis Server. 

Figure 3 - Backup XMLA script

4. With the backup XMLA script now ready, it’s time to automate the script execution. This can be done by creating a SQL Server Job. Create a Job step of type SQL Server Analysis Services Command and paste the XMLA script as shown in Figure 4. 

Figure 4 - SQL Analysis Services Command

 That’s it. The end-to-end process of setting up a simple automated backup takes less than 5 minutes.

Rolling Backup

The technique described above is sufficient for simple applications. Commercial BI applications do require a robust backup mechanism characterized by

• Ability to create daily backup without overwriting the previous backup.

• Configurable to run in different environments.

• Re-usable.

The solution to achieve this is – AMO Wrappers. I’m going to use the BackupDatabase() method of the AMO wrapper library that I created. If I just isolate the BackupDatabase() method from the AMO Wrapper library, the code snippet will look like as shown below. You would notice from the code snippet that the major objects of the OLAP model must be created for the BackupDatabase() method to work.

Imports Microsoft.AnalysisServices

Imports System.String

Namespace BennyAustin.SSAS.Wrappers

Public Class AMOWrapper

Private _ServerName As String

Private _Database As String

Private _CubeName As String

Private _ServerObj As Server

Private _DatabaseObj As Database

Private _CubeObj As Cube

'Property - ServerName

Private Property Server() As String

Get

Return _ServerName

End Get

Set(ByVal value As String)

_ServerName = value

End Set

End Property

'Property - DatabaseName

Private Property Database() As String

Get

Return _Database

End Get

Set(ByVal value As String)

_Database = value

End Set

End Property

'Property - CubeName

Private Property Cube() As String

Get

Return _CubeName

End Get

Set(ByVal value As String)

_CubeName = value

End Set

End Property

'Property - ServerObject

Private Property ServerObj() As Server

Get

Return _ServerObj

End Get

Set(ByVal value As Server)

_ServerObj = value

End Set

End Property

'Property - DatabaseObject

Private Property DatabaseObj() As Database

Get

Return _DatabaseObj

End Get

Set(ByVal value As Database)

_DatabaseObj = value

End Set

End Property

'Property - CubeObject

Private Property CubeObj() As Cube

Get

Return _CubeObj

End Get

Set(ByVal value As Cube)

_CubeObj = value

End Set

End Property

Public Function GetServer(ByVal parmConnectionString) As Server

Dim objServer As Server = New Server()

Try

objServer.Connect(parmConnectionString)

Catch ServerNotFoundException As ConnectionException

Throw ServerNotFoundException

Catch GenericAMOException As AmoException

Throw GenericAMOException

End Try

Return objServer

End Function

Public Function GetDatabase(ByVal parmDatabase As String) As Database

Dim DatabaseNotFoundException As AmoException = New AmoException("Analysis Service Database " + parmDatabase + " not found in Analysis Server " + Me.ServerObj.Name)

Dim objDatabase As Database

Try

objDatabase = Me.ServerObj.Databases.FindByName(parmDatabase)

Catch GenericAMOException As AmoException

Throw GenericAMOException

End Try

If objDatabase Is Nothing Then

Throw DatabaseNotFoundException

End If

Return objDatabase

End Function

Public Function GetCube(ByVal parmCube As String) As Cube

Dim CubeNotFoundException As AmoException = New AmoException("Cube " + parmCube + _

" not found in Analysis Service Database " + _

Me.DatabaseObj.Name + " at Analysis Server " + Me.ServerObj.Name)

Dim objCube As Cube

Try

objCube = Me.DatabaseObj.Cubes(parmCube)

Catch GenericAMOException As AmoException

Throw GenericAMOException

End Try

If objCube Is Nothing Then

Throw CubeNotFoundException

End If

Return objCube

End Function

Public Sub New(ByVal parmServer As String, _

ByVal parmDatabaseName As String, _

ByVal parmCubeName As String)

Me.Server = parmServer

Me.Database = parmDatabaseName

Me.Cube = parmCubeName

'Instantiates major AMO objects

Try

Me.ServerObj = GetServer(parmServer)

Me.DatabaseObj = GetDatabase(parmDatabaseName)

Me.CubeObj = GetCube(parmCubeName)

Catch ex As ConnectionException 'raise AMO connection exception

Throw ex

Catch GenericAMOException As AmoException

Throw GenericAMOException

End Try

End Sub

Public Overloads Sub Finalize()

If Me.ServerObj.Connected Then

Me.CubeObj.Dispose()

Me.DatabaseObj.Dispose()

Me.ServerObj.Disconnect()

Me.ServerObj.Dispose()

End If

MyBase.Finalize()

End Sub

Public Sub BackupDatabase(ByVal parmFilePath As String)

Try

Me.DatabaseObj.Backup(parmFilePath, False)

Catch BackupException As OperationException

Throw BackupException

Catch GenericAMOException As AmoException

Throw GenericAMOException

End Try

End Sub

End Class

End Namespace

The BackupDatabase() method can then be called from a simple application as shown below. By using appropriate config file entries, the wrapper method can be re-used for any environment. Notice that the backup file rolls over each day.

Imports BennyAustin.SSAS.Wrappers

Imports System.Configuration.ConfigurationManager

Module CallAMOWrapper

Sub Main()

Try

Dim Server As String = AppSettings("Server")

Dim DatabaseID As String = AppSettings("DatabaseID")

Dim Cube As String = AppSettings("CubeID")

Dim BackUpDir As String = AppSettings("BackUpDir")

'Instantiate Major Objects

Dim objSec As AMOWrapper = New AMOWrapper(Server, DatabaseID, Cube)

'Backup AS Database before proceeding...

Dim BackUpFileName As String = "bkup" + CStr(Now().Year) + Right("0" + CStr(Now().Month), 2) + CStr(Now().Day) + "_" + DatabaseID + ".abf"

objSec.BackupDatabase(BackUpDir + BackUpFileName)

Catch ex As Exception

'log exception using your exception handler

End Try

End Sub

End Module

Now all that’s left is to schedule this application as a job and your Analysis Services backups are automated.

BENNY AUSTIN