Listando os últimos backups

Na última semana um DBA me questionou como criar um script para listar os backups executados, a ideia dele é criar um script que gera essa lista periodicamente.

Então nesse post vou descrever como criar um script para resolver esse problema. Para iniciar o assunto vou apresentar duas tabelas do MSDB que serão importantes para a criação deste script.

 

1.     backupset;

2.     backupmediafamily.

 

A primeira tabela diz respeito ao registro dos conjuntos de backup, cada linha de retorno corresponderá a um backup, para esse script em especial vamos prestar atenção nas colunas: Type, backup_start_date e media_set_id.

• Type: indica o tipo de backup e abaixo segue os tipos de backups possíveis. É importante salientar que é possível o valor nulo:
  • D = Full Backup;
  • I = Differential;
  • L = Log;
  • F = File/Filegroup;
  • G = Differential file;
  • P = Partial;
  • Q = Differential partial.
• backup_start_date: o retorno deste campo é um datetime com o registro da data e horário em que foi realizado o backup;
• media_set_id: identificador do número da mídia de backup.

A segunda tabela refere-se ao registro das famílias de backups, desta tabela vamos utilizar o campo identificador media_set_id que servirá para fazer uma junção com a tabela backupset.

Além dessas duas tabelas utilizaremos a sysdatabases que pertence ao banco de dados MASTER. Será através desta tabela que descobriremos os bancos de dados existentes.

Agora que já estamos familiarizados com as principais consultas que vamos executar, podemos analisar a consulta abaixo. A mesma apresentará os últimos backups executados dos databases existentes.

SELECT  A.database_name [Banco de dados] ,
        CASE WHEN a.type = 'D' THEN 'Full'
             WHEN A.type = 'I' THEN 'Differential'
             WHEN A.type = 'L' THEN 'Log'
             WHEN a.type = 'F' THEN 'File/Filegroup'
             WHEN a.type = 'G' THEN 'Differential - file'
             WHEN A.type = 'P' THEN 'Partial'
             WHEN A.type = 'Q' THEN 'Differential partial'
        END AS [Tipo de backup] ,
        MAX(A.backup_start_date) [Data do Backup]
FROM    msdb..backupset AS A
        LEFT JOIN msdb..backupmediafamily AS B
        ON A.media_set_id = B.media_set_id
WHERE   A.database_name IN ( SELECT name
                             FROM   sysdatabases c )
                             GROUP BY A.database_name, A.type

O resultado desta consulta será algo similar à imagem abaixo. Ressalto que a imagem que segue é uma representação do meu ambiente.

Apesar desse script ser muito útil, uma nova formatação do mesmo poderá facilitar o diagnóstico de falha de algum backup. Por exemplo, se o ambiente possuir muitos bancos de dados, será difícil perceber a ausência de algum backup.

Para isso, podemos reformular a consulta buscando todos os bancos de dados existentes na sysdatabases que tenham registros na backupset.

Vejamos o script abaixo:

SELECT  A.name [Banco de dados] ,
        CASE WHEN B.type = 'D' THEN 'Full'
             WHEN B.type = 'I' THEN 'Differential'
             WHEN B.type = 'L' THEN 'Log'
             WHEN B.type = 'F' THEN 'File/Filegroup'
             WHEN B.type = 'G' THEN 'Differential - file'
             WHEN B.type = 'P' THEN 'Partial'
             WHEN B.type = 'Q' THEN 'Differential partial'
        END AS [Tipo de backup] ,
        MAX(B.backup_start_date) [Data do Backup]
FROM    master..sysdatabases A
        LEFT JOIN msdb..backupset B
        ON A.name = B.database_name
        LEFT JOIN msdb..backupmediafamily AS C
        ON B.media_set_id = C.media_set_id
GROUP BY A.name ,
        B.type

O retorno desta consulta será algo similar à imagem abaixo:

Desejo que esse script seja útil a todos que necessitam automatizar o monitoramento dos backups.

 

Abraço, Rodrigo