SQL Server backup freshness: the recovery point you only discover you lack during an incident

The recovery point you actually have is the recovery point you can restore to, not the one your schedule promises. Backup freshness is the gap between those two, measured as the time since the last successful full, differential, and transaction log backup per database. When that gap is wrong, you find out during restore: either an analyst files a ticket for missing data, or an incident forces point-in-time recovery and the chain breaks.

Job status is not the signal. A SQL Agent job can report success while writing to a bad target, while silently skipping a database that has never had a full backup, or while backing up every database except the one someone created last week and never added to the job list. Job success is necessary but not sufficient. The authoritative signal is msdb.dbo.backupset, which records a row only when SQL Server finished writing a backup that participates in the restore chain.

What backup freshness actually measures

Freshness is the time elapsed since the most recent successful backup of each type per database, evaluated against what that database needs to be restorable.

• Full backup (backupset.type = 'D') is the restore base. Without a recent full, there is no restore point at all.
• Differential backup (backupset.type = 'I') is an optional forward increment on top of the last full. Stale differentials lengthen the restore path but do not break it.
• Log backup (backupset.type = 'L') is required for point-in-time recovery under FULL or BULK_LOGGED recovery. Without recent log backups, the database loses point-in-time recoverability and its transaction log grows until it fills the volume and triggers Error 9002.

Recovery point exposure is the metric. A 24-hour-old full backup on an OLTP database is a 24-hour worst-case data loss window. A FULL-recovery database whose last log backup is six hours ago is a six-hour exposure window and a log that has been growing for six hours. Both are invisible until you need them.

Why job status is not the signal

Job history tells you only that the job steps completed. It does not tell you that a restorable backup exists for every database. A job can succeed in five distinct ways that leave a database unprotected, and each produces a green job and either an empty or stale backupset row.

flowchart TD
    J[SQL Agent job reports SUCCESS] --> S1[DB skipped: no full backup exists]
    J --> S2[New DB not in job list]
    J --> S3[Target unreachable or full]
    J --> S4[Log chain broken post-restore]
    J --> S5[Copy-only mistaken for chain backup]
    S1 --> X[No usable row in msdb.dbo.backupset]
    S2 --> X
    S3 --> X
    S4 --> X
    S5 --> X
    X --> E[Recovery point exposed, found during restore]

The only signal that proves a restorable backup exists is msdb.dbo.backupset. A row is written only when SQL Server finished writing the backup and the backup is usable for restore. If backupset has no recent row for a database, that database has no recent recovery point, regardless of what the job history says.

The authoritative query

The query joins sys.databases to msdb.dbo.backupset so every online user database is evaluated, including databases the backup job was never told about. The LEFT JOIN ensures a database with no backup history still appears in the result set, which is the exact condition you are trying to detect.

-- Last successful full, differential, and log backup per database
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN bs.backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN bs.type = 'I' AND bs.is_copy_only = 0 THEN bs.backup_finish_date END) AS last_diff_backup,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup,
    DATEDIFF(HOUR, MAX(CASE WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN bs.backup_finish_date END), GETDATE()) AS hours_since_full
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON d.name = bs.database_name
WHERE d.database_id > 4  -- exclude master, tempdb, model, msdb
  AND d.state_desc = 'ONLINE'
GROUP BY d.name, d.recovery_model_desc
ORDER BY hours_since_full DESC;

Three operational notes.

Use uppercase type codes. The values 'D', 'I', and 'L' are stored uppercase. On a server with a case-sensitive collation, a lowercase 'd' will not match. This is a common source of false-negative freshness reports after a query is copied from a case-insensitive environment.

Filter copy-only out for restore-readiness. Copy-only full backups (is_copy_only = 1) do not break the log chain, but they also do not participate in it. A query that counts a copy-only full as the last full reports a recent recovery point that cannot serve as the base for differential or log restores. The query above filters is_copy_only = 0 on the full and differential columns. If you want “any backup at all” for diagnostics, drop that predicate.

backup_finish_date is the right timestamp. backupset only records a row when the backup completes, so the table enforces this for you. Be aware of it if you join to backupfile, backupmediaset, or backupmediafamily, which inherit the same row’s completion semantics.

SQL Server 2022 added last_valid_restore_time and compression_algorithm columns to backupset; these are additive and only need explicit reference when you want to report compression algorithm or a restore-validity timestamp.

The recovery model contract

The recovery model decides what kinds of backups are meaningful and what happens to the log. The contract is strict and routinely misunderstood.

• SIMPLE truncates the log at checkpoint. No log backups are taken or needed. The database cannot be restored to a point in time between full backups. Acceptable for read-only, staging, or easily-repopulated databases.
• FULL and BULK_LOGGED do not truncate the log until a log backup runs. Point-in-time recovery is possible only if log backups run on a cadence that matches your RPO, typically every 15 to 60 minutes.

The most common silent backup gap is a database in FULL recovery with nightly full backups and no log backups at all. The full backup job succeeds. The job history is clean. But the database has no more point-in-time recoverability than it would in SIMPLE, and its transaction log grows without bound. This is the database that pages you at 3 a.m. with Error 9002 when the log volume fills, and that loses every transaction since last night’s full when you finally do restore. It is invisible to any monitoring that only checks full backup status.

The tie to Error 9002 is direct. A FULL-recovery database with no log backups shows log_reuse_wait_desc = 'LOG_BACKUP' in sys.databases, percent log used climbs, and the volume fills. Adding log space is not the fix. Taking log backups is the fix.

Silent failure modes that defeat job status

Each of these produces a green job and an exposed database.

Database skipped because no full backup exists. Log backup jobs cannot produce a valid log backup for a database that has never had a full backup, so they skip it. The job reports success. msdb.dbo.backupset has no row for that database.

New database not in the job’s database list. A backup job configured to protect a specific list of databases does not automatically protect databases created after the job was set up. The job continues to report success against the original list. Only a query that joins to sys.databases for every online database catches this.

Backup target unreachable or full. The job step may complete with a success code if the target error is swallowed by retry logic or if the job is structured to log and continue on per-database errors. The backup did not land anywhere restorable. backupset has no row.

Log chain broken after restore or detach/attach. A test restore or detach/attach breaks the log chain. Until a new full or differential is taken, log backups are not valid for point-in-time restore to any time after the break. The log backup job keeps succeeding, but the chain is broken and the log backups since the break are not usable for recovery to the current state.

Copy-only backups mistaken for chain backups. Copy-only backups (is_copy_only = 1) are safe for ad-hoc copies and do not break the chain, but they do not participate in it. A freshness check that counts a copy-only full as the last full reports a recent recovery point that cannot serve as the restore base for differentials or logs. This is a monitoring bug, not a backup bug, but it produces the same outcome: a green dashboard and an unprotected database.

What backupset does not prove

A row in msdb.dbo.backupset proves a backup was written. It does not prove the backup can be restored. RESTORE VERIFYONLY validates backup media but does not guarantee a successful restore against a real database. Actual restore tests, on a schedule, to a non-production instance, are the only way to know the recovery point is real. This is the gold standard and is rarely done. Treat backup freshness as a necessary condition for recoverability, not a sufficient one.

Severity thresholds

The thresholds below reflect recovery point exposure, not job completion. Tune them to your RPO and your data criticality.

These are starting points. The right threshold for full backup frequency depends on your RPO. A database that can tolerate 24 hours of data loss may be fine with a daily full. A database with a 15-minute RPO needs log backups every 15 minutes and a freshness threshold tighter than the generic 1-hour TICKET line. Set the threshold from the RPO, not from the job schedule.

Signals to watch in production

msdb history bloat

msdb.dbo.backupset has no default retention period. Rows accumulate forever unless you purge them explicitly. On a busy instance with frequent log backups, the table can grow to millions of rows, slowing the freshness queries you depend on and degrading backup and restore operations. Use sp_delete_backuphistory with a retention cutoff on a regular schedule. A common community heuristic is to retain 60 to 90 days; the right retention depends on your audit, compliance, and forensic requirements. Do not set retention so aggressive that you lose the ability to investigate a backup gap discovered after the fact.

How Netdata helps

Netdata brings backup freshness into the same surface as the signals that predict its failure, so the gap between “job succeeded” and “recovery point exists” becomes visible before an incident forces the question.

• Per-database freshness from msdb.dbo.backupset: full, differential, and log backup age tracked per database, so the moment a database falls outside its RPO window it appears without waiting for a job to fail.
• Correlation with log_reuse_wait_desc: a FULL-recovery database showing LOG_BACKUP as the reuse wait alongside rising log usage is a database whose log backups have stopped. The two together turn a future Error 9002 into a now-visible leading indicator.
• Correlation with transaction log used percent: rising log percent used alongside stale log backup timestamps is the composite signature of the most common silent backup gap, the FULL-recovery database with no log backups.
• Error 9002 capture from the error log: surfaces the active write failure, not just the precondition that predicted it.
• Database state and recovery model context: a new database in FULL recovery with no backup coverage is visible the moment it appears, regardless of what the backup job was configured to protect.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• SQL Server blocking chains: finding the head blocker before workers run out
• SQL Server buffer cache hit ratio low: when the working set no longer fits in memory
• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server Error 1205: transaction was deadlocked and chosen as the deadlock victim
• SQL Server Error 701: there is insufficient system memory to run this query
• SQL Server Error 823 and 824: I/O and logical consistency errors
• SQL Server Error 825: read-retry succeeded and the disk is failing
• SQL Server Error 9002: the transaction log for the database is full
• SQL Server high compilations per second: plan cache pollution and CPU burn
• SQL Server high recompilations: stale statistics and schema changes churning plans