Este post apresenta quatro queries fundamentais para um monitoramento de backup e restore eficaz no SQL Server: Histórico de backups, Databases sem backup há mais de 7 dias ,Histórico de restores, e Consultar backup e restore em andamento. Essas consultas ajudam a monitorar a rotina, garantindo a segurança dos dados e prevenindo falhas.
Com essas queries, é possível verificar detalhes dos backups recentes, identificar restaurações realizadas, listar bancos de dados sem backup há mais de 7 dias e monitorar operações em andamento, estimando o tempo restante para sua conclusão. Além disso, a consulta de histórico de backups permite detectar backups possivelmente corrompidos, garantindo que os dados estejam protegidos de forma eficaz. Dessa forma, a administração do SQL Server se torna mais eficiente e proativa, reduzindo riscos e assegurando a integridade dos backups.
Monitoramento de Backup e Restore
1. Histórico de backup
Essa consulta é útil para auditar e monitorar backups recentes, verificando detalhes como tempo de duração, tamanho, tipo de backup e possíveis falhas. Também permite identificar backups corrompidos (is_damaged), protegidos por senha (is_password_protected) ou com checagem de integridade ativada (has_backup_checksums).
Ver Script:
SELECT
B.[database_name],
(CASE B.[type]
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'TLog Backup'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential Partial'
END) AS BackupType,
B.recovery_model AS RecoveryModel,
B.backup_start_date,
B.backup_finish_date,
CAST(DATEDIFF(SECOND,B.backup_start_date, B.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' AS TotalTimeTaken,
B.expiration_date,
B.[user_name],
B.machine_name,
B.is_password_protected,
B.collation_name,
B.is_copy_only,
CONVERT(NUMERIC(20, 2), B.backup_size / 1048576) AS BackupSizeMB,
A.logical_device_name,
A.physical_device_name,
B.[name] AS backupset_name,
B.[description],
B.has_backup_checksums,
B.is_damaged,
B.has_incomplete_metadata
FROM
sys.databases X
JOIN msdb.dbo.backupset B ON X.[name] = B.[database_name]
JOIN msdb.dbo.backupmediafamily A ON A.media_set_id = B.media_set_id
WHERE
B.backup_start_date >= CONVERT(DATE, DATEADD(DAY, -7, GETDATE()))Exemplo de resultado:
Explicação dos campos
- database_name: Nome do banco de dados
- BackupType: Tipo de backup realizado:
- Full Backup (D): Backup completo da base de dados.
- Differential Backup (I): Apenas as alterações desde o último full backup.
- TLog Backup (L): Backup do log de transações.
- File or filegroup (F): Backup de um arquivo ou grupo de arquivos específico.
- Differential file (G): Backup diferencial de um arquivo.
- Partial (P): Backup parcial do banco de dados.
- Differential Partial (Q): Backup diferencial parcial.
- RecoveryModel: Modelo de recuperação do banco (Full, Simple ou Bulk-Logged).
- backup_start_date / backup_finish_date: Data e hora de início e término do backup.
- TotalTimeTaken: Tempo total de execução do backup em segundos.
- expiration_date: Data de expiração do backup (caso configurado).
- user_name: Usuário que executou o backup.
- machine_name: Identifica o nome do servidor que gerou o backup.
- is_password_protected: Indica se o backup está protegido por senha.
- collation_name: Collation do banco de dados no momento do backup.
- is_copy_only: Se o backup foi realizado como ‘copy-only’ (não interfere na cadeia de backups).
- BackupSizeMB: Tamanho do backup em megabytes.
- logical_device_name: Nome lógico do dispositivo de backup.
- physical_device_name: Exibe o caminho físico do local de armazenamento do backup.
- backupset_name: Nome do conjunto de backup.
- description: Descrição fornecida ao criar o backup.
- has_backup_checksums: Indica se o backup inclui checksums para integridade.
- is_damaged: Indica se o backup está corrompido.
- has_incomplete_metadata: Se há metadados incompletos no backup.
2. Databases sem backup há mais de 7 dias
Essa consulta é útil para identificar bancos que estão em risco devido à falta de backups recentes.
Ver Script:
SELECT
A.[name] AS [database_name],
A.recovery_model_desc,
(SELECT SUM(CAST(size / 128 / 1024.0 AS NUMERIC(18, 2))) FROM sys.master_files WHERE A.[name] = [name]) AS size_GB,
MAX(B.backup_start_date) AS last_backup_date
FROM
sys.databases A
LEFT JOIN msdb.dbo.backupset B ON A.[name] = B.[database_name]
WHERE
(B.backup_set_id IS NULL OR DATEDIFF(DAY, B.backup_start_date, GETDATE()) > 7)
AND A.[name] NOT IN ('tempdb', 'model')
GROUP BY
A.[name],
A.recovery_model_descExemplo de resultado:
Explicação dos campos
- database_name – Nome do banco de dados.recovery_model_desc – Modelo de recuperação configurado para o banco de dados, podendo ser:
- FULL – Registra todas as transações, permitindo restauração ponto a ponto.
- SIMPLE – Não mantém registros detalhados de transações, sendo mais leve, mas com menos opções de recuperação.
- BULK_LOGGED – Similar ao FULL, porém otimizado para operações em massa.
- size_GB – Tamanho total do banco de dados em gigabytes, calculado a partir dos arquivos armazenados no sistema.
- last_backup_date – Data e hora do último backup realizado para o banco de dados. Se o valor for
NULL, significa que nunca houve backup registrado.
3. Histórico de restore
Essa consulta permite auditar as restaurações realizadas na instância, verificando quais bancos foram restaurados, quem realizou a operação e quais backups foram utilizados.
Ver Script:
SELECT
A.[restore_history_id],
A.[restore_date],
A.[destination_database_name],
C.physical_device_name,
A.[user_name],
A.[backup_set_id],
CASE A.[restore_type]
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'Filegroup'
WHEN 'V' THEN 'Verifyonlyl'
END AS RestoreType,
A.[replace],
A.[recovery],
A.[restart],
A.[stop_at],
A.[device_count],
A.[stop_at_mark_name],
A.[stop_before]
FROM
[msdb].[dbo].[restorehistory] A
JOIN [msdb].[dbo].[backupset] B ON A.backup_set_id = B.backup_set_id
JOIN msdb.dbo.backupmediafamily C ON B.media_set_id = C.media_set_id
WHERE
A.restore_date >= CONVERT(DATE, DATEADD(DAY, -7, GETDATE()))Exemplo de resultado:
Explicação dos campos
- restore_history_id: Identificador único do histórico de restauração.
- restore_date: Data e hora em que o restore foi realizado.
- destination_database_name: Nome do banco de dados restaurado.
- physical_device_name: Caminho físico do arquivo de backup utilizado para o restore.
- user_name: Nome do usuário que executou a restauração.
- backup_set_id: Identificador do conjunto de backup utilizado na restauração.
- RestoreType: Tipo de restore realizado, podendo ser:
- D: Restore de banco de dados completo.
- I: Restore diferencial.
- L: Restore de log de transações.
- F: Restore de arquivo.
- G: Restore de grupo de arquivos.
- V: Verificação do backup sem realizar o restore.
- replace: Indica se o banco de dados existente foi substituído no restore.
- recovery: Define se o banco de dados foi recuperado ao final do restore, tornando-o utilizável.
- restart: Indica se o restore foi reiniciado após uma falha anterior.
- stop_at: Caso um restore de log tenha sido realizado com a opção STOPAT, este campo indica a data e hora alvo da recuperação.
- device_count: Número de dispositivos de backup usados no restore.
- stop_at_mark_name: Nome do marcador de transação usado para restaurar até um ponto específico.
- stop_before: Indica se o restore foi interrompido antes do marcador de transação especificado.
4. Consultar backup e restore em andamento
Essa consulta ajuda a monitorar o progresso de operações críticas no banco de dados, evitando interrupções inesperadas e otimizando o planejamento de recursos.
Ver Script:
SELECT
R.session_id,
R.command AS Ds_Operacao,
B.name AS Nm_Banco,
R.start_time AS Dt_Inicio,
CONVERT(VARCHAR(20), DATEADD(MS, R.estimated_completion_time, GETDATE()), 20) AS Dt_Previsao_Fim,
CONVERT(NUMERIC(6, 2), R.percent_complete) AS Vl_Percentual_Concluido,
CONVERT(NUMERIC(6, 2), R.total_elapsed_time / 1000.0 / 60.0) AS Qt_Minutos_Execucao,
CONVERT(NUMERIC(6, 2), R.estimated_completion_time / 1000.0 / 60.0) AS Qt_Minutos_Restantes,
CONVERT(NUMERIC(6, 2), R.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS Qt_Horas_Restantes,
CONVERT(VARCHAR(MAX), ( SELECT
SUBSTRING(text, R.statement_start_offset / 2, CASE WHEN R.statement_end_offset = -1 THEN 1000 ELSE ( R.statement_end_offset - R.statement_start_offset ) / 2 END)
FROM
sys.dm_exec_sql_text(sql_handle)
)) AS Ds_Comando
FROM
sys.dm_exec_requests R WITH(NOLOCK)
JOIN sys.databases B WITH(NOLOCK) ON R.database_id = B.database_id
WHERE
R.command IN (
'BACKUP DATABASE',
'RESTORE DATABASE',
'ALTER INDEX REORGANIZE',
'AUTO_SHRINK option with ALTER DATABASE',
'CREATE INDEX',
'DBCC CHECKDB',
'DBCC CHECKFILEGROUP',
'DBCC CHECKTABLE',
'DBCC INDEXDEFRAG',
'DBCC SHRINKDATABASE',
'DBCC SHRINKFILE',
'KILL',
'UPDATE STATISTICS',
'DBCC'
)
AND R.estimated_completion_time > 0 Exemplo de resultado:
Explicação dos campos
- session_id: Identificador da sessão que está executando a operação.
- Ds_Operacao (command): Tipo de comando em execução, como BACKUP DATABASE ou RESTORE DATABASE.
- Nm_Banco (name): Nome do banco de dados onde a operação está sendo executada.
- Dt_Inicio (start_time): Data e hora de início da operação.
- Dt_Previsao_Fim: Data e hora previstas para a conclusão da operação, calculadas com base no tempo estimado de execução.
- Vl_Percentual_Concluido (percent_complete): Percentual já concluído da operação.
- Qt_Minutos_Execucao (total_elapsed_time): Tempo total decorrido desde o início da operação, convertido para minutos.
- Qt_Minutos_Restantes (estimated_completion_time): Tempo restante estimado para a conclusão da operação, em minutos.
- Qt_Horas_Restantes: Tempo restante estimado para a conclusão, convertido para horas.
- Ds_Comando: Texto do comando SQL que está sendo executado na sessão, permitindo verificar detalhes da operação.
Espero que tenham gostado do conteúdo e que seja útil no seu dia a dia.
Compartilhe com outros profissionais que também possam se beneficiar dessa consulta!
