Olá pessoal,
Como vocês estão hoje ?

Neste post, vou demonstrar como utilizar a linguagem XLMA (XML for Analysis) para conseguir realizar backup e restore de cubos e dimensões do Analysis Services através de queries Transact-SQL (T-SQL), que podem ser utilizadas em um job do SQL Agent para automatizar esse processo, permitindo que você automatize as rotinas de backup/restore de seus cubos do Analysis Services.

Para que seja possível realizar as rotinas descritas neste post, você precisará ter criado um Linked Server de integração entre o SQL Server e o Analysis Services (Aqui no exemplo, eu chamo esse objeto de INTERFACE_CUBOS).

Caso você não tenha criado esse Linked Server e não saiba como fazer isso, acesse o post Analysis Services (SSAS) – Como consultar informações e processar comandos via linha de comando (XLMA) pelo SQL Server e siga o passo a passo para criá-lo.

Como fazer backup de cubos do SSAS via T-SQL e XLMA

Agora que já criamos o nosso Linked Server de conexão, onde chamamos de “INTERFACE_CUBOS”, vamos gerar o backup do nosso cubo do Analysis Services. Para fazer isso, basta executar o comando XLMA abaixo, utilizando a conexão do Linked Server.

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 após a execução:

Observação: Por padrão, os backups já são gerados utilizando compressão. Caso você NÃO queira compactar os arquivos de backup gerados, utilize o parâmetro <ApplyCompression>false</ApplyCompression> após a tag <AllowOverwrite>.

Como fazer restore de cubos do SSAS via T-SQL e XLMA

Assim como fizemos com o backup do cubo utilizando o XLMA e o Linked Server “INTERFACE_CUBOS”, vamos executar uma query para realizar o restore a partir de um backup realizado.

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

Caso você queira restaurar o arquivo de backup substituindo o cubo original do Backup, basta omitir a tag <DatabaseName> que o comando irá fazer o restore substituindo o cubo original.

Resultado do Restore:

Como listar os cubos da instância do Analysis Services

Neste post, demonstrei como fazer o backup e restore de um cubo do Analysis Services utilizando apenas Transact-SQL, um Linked Server para conexão com o Analysis Services e uma query XLMA. Muitos DBA’s tem a necessidade de automatizar o backup de todos os cubos da instância e seria muito interessante que fosse possível listar quais são os cubos para que seja possível criar um processo realmente automático.

Para isso, basta uma simples query para listar os cubos do Analysis Services:

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

Resultado da consulta:

Como criar um backup de todos os cubos da instância

Para criar um backup de todos os cubos da sua instância do Analysis Services utilizando o Transact-SQL, basta executar o script abaixo:

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:

E é isso aí, pessoal!
Espero que tenham gostado desse post.

Abraço!