Log Backup 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 when those log backups fail (and they invariably will 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.

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 you scheduled log backups.