Hola, chicos,
¿Cómo estás hoy?

En esta publicación, demostraré cómo usar el lenguaje XLMA (XML para análisis) para realizar copias de seguridad y restaurar cubos y dimensiones de Analysis Services a través de consultas Transact-SQL (T-SQL), que se pueden usar en un trabajo del Agente SQL para automatizar este proceso, permitiéndole automatizar las rutinas de copia de seguridad/restauración de sus cubos de Analysis Services.

Para poder realizar las rutinas descritas en este post, necesitarás haber creado un Servidor Vinculado para la integración entre SQL Server y Analysis Services (Aquí en el ejemplo, a este objeto lo llamo INTERFACE_CUBOS).

Si no has creado este Servidor Enlazado y no sabes cómo hacerlo accede al post Analysis Services (SSAS): cómo consultar información y procesar comandos a través de la línea de comandos (XLMA) a través de SQL Server y sigue el paso a paso para crearlo.

Cómo hacer una copia de seguridad de cubos SSAS a través de T-SQL y XLMA

Ahora que hemos creado nuestra conexión de servidor vinculado, al que llamamos “INTERFACE_CUBOS”, generemos una copia de seguridad de nuestro cubo de Analysis Services. Para hacer esto, simplemente ejecute el comando XLMA a continuación, utilizando la conexión del servidor vinculado.

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

Resultado después de la ejecución:

Nota: De forma predeterminada, las copias de seguridad ya se generan mediante compresión. Si NO desea comprimir los archivos de copia de seguridad generados, utilice el parámetro false después de la etiqueta .

Cómo restaurar cubos SSAS a través de T-SQL y XLMA

Tal como hicimos con la copia de seguridad del cubo usando XLMA y el servidor vinculado “INTERFACE_CUBOS”, ejecutaremos una consulta para realizar la restauración a partir de una copia de seguridad realizada.

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

Si desea restaurar el archivo de copia de seguridad reemplazando el cubo de copia de seguridad original, simplemente omita la etiqueta y el comando lo restaurará reemplazando el cubo original.

Resultado de la restauración:

Cómo enumerar cubos de instancias de Analysis Services

En esta publicación, demostré cómo hacer una copia de seguridad y restaurar un cubo de Analysis Services usando solo Transact-SQL, un servidor vinculado para conectarse a Analysis Services y una consulta XLMA. Muchos DBA necesitan automatizar la copia de seguridad de todos los cubos en la instancia y sería muy interesante si fuera posible enumerar qué cubos hay para que sea posible crear un proceso verdaderamente automático.

Para hacer esto, basta con una simple consulta para listar los cubos de Analysis Services:

SELECT * FROM OPENQUERY(INTERFACE_CUBOS, 'SELECT * FROM $System.DBSCHEMA_CATALOGS')

Resultado de la consulta:

Cómo crear una copia de seguridad de todos los cubos de instancia

Para crear una copia de seguridad de todos los cubos en su instancia de Analysis Services usando Transact-SQL, simplemente ejecute el siguiente script:

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

Resultado:

¡Y eso es todo, amigos!
Espero que te haya gustado esta publicación.

¡Abrazo!