SQL Server Backup Status Report: All Databases

calendar Apr 10, 2026 · 7 min read · sql server database administration backup recovery maintenance scripts msdb sql scripts database management backup monitoring full backup differential backup transaction log backup  ·
Share on: linkedin copy

Check SQL Server Backup Age Across All Databases

This script queries the SQL Server backup history catalog to report the most recent full, differential, and transaction log backup for every database, including how many hours and minutes ago each backup completed.

Purpose and Overview

Database backups are your last line of defense against data loss. Knowing exactly when each database was last backed up — and how old that backup is right now — is a fundamental DBA responsibility. This script, originally written by Tim Ford, joins master.sys.databases with the msdb backup history tables to produce a single report covering all backup types for all databases. It shows the backup finish time, the physical backup file path, and elapsed time in both hours and minutes, making it straightforward to spot any database that has missed a scheduled backup.

Code Breakdown

The complete script is shown below, followed by an explanation of each section.

 1/*
 2Author: Tim Ford
 3Original link: http://sqlmag.com/database-backup-and-recovery/how-old-are-your-backups
 4*/
 5WITH full_backups AS (
 6SELECT ROW_NUMBER() OVER(PARTITION BY BS.database_name,
 7                                      BS.type
 8                             ORDER BY BS.database_name ASC,
 9                                      BS.backup_finish_date DESC
10                        ) AS [Row Number],
11        D.name AS [database_name],
12        BS.backup_set_id,
13        BS.type AS backup_type,
14        BS.backup_finish_date,
15        D.recovery_model_desc
16FROM master.sys.databases AS D
17        LEFT JOIN msdb.dbo.[backupset] AS BS
18                ON D.name = BS.database_name
19)
20SELECT FB.database_name,
21       CASE FB.backup_type
22               WHEN 'D' THEN 'Data (Full)'
23               WHEN 'I' THEN 'Differential'
24               WHEN 'L' THEN 'Transaction Log'
25       END AS backup_type_desc,
26       FB.recovery_model_desc,
27       FB.backup_finish_date,
28       BMF.physical_device_name,
29       DATEDIFF(hour, FB.backup_finish_date, GETDATE()) AS backup_hours_ago,
30       DATEDIFF(minute, FB.backup_finish_date, GETDATE()) AS backup_minutes_ago
31FROM full_backups FB
32 LEFT JOIN msdb.dbo.[backupset] BS ON FB.backup_set_id = BS.backup_set_id
33 LEFT JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
34 WHERE FB.[Row Number] = 1
35ORDER BY FB.database_name, FB.[Row Number], FB.backup_type;

CTE: Ranking Backup History Per Database and Type

The common table expression full_backups uses ROW_NUMBER() with a PARTITION BY on both database_name and type. This assigns rank 1 to the most recent backup of each type (full, differential, log) for each database. Ordering by backup_finish_date DESC inside the window function ensures the newest record gets rank 1.

The LEFT JOIN from sys.databases to backupset is intentional. Databases that have never been backed up still appear in sys.databases but have no matching row in backupset, so a LEFT JOIN causes them to show up in the output with NULL backup dates — which is exactly the alert you need.

Outer SELECT: Readable Output with Age Calculation

The outer query filters on [Row Number] = 1 to keep only the latest backup per type per database. The CASE expression translates the single-character backup type codes (D, I, L) into readable labels. Two DATEDIFF calls compute elapsed time: one in hours and one in minutes, both measured from backup_finish_date to GETDATE().

LEFT JOINs to backupset and backupmediafamily

msdb.dbo.backupmediafamily stores the physical file path of the backup. Joining through backupset to backupmediafamily via media_set_id retrieves the physical_device_name column, which shows the full file path or device name used for the backup. This is useful for confirming that backups are landing in the expected location.

Recovery Model Column

D.recovery_model_desc comes from sys.databases and reflects the current recovery model of the database (FULL, BULK_LOGGED, or SIMPLE). This helps contextualize whether a missing transaction log backup is expected (SIMPLE recovery databases do not use log backups).

Key Benefits and Use Cases

  • Quickly identifies databases that have not been backed up recently or at all
  • Covers all three backup types (full, differential, transaction log) in a single pass
  • Shows the physical backup file path so you can verify backup destination
  • Includes the recovery model to distinguish which databases require log backups
  • Works across all user databases and system databases simultaneously
  • Elapsed time columns make threshold alerting straightforward — for example, flagging any full backup older than 24 hours

Performance Considerations

The msdb backup history tables grow over time. On busy servers or servers with long backup history retention, these tables can become large. Keep the following in mind:

  • History retention: SQL Server Agent maintenance jobs can prune backup history. The stored procedure msdb.dbo.sp_delete_backuphistory removes records older than a specified date. Keeping history to a reasonable window (such as 90 days) keeps msdb manageable without losing useful trending data.
  • Index coverage: msdb.dbo.backupset has indexes on database_name and backup_finish_date. The query is efficient for most environments, but on servers with millions of history rows the CTE scan can take a few seconds. Run during low-activity windows if msdb is very large.
  • sys.dm_database_backups: SQL Server 2022 introduced sys.dm_database_backups, a dynamic management view that surfaces recent backup information from the buffer cache without hitting msdb. For near-real-time monitoring on SQL Server 2022 and later, this DMV is worth exploring as a complement to msdb queries.
  • Azure SQL Managed Instance: On Managed Instance, automated backups are handled by the platform. The msdb tables are still populated and queryable, but the physical paths reflect Azure Blob Storage URLs rather than local file paths.

Practical Tips

Run the script from a central monitoring instance with linked servers or by using a management tool that can fan out queries across multiple SQL Server instances. Results with a NULL backup_finish_date mean the database has no backup history in msdb — treat those as immediate action items.

The following alternative query shows only the most recent full backup per database and adds backup size in GB, which is useful when you also need to verify that backup files are a plausible size:

 1-- Alternative: Shows last backup with size in GB
 2SELECT  DatabaseName = x.database_name,
 3        LastBackupFile = x.physical_device_name,
 4        LastBackupStart = x.backup_start_date,
 5        LastBackupEnd = x.backup_finish_date,
 6        DurationMinutes = DATEDIFF(MINUTE, x.backup_start_date, x.backup_finish_date),
 7        BackupSizeGB = CAST((backup_size/1024/1024/1024) AS DECIMAL(10,2))
 8FROM (  SELECT  bs.database_name,
 9                bs.backup_start_date,
10                bmf.physical_device_name,
11                bs.backup_finish_date,
12                bs.backup_size,
13                Ordinal = ROW_NUMBER() OVER(PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC)
14          FROM  msdb.dbo.backupmediafamily bmf
15                JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
16                JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
17          WHERE bs.[type] = 'D') x
18WHERE x.Ordinal = 1
19ORDER BY DatabaseName;

This version joins backupmediafamily and backupmediaset first, then to backupset, which avoids the CTE approach and is a useful alternative structure when you only need full backups. The backup_size column in backupset stores bytes; dividing by 1024 three times and casting to DECIMAL(10,2) gives a clean GB figure.

Additional tips for daily use:

  • Schedule both queries as SQL Server Agent jobs that write results to a monitoring table, then set up alerts when any row shows backup_hours_ago above your acceptable threshold.
  • Exclude system databases (master, model, msdb, tempdb) by adding WHERE DB.name NOT IN ('master','model','msdb','tempdb') if you only want to review user databases.
  • Cross-check results against your backup tool's own logs (such as SQL Server Agent job history or a third-party backup application) to confirm that msdb history matches what was actually written to disk.

Conclusion

Tim Ford's backup age query is a reliable, low-overhead script that every SQL Server DBA should keep on hand. By joining sys.databases to the msdb backup history tables with a ROW_NUMBER() ranking, it surfaces the most recent backup of each type for every database in a single readable result set. The elapsed time columns make it easy to build threshold-based alerts, and the physical device name column confirms backup destination. Pair it with the size-focused alternative query and a scheduled Agent job to build a straightforward backup monitoring routine that requires no third-party tooling.

References

Posts in this series