Documentação: Bancos de dados, backups e planos de manutenção

Bom dia pessoal.

Um DBA (acho que todo profissional) deveria ter como um de seus focos a excelência na documentação do que ele está trabalhando, claro que isso não é verdade na maioria esmagadora dos casos, inclusive eu preciso melhorar bastante neste aspecto (hehehe). Fica registrada a tarefa e o heads up para nós.

Dito isso, na semana passada eu estava discutindo com um cliente a documentação do SQL Server e me agonia profundamente não termos living documents, mas sim aquele documento do Word ou Excel que em 10 dias já está todo desatualizado. Caímos dentro da rotina de backups e, quando feito através dos planos de manutenção, o diabo vira uma caixa preta.

Pergunto: em qual tabela de sistema temos uma listagem dos bancos de dados que estão cobertos pelo plano de manutenção?
Fiz algumas pesquisas rápidas e não encontrei nada referente a isso.
Consulta rápida no MSDB, encontramos o “dbo.sysssispackages” e lá está o binário do plano de manutenção. Brincando um pouco com o XML do plano de manutenção cheguei à consulta abaixo…

DISCLAIMER: não estudei o XSD ou referência detalhada ao XML dos pacotes do SSIS que contém os planos de manutenção, somente testei com o SQL Server 2008 R2 SP1, foi uma coisa rápida que eu produzi sem grandes cuidados e não garanto que vai funcionar em todos os casos, ok? É apenas um trecho que código que você pode evoluir ou eventualmente ser útil para alguém.

Código 01 – Consulta bancos do plano de execução
DECLARE @Dado XML
SELECT @Dado = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
FROM dbo.sysssispackages AS S
WHERE S.name = 'Plano01'

;WITH XMLNAMESPACES('www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask
    , 'www.microsoft.com/SqlServer/Dts' AS DTS)
SELECT
    RelOp.op.value(N'(@SQLTask:DatabaseName)', N'VARCHAR(1000)') AS DatabaseName
    , (CASE RelOp.op.value(N'(../@SQLTask:BackupAction)', N'INT')
        WHEN 0 THEN 'Data'
        WHEN 2 THEN 'Log'
        ELSE NULL
    END) AS BackupType
    , RelOp.op.value(N'(../@SQLTask:BackupIsIncremental)', N'VARCHAR(100)') AS BackupIsIncremental   
FROM @Dado.nodes(N'//DTS:ObjectData/SQLTask:SqlTaskData/SQLTask:SelectedDatabases') RelOp (Op)
GO

Esse código já evoluiu bastante no meu projetinho, pois é possível você cruzar os dados com jobs, jobsteps, schedules, etc. Aí você vai ter um detalhamento completo entre planos de execução, jobs e seus agendamentos, sendo possível documentar propriamente sua rotina de backup e manter os documentos facilmente atualizáveis. Sem precisar especificar no WHERE qual o plano você está interessado (#KidsModeON).

Por curiosidade, achei muito fraco o elo que o SQL Server faz entre esses elementos, então vou ter que estudar mais um pouco para fazer uma crítica consistente, senão posso acabar falando besteira por aqui.

Se vou publicar o código completo? Ainda não sei, no momento a versão “full” faz parte do Script Master DBA Nimbus Automator Tabajara hehehe

Se você testar com outra versões do SQL Server, planos complexos ou qualquer outro cenário, me conte se funcionou.

[]s
Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
www.srnimbus.com.br