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.

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.