Introduction

SQL Server Analysis Service (SSAS) Databases are another type of database which stores cubes, dimensions, etc. Data for these databases are taken from SQL Server relational databases and other sources. The SSAS databases and SQL Server databases are linked by data sources in SSAS and the SSAS database is often updated using an operation called "processing" the cube.

Though these databases' data is not row data, still you need to take backups for these databases to improve the usability. Unlike relational databases, the options for SSAS database backups are limited. This article focuses on the available backup options and how to

achieve other required backup options.

Backup From SSMS

SQL Server Management Studio (SSMS) can be used easily to take backups of SSAS databases following below steps.

1. Login to an SSAS instance from SSMS.

2. Right Click the SSAS database you want to backup.

3. Select the Back Up... option and you will be taken to following page.

You can see that the selected database name appears in the Database field and you can provide the name of the backup file. If you didn't specify the path, the database backup will be written to the default folder C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Backup.

In this screen there are three additional options available.

• Allow file overwrite option will allow existing database backup to overwrite.
• Apply compression option will compress the database backup.

Encrypt backup file option will allow SSAS database backup to be secured with a password.

The apply compression and encrypt backup file options are very handy as they are not

available even for relational databases in SQL Server 2005. However, the release of SQL Server 2008 for relational databases will add these options as well.

Scheduling SSAS Database Backup

This seems to be a frequent question in the forums. To achieve this, you can create SQL Server Agent Job and schedule that job as per your requirement.

1.Right click Job folder of the SQL Agent node from the SSMS.

2.Select New Job… option.

3.Enter a Job name to identify the job

4.Go to Steps page.

5.Click New… button

6.After entering Step Name, select SQL Server Analysis Services Command as job type.

7.Enter the server name.

8.Enter the following XMLA script. In this, you can change the SSAS database name and the backup name.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>AdventureWorksDW</DatabaseID>
  </Object>
  <File>AdventureWorksDW.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
  <Password>password</Password>
</Backup>

Following image shows the step page after configuring it as above.

9.Click OK button.

10.Go to the Schedule page and create a schedule as per your need.

Backup All SSAS Databases

The next question, which I often hear, is how to take a backup of all the SSAS databases in your instance. Of course, you can create theSQL Server Agent jobs above for each and every database. However, there will be issues whenever new SSAS database is introduced as we need to remember to create a new job, which can be quite tricky.

To overcome this, a SQL Server Integration Services (SSIS) package is created. This SSIS package can be scheduled from SQL Server Agent to schedule the SSAS database backup creation.

First create an SSIS project and add an SSIS package to it.

In this SSIS package, we are going to use the above XMLA script. We will loop

through all the databases available by using Foreach Loop Container and pass the database name into the the XMLA script in which it has a parameter to accept the different database names.

First we have to create a Connection Manager using the Microsoft OLE DB Provider for Analysis Services 9.0, which will point to the SSAS Server where you want to take a backup of all the databases.

Next, let us configure Foreach Loop Container. Double click the

ForEach Loop Container and select the Collection option. In the Collection option, select the Foreach ADO.NET Schema Rowset Enumerator as the Enumerator.

Set the Connection to connection the you created before and set the Schema to Catalogs.

Create a string variable to hold the SSAS database name, ssas_database_name

and assign index 0 to this variable in the Variable Mappings option in the ForEach Loop Container

Drag and drop Script Task inside the ForEach Loop Container and set ssas_database_name as ReadOnlyVariables and newly created string type variable ssas_backup_script as ReadWriteVariables. 

Below is the script that should be written.

Public Sub Main()
'
' Add your code here
'
Dts.Variables.Item("ssas_backup_script").Value = _"<Backup xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" 
+ _
" <Object>" + _
" <DatabaseID>" + Dts.Variables.Item("ssas_database_name").Value.ToString + "</DatabaseID>" + _
" </Object>" + _
" <File>" + Dts.Variables.Item("ssas_database_name").Value.ToString + ".abf</File>" + _
" <AllowOverwrite>true</AllowOverwrite>" + _
" <Password>password</Password>" + _
"</Backup>"
Dts.TaskResult = Dts.Results.Success
End Sub

In the script above you can see that the database name is a variable that was updated from the ForEach Loop Container. The backup name is also parameterised. You can improve this by including date and time in the database backup name if you are wish as well.

Next, Drag and drop Analysis Services Execute DDL Task control and in the DDL option set the SourceType to Variable and Source to the variable to ssas_backup_script.

With this, the SSIS package is ready to run and it is just a matter of configuring this SSIS to run from SQL Agent Job so that you can schedule it.

If you need the sample of this SSIS package Drop me an email to dineshasanka@dbfriend.net.

Conclusion

This article try to address a frequently asked question in the SQL Server public forums and hope this will solve many questions of several people out there.