Log Backup Failures – Danger, Will Robinson!
For databases in Full Recovery Model, log backups should be taken at a frequency that ensures your recovery point objective can be met. So you schedule log backups to occur, setup email notifications so you can respond log backup failures (and they invariably will fail at some point!), then go out for tacos, secure in the knowledge you’ll be able to restore that critical production data at the drop of a hat.
But wait, what if that log backup job doesn’t run, and you fail to notice for whatever reason?
Retro TV Shows aside, this is a pretty cool meme!
Use this script regularly to confirm the last time a log backup was taken for all the “Full Recovery Model” databases you’re responsible for, and avoid log backup failures.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
DECLARE @MaxLogBackupAgeInMinutes int = 30; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; ;WITH BackupHistory AS ( SELECT ServerName = COALESCE(bs.server_name, @@SERVERNAME) , DatabaseName = COALESCE(bs.database_name, d.name) , BackupStartDate = bs.backup_start_date , ExpirationDate = bs.expiration_date , BackupSetName = bs.name , PhysicalDeviceName = bmf.physical_device_name , RowNum = ROW_NUMBER() OVER (PARTITION BY COALESCE(bs.database_name, d.name), bs.type ORDER BY bs.backup_start_date DESC) FROM master.sys.databases d LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = d.name LEFT JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id WHERE d.state_desc = N'ONLINE' AND d.name <> N'tempdb' AND d.recovery_model_desc = N'FULL' AND ( bs.server_name IS NULL OR bs.server_name = @@SERVERNAME ) AND (bs.type = 'L' OR bs.type IS NULL) ) SELECT bh.ServerName , bh.DatabaseName , BackupAgeInMinutes = DATEDIFF(MINUTE, bh.BackupStartDate, GETDATE()) , bh.BackupStartDate , bh.ExpirationDate , bh.PhysicalDeviceName FROM BackupHistory bh WHERE bh.RowNum = 1 AND (DATEDIFF(MINUTE, bh.BackupStartDate, GETDATE()) > @MaxLogBackupAgeInMinutes OR bh.BackupStartDate IS NULL) ORDER BY bh.DatabaseName , bh.BackupStartDate DESC; |
As is, the code shows databases that haven’t had a BACKUP LOG action taken in the past 30 minutes – you’ll want to adjust that to match the frequency of your scheduled log backups.
Check the Microsoft Documentation for details on BACKUP, and the dbo.backupset and dbo.backupmediafamily MSDB tables.
Take a look at the rest of our series on Recovery, and SQL Server Agent.