Analysis Services - How to use XLMA to backup and restore cubes via the T-SQL command line

Views: 335
Reading Time: 3 minutes

Hello people,
How are you today ?

In this post, I will demonstrate how to use the XML for Analysis (XLMA) language to be able to back up and restore Analysis Services cubes and dimensions through Transact-SQL (T-SQL) queries, which can be used in a SQL Agent job. to automate this process by letting you automate the backup / restore routines of your Analysis Services cubes.

In order to perform the routines described in this post, you will need to have created a Linked Server for integration between SQL Server and Analysis Services (Here in the example, I call this object INTERFACE_CUBOS).

If you have not created this Linked Server and do not know how to do this, visit the post Analysis Services (SSAS) - How to query information and process command line (XLMA) commands through SQL Server and follow the step by step to create it.

How to back up SSAS cubes via T-SQL and XLMA

Now that we have created our connected Linked Server, which we call “INTERFACE_CUBOS”, we will generate the backup of our Analysis Services cube. To do this, simply execute the XLMA command below, using the Linked Server connection.

Result after execution:

Note: By default, backups are already generated using compression. If you do NOT want to compress the generated backup files, use the parameter false after the tag .

How to restore SSAS cubes via T-SQL and XLMA

Just as we did with the backup of the cube using XLMA and the Linked Server “INTERFACE_CUBOS”, we will execute a query to perform the restore from a backup performed.

If you want to restore the backup file by replacing the original Backup cube, just omit the tag that the command will restore by replacing the original cube.

Restore Result:

How to List Analysis Services Instance Cubes

In this post, I demonstrated how to backup and restore an Analysis Services cube using only Transact-SQL, a Linked Server for connection to Analysis Services and an XLMA query. Many DBA's have the need to automate the backup of all the cubes of the instance and it would be very interesting if it were possible to list which cubes are so that it is possible to create a really automatic process.

This requires a simple query to list the Analysis Services cubes:

Query Result:

How to back up all instance cubes

To back up all cubes from your Analysis Services instance using Transact-SQL, simply run the script below:

Result:

And that's it folks!
I hope you enjoyed this post.

Hug!