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).

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).

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.

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.

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.
Thanks for information .. good work…
Hi Benny, thank you for the useful tip.
But I have a question for you: what can I do if, when the job runs, it terminates with an error like “No connection could be made because the target machine actively refused it”?
Thanks
Is the SQL Server Browser Service running ?
Yes, it is
check whether the firewall is not blocking ssas port. You could test this by from telnet. Run this – telnet <> <>. Default port for ssas is 2383. If the firewall is not open, you will get an error. Otherwise pretty much nothing happens
telnet ip port
It’s realy usefull. Thanks
great job, thx a lot 🙂
That is awesome, worked for me!!
Ton of thanks!!!
Well this page helped me tremendously!!! Well done!! Automated Olap backups in SQL 2008 are quite different from previous versions with MSMDARCH not being there and this page details a way of doing it beautifully…