Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to dynamically back up all the databases on a given SSAS instance using a SQL Agent job (which would most likely involve executing an SSIS package). It is imperative that this is a dynamic process - if users add databases or cubes, I want to set up a job one time that can automatically detect all existing SSAS metadata.

Unfortunately, I don't see anything out there that tells me how I can automatically and dynamically back up all of the databases on an SSAS instance in a clean way. By "clean", I mean:

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

This can in fact be done. There are probably a few ways to do it, and here is a fairly straightforward example. For this solution, you will use a combination of:

  1. A SQL Agent job with a step for each instance that needs backed up (i.e. A step for the development server, the qa server, and for production).
  2. One dynamic SSIS package that is called in each step of the job.
  3. An Execute Script Task that uses the Analysis Management Objects (AMO).

Create the SSIS Package

Variables

VariableName|Scope|Type

  • BackupDir | Package level or Foreach level | String

  • DatabaseId | Package level or Foreach level | String

  • DatabaseName | Package level or Foreach level | String

  • InstanceForDatasource | Package level | String

    • Intended for use in the Set Values tab of the SQL Agent job if more than one instance will be backed up. This variable will be used to build the ConnectionString.
  • ConnectionString | Package level | String

    • Set the EvaluateAsExpression property to true.

    • Set the Expression property as follows: Data Source="+ @[User::InstanceForDatasource] +";Provider=MSOLAP.4;Integrated Security=SSPI;

    • Please note that this approach can be expanded upon to have the entire ConnectionString be dynamic and dictated by the calling SQL Agent job step if necessary.

  • XMLAScript | Package or Foreach level | String

Connection Managers

Use a real connection in design time so that the metadata plays nice.

Creating the connection managers now isn't required, but it makes it easier for later. For each task in the process, you will have the appropriate connection manager available in the drop-down without the need to create any on-the-fly.

ADO.NET

  1. Create a new ADO.NET connection manager that uses the Microsoft OLE DB Provider for Analysis Service
  2. Using the Property Expressions Editor, set the ConnectionString property as follows: [User::ConnectionString]

OLEDB

  1. Create a new OLEDB connection manager that uses the Microsoft OLE DB Provider for Analysis Service
  2. Using the Property Expressions Editor, set the ConnectionString property as follows: [User::ConnectionString] + "Format=Tabular;"

Analysis Services

  1. Create a new Analysis Services connection
  2. Using the Property Expressions Editor, set the ConnectionString property as follows: [User::ConnectionString] = "Impersonation Level=Impersonate;"

Create the Foreach Container

Here, you will create a Foreach based on the Catalogs schema rowset. This will get us the DatabaseName for each database in the instance and the DatabaseName will be put into its corresponding variable.

Foreach Container configuration Foreach Container variable mappings

Create an Execute Script Task

Set the script to use Visual Basic.

Set the ReadOnlyVariables and ReadWriteVariables as follows:

  • ReadOnlyVariables: User::ConnectionString,User::DatabaseName,User::InstanceForDatasource
  • ReadWriteVariables: User::BackupDir,User::DatabaseId,User::XMLAScript

Edit the Script

  • Add a reference to the Analysis Management Objects (AMO) assembly.

    • Right-click on the project name (should be the top-most item in the Project Explorer in the Visual Studio window that opened when you clicked Edit Script) and select Add Reference.

    • Add a reference to the Analysis Services Objects component. The dll for AMO should be located in <SQL Server InstallationDrive>:\Program Files\Microsoft SQL Server\<SQL Server Version>\SDK\Assemblies.

  • In the script header, add an Imports statement to use AMO: Imports Microsoft.AnalysisServices

  • Replace the contents of Public Sub Main() with the following script:

    Public Sub Main()
        '
        Dim ASServer As New Microsoft.AnalysisServices.Server()
        Dim AsDatabase As New Microsoft.AnalysisServices.Database
        Dim ASConn As String = ""
        Dim ASDatabaseName As String = ""
        Dim ASDatabaseId As String = ""
    
        'Create a variable that uses the dynamic ConnectionString variable
        ASConn = Dts.Variables("ConnectionString").Value
    
        'What database are we working with in this iteration of the Foreach?
        ASDatabaseName = Dts.Variables("DatabaseName").Value
    
        'Use the Analysis Services AMO to get the DatabaseId for this DatabaseName.
        '   It is necessary to get the DatabaseId because the XMLA backup command requires the DatabaseId,
        '   and if at any point the database is renamed, the DatabaseId will differ from the DatabaseName.
        Try
            'Establish the connection to SSAS.
            ASServer.Connect(ASConn)
    
            'Get the database.
            AsDatabase = ASServer.Databases.FindByName(ASDatabaseName)
    
            '***ONLY IF the EstimatedSize > 0***. 
            ' --> If it is 0, it could be a corrupted database which causes the backup process to stop.
            ' --> In that case, we will leave the DatabaseId variable blank to be a visiblie indicator that the db is corrupt.
            '***ONLY IF the number of cubes > 0***.
            ' --> The databse could be corrupt or backup could hang if there are no cubes.
            If AsDatabase.EstimatedSize > 0 And AsDatabase.Cubes.Count > 0 Then
                'Get the DatabaseId
                'Retrieve the DatabaseId from the Databases collection.
                ASDatabaseId = ASServer.Databases.FindByName(ASDatabaseName).ID
            Else
                Dts.Events.FireWarning(0, "Verifying database", "The estimated size and/or number of cubes in the database does not meet the requirements. Estimated Size= " & AsDatabase.EstimatedSize & "; " & "Number of cubes= " & AsDatabase.Cubes.Count, String.Empty, 0)
            End If
        Catch ex As Exception
            'Couldn't connect. Do not error out because maybe the next iteration will succeed.
            Dts.Events.FireWarning(0, "Establishing SSAS Connection", "Unable to connect to the SSAS Server with ConnectionString= '" & ASConn & "'. Error: " & ex.ToString, String.Empty, 0)
        End Try
    
        'Print info for output while testing/debugging.
        Dts.Events.FireInformation(0, "Establishing SSAS Connection", "Connection established. ConnectionString= '" & ASConn & "'.", String.Empty, 0, True)
    
        'Put the DatabaseId into the SSIS variable. 
        Dts.Variables("DatabaseId").Value = ASDatabaseId
    
        'If we were able to retrieve the DatabaseId, then generate the backup directory and filename and the XMLA script.
        If ASDatabaseId <> "" Then
    
            'Print info for output while testing/debugging.
            Dts.Events.FireInformation(0, "Retrieving DatabaseId", "DatabaseId retrieved for " & ASDatabaseName & ". DatabaseId= '" & ASDatabaseId & "'.", String.Empty, 0, True)
    
            'Get timestamp for the backup file
            Dim timeStamp As String = Now.Year & _
                                        Right("0" & Now.Month, 2) & _
                                        Right("0" & Now.Day, 2) & _
                                        Right("0" & Now.Hour, 2) & _
                                        Right("0" & Now.Minute, 2) & _
                                        Right("0" & Now.Second, 2)
    
            'Get the filename and location for the backupfile
            Dim filePath As String = "\\<servershare>\" & Dts.Variables("ServerForDatasource").Value & "\" & ASDatabaseName
            Dim fileName As String = ASDatabaseName & "_" & timeStamp & ".abf"
    
            'Put the filepath into the SSIS variable
            Dts.Variables("BackupDir").Value = filePath
    
            'Print info for output while testing/debugging
            Dts.Events.FireInformation(0, "Retrieving full backup path", "BackupPath= '" & filePath & "\" & fileName & "'.", String.Empty, 0, True)
    
            'Build XMLA script
            Dim xmlaScript As String = ""
            xmlaScript = "<Backup xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" & _
                                "<Object><DatabaseID>" & ASDatabaseId & "</DatabaseID></Object>" & _
                                "<File>" & filePath & "\" & fileName & "</File>" & _
                            "</Backup>"
    
            'Put the XMLA script into the SSIS variable
            Dts.Variables("XMLAScript").Value = xmlaScript
    
            'Print info for output while testing/debugging.
            Dts.Events.FireInformation(0, "Generate XMLAScript", "XMLAScript= '" & xmlaScript & "'.", String.Empty, 0, True)
        Else
            'Print info for output while testing/debugging.
            'If DatabaseId is empty, we will not perform the rest of the tasks in the Foreach container.
            Dts.Events.FireWarning(0, "Retrieving DatabaseId", "Unable to retrieve the DatabaseId for Database= '" & ASDatabaseName & "'.", String.Empty, 0)
        End If
    
        ' Return success
        Dts.TaskResult = ScriptResults.Success
    End Sub
    

Create a File System Task (to create the Backup Directory)

It is important to set UseDirectoryIfExists to true to avoid an error if the backup directory already exists.

File System Task Editor

Edit the precedence constraint between the Execute Script Task and the File System Task

This precedence constraint will handle a missing DatabaseId. The DatabaseId would be missing if a connection to the SSAS server could not be established in the vbscript, or if the database is corrupted. You do not want to back up a corrupted database. It will bring the backup to a halt.

Precedence Constraint Editor

Example of Expression: `@[User::DatabaseId] !=""

Create an Analysis Services Execute DDL Task

Analysis Services DDL Task

Your final Control Flow should look like this:

Final Control Flow


Create the SQL Agent Job

  1. Add a step for each SSAS instance that will be backed up.
  2. Each step should be configured to execute the SSIS package.
  3. For each step, click the Set values tab and set the value of InstanceForDatasource to the instance name for the step.

Notes:

  • To make the entire backup directory dynamic, you would just have to add another variable for that and account for it in the vbscript.

  • Again, if necessary, the entire connection string variable could be set by the SQL Agent job instead of just the instance name.

share|improve this answer
add comment

Another option would be Powershell SQL Server Analysis Services Backup in Powershell

share|improve this answer
    
I actually just implemented that very same script today on an environment that doesn't have access to SSIS. It should be noted though that to use it in a SQL Agent job, you have to run it in a CmdExec step. –  dev_etter Jun 1 '13 at 4:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.