
Applies to SQL Server 2019, 2022 and 2025.
A backup report proves that every database has a recent backup, across the whole estate, every day. “The backup job succeeded” isn’t the same thing. Jobs skip databases that were added last week, maintenance plans quietly miss one that went offline, and an availability group can take its backups on a replica nobody’s checking.
Mine is built on the database inventory. It looks at the last full, differential and log backup of every database and marks each one OK or overdue against thresholds that match the recovery point we’ve agreed with the business.
What the report shows
- Every database on every server, with its recovery model.
- The last full, differential and log backup, and hours since the full and minutes since the log.
- A status: NEVER BACKED UP, FULL OVERDUE, DAILY OVERDUE, LOG OVERDUE or OK. Databases that aren’t online are flagged CHECK so they don’t slip through as “fine”.
Never-backed-up databases sort to the top, then the oldest full backups.
Why it matters
Backups are the one thing a DBA can’t get wrong. Every other mistake can be fixed. A missing backup on the day you need it can’t.
The report has to cover databases nobody told you about. The commonest gap I find is a database created by an application install or a developer, which isn’t in any backup job because the job lists databases by name. Because this report starts from sys.databases, a new database shows as NEVER BACKED UP the next morning.
Log backups need their own check. A FULL recovery database with no log backups has a log that grows until the disk fills, and you can only restore it to the last full or differential. Both are bad, and only one of them shows up as an alert on its own.
Where the data comes from
The collection is done by the database inventory script, which reads msdb.dbo.backupset on each instance and stores the last backup of each type. This page is the report on top of that data. Set it up first, then run the query below in the DBA database.
The report query
The three thresholds at the top are the ones to change. Mine assume a daily full or differential, a weekly full at least, and log backups every 15 minutes with an hour’s tolerance. Yours should come from the recovery point objective the business signed off.
-- Backup report from the latest DatabaseInventory capture (run in the DBA database).
-- Set the thresholds to match your agreed RPO, not mine.
DECLARE @FullOrDiffHours INT = 26, -- daily full or differential, plus two hours' slack
@FullDays INT = 8, -- weekly full, plus a day
@LogMinutes INT = 60; -- FULL / BULK_LOGGED databases
WITH Latest AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName ORDER BY CaptureDate DESC) AS RN
FROM dbo.DatabaseInventory
WHERE CollectionStatus = 'SUCCESS'
AND CaptureDate >= DATEADD(DAY, -2, SYSDATETIME())
),
Newest AS
(
SELECT * FROM Latest WHERE RN = 1
),
AgBest AS
(
-- An AG database can be backed up on any replica: take the newest backup across all of them
SELECT DatabaseName,
MAX(LastFullBackup) AS LastFullBackup,
MAX(LastDiffBackup) AS LastDiffBackup,
MAX(LastLogBackup) AS LastLogBackup
FROM Newest
WHERE IsInAG = 1
GROUP BY DatabaseName
),
Effective AS
(
SELECT
C.ServerName, C.DatabaseName, C.RecoveryModel, C.StateDesc, C.IsInAG,
COALESCE(A.LastFullBackup, C.LastFullBackup) AS LastFullBackup,
COALESCE(A.LastDiffBackup, C.LastDiffBackup) AS LastDiffBackup,
COALESCE(A.LastLogBackup, C.LastLogBackup) AS LastLogBackup,
C.CaptureDate
FROM Newest AS C
LEFT JOIN AgBest AS A
ON A.DatabaseName = C.DatabaseName AND C.IsInAG = 1
)
SELECT
ServerName,
DatabaseName,
RecoveryModel,
LastFullBackup,
LastDiffBackup,
LastLogBackup,
DATEDIFF(HOUR, LastFullBackup, CaptureDate) AS HoursSinceFull,
DATEDIFF(MINUTE, LastLogBackup, CaptureDate) AS MinutesSinceLog,
CASE
WHEN StateDesc <> 'ONLINE' THEN 'CHECK: ' + StateDesc
WHEN LastFullBackup IS NULL THEN 'NEVER BACKED UP'
WHEN LastFullBackup < DATEADD(DAY, -@FullDays, CaptureDate) THEN 'FULL OVERDUE'
WHEN CASE WHEN LastDiffBackup > LastFullBackup THEN LastDiffBackup ELSE LastFullBackup END
< DATEADD(HOUR, -@FullOrDiffHours, CaptureDate) THEN 'DAILY OVERDUE'
WHEN RecoveryModel <> 'SIMPLE' AND DatabaseName <> 'model'
AND (LastLogBackup IS NULL
OR LastLogBackup < DATEADD(MINUTE, -@LogMinutes, CaptureDate)) THEN 'LOG OVERDUE'
ELSE 'OK'
END AS BackupStatus
FROM Effective
ORDER BY CASE WHEN LastFullBackup IS NULL THEN 0 ELSE 1 END, HoursSinceFull DESC, ServerName, DatabaseName;
For databases in an availability group, the query takes the newest backup across all the replicas, so it doesn’t matter which one runs the job.
Checking the gaps between log backups
The last log backup only tells you about now. This one runs on each instance and shows the longest gap between log backups over the last 24 hours. If the log job hung for three hours overnight and then recovered, the main report says OK this morning. This one doesn’t.
-- Longest gap between log backups in the last 24 hours (run on each instance).
-- The longest gap, not the average, is the data you would have lost.
WITH LogBackups AS
(
SELECT database_name,
backup_finish_date,
has_backup_checksums,
LAG(backup_finish_date) OVER (PARTITION BY database_name
ORDER BY backup_finish_date) AS PreviousFinish
FROM msdb.dbo.backupset
WHERE type = 'L'
AND server_name = @@SERVERNAME
AND backup_finish_date >= DATEADD(HOUR, -24, GETDATE())
)
SELECT
database_name AS DatabaseName,
COUNT(*) AS LogBackups24h,
MAX(DATEDIFF(MINUTE, PreviousFinish, backup_finish_date)) AS LongestGapMinutes,
AVG(DATEDIFF(MINUTE, PreviousFinish, backup_finish_date)) AS AverageGapMinutes,
MAX(backup_finish_date) AS LastLogBackup,
SUM(CASE WHEN has_backup_checksums = 0 THEN 1 ELSE 0 END) AS WithoutChecksum
FROM LogBackups
GROUP BY database_name
ORDER BY LongestGapMinutes DESC;
The longest gap is your real recovery point for that day: it’s the most data you’d have lost if the server had died at the worst moment.
Watch out for
- A backup isn’t proven until it’s restored. This report proves the backups exist. Restore a sample every month, ideally automatically, and run
DBCC CHECKDBon the restored copy. Report that as well. - Checksums. Back up
WITH CHECKSUMso a damaged page fails the backup instead of being copied silently. The log gap query counts backups without one. - Copy-only and third-party backups. The inventory counts every backup in msdb, including copy-only ones and those taken by VM snapshot tools that register with SQL Server. A snapshot backup every night can make the report look perfect while your own jobs have stopped. Check which tool wrote the backups as well as when.
- Same database name in two availability groups. The AG step groups by database name. If two AGs both have a database called
Staging, they’re merged. Rename one, or add the AG name to the inventory. - Backups on the same disk as the data. If the drive fails you lose both. Check
physical_device_nameinmsdb.dbo.backupmediafamilynow and then. - model in FULL recovery. New databases copy model’s recovery model. The report ignores log backups for model itself, but every new database will need them.
Where it fits
This is part of my Auditing section, built on the database inventory. Backup strategy and restores are covered in the SQL Server DBA Handbook, and if you use encryption, Backing Up the Database Master Key covers the part a normal backup doesn’t.
The other Auditing reports: Licensing | Availability: ping and response time | Disk space alerts | Server inventory | Database inventory | SSIS errors
Clive Roberts DBA
2 thoughts on “Backup Report: Is Every Database Backed Up?”
Comments are closed.