DBA Scripts: Get Backup Coverage

January 6, 2026

–

Part of the DBA-Tools Project.

Most production SQL Server environments have backup jobs configured. The question that actually matters is whether those jobs are running, and succeeding, for every database that needs them. A database added last month might never have been folded into the backup plan. A job that’s been failing silently for weeks looks identical to a healthy one until someone checks.

SQL Server Agent doesn’t fail loudly. A backup job that breaks at 2am doesn’t surface itself until someone tries to restore at 2pm and finds the latest backup is days old. “No alert fired” is not the same thing as “the backup ran.”

This script checks every user database against msdb’s backup history and returns a single status flag per database, so gaps and stale backups are visible at a glance instead of buried in a column of dates.

Why Backup Coverage Matters

A missing or stale backup is invisible until the moment you need it, and by then it’s too late to fix. Coverage checks close that gap before it becomes an incident:

A database with no full backup on record has zero recovery options, full stop
A FULL recovery model database with no log backups is accumulating transaction log growth with no point-in-time recovery to show for it
A backup job silently failing looks, from the outside, identical to one that never existed for a given database
Backup coverage is one of the first things worth checking on any inherited or newly onboarded server, before touching anything else

When to Run This Script

Routine SQL Server health checks
Taking ownership of a new or inherited instance
After adding a new database, to confirm it’s actually in the backup plan
Investigating recovery options during an incident
Verifying backup jobs after a schedule or configuration change

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-BackupCoverage
Category    : backups-and-recovery
Purpose     : Review backup coverage per database with a status flag for quick health assessment.
Author      : Peter Whyte (https://sqldba.blog/script-check-backup-coverage-across-all-databases/)
Requires    : VIEW ANY DATABASE, db_datareader on msdb
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

WITH latest_backups AS (
    SELECT
        bs.database_name,
        bs.backup_finish_date,
        bs.type,
        bs.backup_size / 1024.0 / 1024 AS backup_size_mb,
        ROW_NUMBER() OVER (
            PARTITION BY bs.database_name, bs.type
            ORDER BY bs.backup_finish_date DESC
        ) AS rn
    FROM msdb.dbo.backupset AS bs
)
SELECT
    d.name                                                                              AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN lb.type = 'D' THEN lb.backup_finish_date END)                        AS last_full_backup,
    MAX(CASE WHEN lb.type = 'D' THEN DATEDIFF(HOUR, lb.backup_finish_date, GETDATE()) END)
                                                                                        AS full_backup_age_hours,
    MAX(CASE WHEN lb.type = 'D' THEN lb.backup_size_mb END)                            AS full_backup_size_mb,
    MAX(CASE WHEN lb.type = 'I' THEN lb.backup_finish_date END)                        AS last_diff_backup,
    MAX(CASE WHEN lb.type = 'I' THEN DATEDIFF(HOUR, lb.backup_finish_date, GETDATE()) END)
                                                                                        AS diff_backup_age_hours,
    MAX(CASE WHEN lb.type = 'L' THEN lb.backup_finish_date END)                        AS last_log_backup,
    MAX(CASE WHEN lb.type = 'L' THEN DATEDIFF(HOUR, lb.backup_finish_date, GETDATE()) END)
                                                                                        AS log_backup_age_hours,
    CASE
        WHEN MAX(CASE WHEN lb.type = 'D' THEN lb.backup_finish_date END) IS NULL
            THEN 'NO_FULL_BACKUP'
        WHEN MAX(CASE WHEN lb.type = 'D' THEN DATEDIFF(HOUR, lb.backup_finish_date, GETDATE()) END) > 25
            THEN 'STALE_FULL'
        WHEN d.recovery_model_desc IN ('FULL', 'BULK_LOGGED')
         AND MAX(CASE WHEN lb.type = 'L' THEN lb.backup_finish_date END) IS NULL
            THEN 'FULL_RECOVERY_NO_LOG'
        WHEN d.recovery_model_desc IN ('FULL', 'BULK_LOGGED')
         AND MAX(CASE WHEN lb.type = 'L' THEN DATEDIFF(HOUR, lb.backup_finish_date, GETDATE()) END) > 4
            THEN 'STALE_LOG'
        ELSE 'OK'
    END                                                                                 AS backup_status
FROM sys.databases AS d
LEFT JOIN latest_backups AS lb
    ON d.name = lb.database_name
   AND lb.rn  = 1
WHERE d.database_id > 4
GROUP BY d.name, d.recovery_model_desc
ORDER BY
    CASE WHEN MAX(CASE WHEN lb.type = 'D' THEN lb.backup_finish_date END) IS NULL THEN 0
         ELSE 1 END,
    MAX(CASE WHEN lb.type = 'D' THEN DATEDIFF(HOUR, lb.backup_finish_date, GETDATE()) END) DESC;

The script returns one row per user database with a backup_status flag, ordered worst-first so the most urgent problems sort to the top.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check backup coverage and status across all user databases:
.\run.ps1 Get-BackupCoverage

# To run against a remote sql server:
.\run.ps1 Get-BackupCoverage -ServerInstance SQLSERVER01

This script lives in the repo at:

Example Output

Real output, captured against a local lab instance carrying 20 user databases right now. Every single one has a live finding: 5 with no full backup on record at all, and 15 flagged stale, including four databases backed up only 28 hours ago. That last group is a genuinely useful edge case: this script’s STALE_FULL threshold is 25 hours, so a backup one day old already trips it. A daily backup job with any delay or a missed run crosses that line fast.

Understanding the Results

The backup_status column is the one to read first. The result set is ordered worst-first, so the databases needing attention are always at the top.

NO_FULL_BACKUP. No full backup record exists in msdb for this database. Either it was never backed up, or the backup history has been purged. Either way, there is currently no way to recover this database, and that’s the most urgent state this script can report.

STALE_FULL. The last full backup completed more than 25 hours ago. That threshold gives a daily backup job roughly an hour of grace for a delayed start; if your schedule is weekly instead of daily, adjust the threshold in the script rather than treating every row as urgent.

FULL_RECOVERY_NO_LOG. The database is in FULL or BULK_LOGGED recovery model but has never had a log backup. The transaction log will grow without bound until something backs it up or someone shrinks it and breaks the chain, and there is no point-in-time recovery in the meantime.

STALE_LOG. FULL or BULK_LOGGED recovery model with no log backup in the last 4 hours. Adjust the threshold if your log backup schedule is less frequent than hourly.

OK. Every check passed. This is the state every production database should be in most of the time; seeing zero OK rows on an instance, as in the example output above, is itself a signal worth investigating.

Common Causes

A database added to the instance after the backup jobs were configured, so it was never folded into the schedule
A backup job failing silently, especially without job failure alerting wired up
Migration or lab databases seeded once and never added to a real recurring backup plan, the pattern behind every NO_FULL_BACKUP row above
msdb backup history pruned by a maintenance or cleanup job, which can make a genuinely backed-up database look like NO_FULL_BACKUP if the history itself is gone rather than the backup

How to Fix Missing or Stale Backup Coverage

For NO_FULL_BACKUP and STALE_FULL, confirm the database is actually included in the backup job, check the SQL Agent job history, and take a backup now if one is genuinely missing:

BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\SQL-Backups\YourDatabase_FULL.bak'
WITH COMPRESSION, CHECKSUM;

For FULL_RECOVERY_NO_LOG, decide whether this database actually needs point-in-time recovery. If yes, add a log backup job, every 15 to 60 minutes is typical:

BACKUP LOG [YourDatabase]
TO DISK = N'D:\SQL-Backups\YourDatabase_LOG.trn'
WITH COMPRESSION, CHECKSUM;

If point-in-time recovery genuinely isn’t required, switch the database to SIMPLE recovery instead of leaving it in FULL with no log backup job; that configuration gives you the log growth overhead with none of the recovery benefit.

Best Practices

Run backup coverage as a standing item in routine health checks, not just when something’s already gone wrong
Alert on backup job failures directly, rather than relying on someone noticing a stale coverage report later
Fold new databases into the backup schedule the same day they’re created
Cross-check against actual backup files on disk if NO_FULL_BACKUP looks suspicious; aggressive msdb history cleanup can produce a false positive
Treat a STALE_FULL finding on a database backed up “only yesterday” as real, not a false alarm; a 25-hour threshold exists because daily backups have almost no slack before they start missing recovery point objectives

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

What counts as a stale backup?

By default, this script flags a full backup as stale after 25 hours, and a log backup as stale after 4 hours. Both thresholds are set for a daily full plus hourly log schedule; adjust them in the script if your backup cadence is different.

Does a recent backup guarantee I can recover this database?

No. Coverage confirms a backup exists and is recent, not that the backup chain is unbroken or that the file itself is valid. Pair this script with a backup chain integrity check, and test restores periodically, for the fuller picture.

Why would msdb say a database has no backups when I know one was taken?

Backup history in msdb can be pruned by cleanup jobs or maintenance plans. If NO_FULL_BACKUP looks wrong for a database you’re confident was backed up, check the actual backup files on disk before assuming the backup never happened.

Summary

A backup job existing and a backup job working are two different claims, and the gap between them is usually invisible until a restore is actually needed. This script closes that gap with one query: every user database, one status flag, worst problems first.

Run it as a standing part of routine health checks, not just when something’s already wrong, and treat every non-OK row as a real finding rather than noise. The 25-hour and 4-hour thresholds exist because recovery point objectives erode fast once a schedule starts slipping.

Blog Categories

Comments

Leave a Reply Cancel reply