How to take analysis services database backups step by step

How to take analysis services database backups step by step

We can take database backup of analysis services in different ways. I will show some of the ways to take analysis services database backup using SQL Server native methods.

Using SQL Server Management Studio (SSMS):

Connect SQL Server Analysis Services. In order connect analysis services using SQL Server management studio Analysis Services must running. 

To verify analysis service is running go to configuration manager under configuration tools in SQL Server2014 folder.

 If services not started start the service.

Right Click on Analysis Service Database and click backup as shown below.

Specify the database backup file name and folder path

Allows file overwrite allows to over write the analysis service database if exists in mentioned folder.

Apply Compression will apply compression

Encrypt data file will encrypts the analysis service database file. 

You must provide a password to encrypt the file. Same password must be provided while restoring the database. For performing these operations you must have admin permissions for the server.

Using the XMLA Script you can take database backup

Then execute the following XMLA script for database backup.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Object>

    <DatabaseID>AnalaysisDatabase2014</DatabaseID>

  </Object>

  <File>AnalaysisDatabase2014.abf</File>

  <AllowOverwrite>true</AllowOverwrite>

</Backup>

Analysis service used the XML Analysis script for talking database backup.

Execute the above the script will complete the database backup.

Using SQL Server Integration services data tools (SSIS)

  Using the analysis service execute DDL task it will open the Task editor.

In general tab provides the description of the task to be performed.

Then go to the DDL tab click on new connection. 

You can provide the server name in the connection string else edit the connection and test the connection string is proper by click on test connection.

If any errors in connection it will notify the error message. 

Select the source type as direct input. Alternatively the script can be executed from file or variables.

 In SourceDirect copy the script above XMLA script replace database id with your database id and file name. Click ok. Verify for any errors in the task. The task will show as below if there are no errors.

Execute the task by Right click and click on execute task.

The execute task successfully executed.

Similarly you can restore the analysis service database backup using the same task. Using SSMS also you can restore the SQL Server analysis service database backup.

First right click on databases and click on restore as shown above.

Then browse the backup file. you restore the full database and data files. similarly we can over ride the database files. I have selected the restore as new database instead of overriding. using the partitions tab we can restore only some partitions of the database if the database is partitioned.

If the database is protected by password then it must be provided while restoring the database. click ok. the new database restored successfully.