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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @DatabaseId VARCHAR(128) = 'Cubo_Venda', @DatabaseName VARCHAR(128) = 'Cubo_Venda', @Ds_Data VARCHAR(10) = CONVERT(VARCHAR(10), GETDATE(), 112) DECLARE @CmdBackup varchar(max) = ' <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>' + @DatabaseId + '</DatabaseID> </Object> <File>C:\Backups\OLAP\' + @DatabaseName + '_' + @Ds_Data + '.abf</File> <AllowOverwrite>true</AllowOverwrite> </Backup>' EXEC(@CmdBackup) AT INTERFACE_CUBOS |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE @DatabaseName VARCHAR(128) = 'Cubo_Venda_Restaurado', @Ds_Caminho VARCHAR(255) = 'C:\Backups\OLAP\Cubo_Venda_20170305.abf' DECLARE @CmdRestore varchar(max) = ' <Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <DatabaseName>' + @DatabaseName + '</DatabaseName> <File>' + @Ds_Caminho + '</File> <AllowOverwrite>true</AllowOverwrite> </Restore>' EXEC(@CmdRestore) AT INTERFACE_CUBOS |
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:
|
1 |
SELECT * FROM OPENQUERY(INTERFACE_CUBOS, 'SELECT * FROM $System.DBSCHEMA_CATALOGS') |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
IF (OBJECT_ID('tempdb..#Lista_Cubos') IS NOT NULL) DROP TABLE #Lista_Cubos SELECT *, ROW_NUMBER() OVER(ORDER BY CAST(CATALOG_NAME AS VARCHAR(256))) AS Ranking INTO #Lista_Cubos FROM OPENQUERY(INTERFACE_CUBOS, 'SELECT * FROM $System.DBSCHEMA_CATALOGS') DECLARE @Contador INT = 1, @Total INT = (SELECT COUNT(*) FROM #Lista_Cubos), @Nm_Cubo VARCHAR(256), @CmdBackup VARCHAR(MAX), @Ds_Data VARCHAR(10) = CONVERT(VARCHAR(10), GETDATE(), 112) WHILE(@Contador <= @Total) BEGIN SELECT @Nm_Cubo = CATALOG_NAME FROM #Lista_Cubos WHERE Ranking = @Contador SET @CmdBackup = ' <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>' + @Nm_Cubo + '</DatabaseID> </Object> <File>C:\Backups\OLAP\' + @Nm_Cubo + '_' + @Ds_Data + '.abf</File> <AllowOverwrite>true</AllowOverwrite> </Backup>' EXEC(@CmdBackup) AT INTERFACE_CUBOS SET @Contador += 1 END |
Result:
And that's it folks!
I hope you enjoyed this post.
Hug!
Dirceu, I researched and much about and found no posts as enlightening and complete as yours. Thank you so much for your contribution! Perfect!!!!