☰

A exclusão automática de backups e logs de transações deixa pontos de backup quebrados

tag-icon • tag-icon sql-server backup-restoration ssms incremental-backup
A exclusão automática de backups e logs de transações deixa pontos de backup quebrados

TLDR: o trabalho de envio de log padrão e o plano de manutenção estão excluindo arquivos antigos do disco, mas os backups ainda são exibidos no SSMS, tornando a exibição da janela "Restaurar" muito lenta. Como excluir .trn/.bak antigo corretamente?

(Máquina virtual do Azure, Windows Server 2016 Datacenter (10.0), Microsoft SQL Server Web (nível de compatibilidade 2017), SMMS 17.3)

(as datas exibidas estão no formato AAAA-MM-DD ou DD-MM-AAAA com relógio de 24h)

Gerenciar bancos de dados não está em meu conjunto de habilidades nem na descrição de meu trabalho, mas fui encarregado de preparar backups no MS Sql Server, portanto, trate-me como um leigo neste assunto.

Decidi criar backups completos do banco de dados todos os dias (copiados para armazenamento externo, é claro), manter alguns deles no servidor por conveniência, excluir os antigos e, em seguida, preencher lacunas com logs de transações para restauração pontual por alguns dias. Depois de pesquisar um pouco no Google, segui os bancos de dados de teste:

  1. Criado backup completo manual, pois é necessário para o envio de logs - nada de errado aqui

  2. Tarefa de agendamento de envio de log de transações padrão gerada com exclusão de arquivos mais antigos. Funciona bem, envia spam para .trn a cada minuto e exclui arquivos antigos do disco trabalho de envio de log

  3. Configure o plano de manutenção para criar e excluir backups completos antigos para bancos de dados de teste à meia-noite - sem problemas aqui também

plano de manutenção

  1. Deixe rodar de sexta a segunda, tanto .trn quanto .bak foram criados e excluídos corretamente, então eu tive backups de dois dias para brincar: Histórico

  2. Tentei restaurar a partir de backups, o que causou problemas devido à falta de arquivos de transação. A abertura de Tasks\Restore\Database é muito, muito lenta, o SMMS está triturando 100% do disco por vários minutos para aproximadamente 10 MB de .trn de banco de dados vazio. Para banco de dados sem backup completo automático, ele listava todos os .trn já criados (e backup completo manual em primeiro lugar), obviamente acompanhados de erros de "arquivo não encontrado". Para banco de dados com backup completo diário, por padrão, o resultado limitado volta para o último backup e restauração completos funcionaram corretamente, mas ainda era muito lento exibir apenas a lista de backups. A navegação na linha do tempo também é muito lenta (vários minutos de "não resposta").

Encontrei um script para buscar o histórico do script emhttps://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/(ligeiramente modificado para retornar todos os backups do banco de dados de interesse)

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE 
--(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
--and
 [database_name] = 'test'
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date

e inclui arquivos .trn e .bak excluídos: insira a descrição da imagem aqui

e assim por diante até o momento, totalizando 29.682 pontos de backup.

Gostaria que a janela "Restaurar" respondesse novamente e tenho dois bancos de dados de teste para testar livremente. Posso limpar a lista de backup com segurança? Como posso torná-lo automático, para acompanhar bem meus trabalhos de agendamento atuais?

Tenho privilégios totais de administração no banco de dados e no sistema operacional, controle criativo total e muitos recursos restantes na VM, portanto, dificilmente há limitações para possíveis soluções.

Responder1

É possível usar osp_delete_backuphistoryprocedimento armazenado para limpar o histórico antigo. Observe que ele não sabe o que você excluiu ou não, mas limpará o histórico mais antigo que a data X fornecida.

Depois de limpar a grande quantidade de entradas de backup, ele deverá responder. Isso pode ser facilmente incluído em seu trabalho de agente ligando para o SP com a data atual menos X número de dias como a última etapa do trabalho.

informação relacionada