Recently I was asked for a generic script that does backup of all Analysis Services 2005 databases on one server.

 I quickly wrote SSIS package that does just that. You can download this package from here.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
    Public Sub Main()
        Dim sSSASServerName As String = CStr(Dts.Variables(”SSASServerName”).Value)
        Dim sBackupLocation As String = CStr(Dts.Variables(”BackupLocation”).Value)
        If Right(sBackupLocation, 1) <> “\” Then sBackupLocation = sBackupLocation + “\”

        Dim Locations() As Microsoft.AnalysisServices.BackupLocation
        Dim oServer As New Microsoft.AnalysisServices.Server
        oServer.Connect(sSSASServerName) ‘ connect to the server and start scanning down the object hierarchy
        Dim oDB As Microsoft.AnalysisServices.Database
        For Each oDB In oServer.Databases
            ‘If oDB.Name = “AdventureWords” Then ‘ here you can include or excluded databases
            oDB.Backup(sBackupLocation & oDB.Name & “_” & Now().ToString(”yyyyMMdd_hhmmss”) & “.abf”, True, False, Locations, True)
            ‘End If
        Next
        Dts.TaskResult = Dts.Results.Success
    End Sub
 
End Class

If you wish you can use CaptureXML technique (described in my previous blogs) for doing backups in parallel, but I usually like to leave some server power to users so they can query data while backups are executed. Running multiple database backups in parallel could have huge performance impact for end user querying. 

You can execute this package from command line and pass parameters that specify server name and location:

dtexec /FILE “C:\DTSX_Scripts\DWBackupOLAPDBs.dtsx” /Set \package.variables[BackupLocation].Value;c:\NewBackupLocation\ /Set \package.variables[SSASServerName].Value;NewSSASServerName

When I have to write a script to backup one or a few databases with a known name, then I usualy use XMLA command:

<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
  <Object>
    <DatabaseID>MyDBName</DatabaseID>
  </Object>
  <File>c:\BackupLocation\MyDBName.abf</File>
</Backup>

 This XMLA script could be executed using one of these methods:

  • SSIS package using control flow item “Analysis Services Execute DDL task”
  • SQL Server Job create step with type “SQL Server Analysis Services command”
  • using ascmd.exe utility.