Analysis Services - Using XLMA for Cube Backup and Restore via T-SQL Command Line

Views: 321
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, where we call it “INTERFACE_CUBOS”, let's back up our Analysis Services cube. To do this, simply run 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 <ApplyCompression> false </ApplyCompression> parameter after the <AllowOverwrite> tag.

How to restore SSAS cubes via T-SQL and XLMA

Just as we did with the cube backup using XLMA and Linked Server “INTERFACE_CUBOS”, let's run a query to restore from a backup.

If you want to restore the backup file by replacing the original Backup cube, just omit the <DatabaseName> 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 back up and restore an Analysis Services cube using only Transact-SQL, a Linked Server for connecting to Analysis Services, and an XLMA query. Many DBAs have a need to automate the backup of all instance cubes and it would be very interesting to list which cubes to create a truly automated 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:


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