SQL Server: Find Databases With No Recent Backup

calendar Apr 19, 2026 · 6 min read · sql server backup recovery msdb monitoring database administration sql scripts backup audit sys.databases  ·
Share on: linkedin copy

Finding databases that have not been backed up recently is one of the most important checks a DBA can run. A database that grows unnoticed without a backup is a data loss event waiting to happen. This script queries msdb.dbo.backupset against sys.databases to surface any online user database with no full backup in the last 24 hours, or that has never been backed up at all.

Purpose and Overview

SQL Server does not alert you by default when a database backup is overdue. If a scheduled backup job fails silently or a new database is created without being added to the backup plan, that database will accumulate changes with no recovery point. The only way to detect this proactively is to query the backup history in msdb.

This post provides two scripts:

  1. Find databases with no full backup in the last 24 hours or that have never been backed up
  2. Extended version showing last full, differential, and log backup dates for all user databases

Code Breakdown

Script 1: Find Databases With No Recent Full Backup

 1SELECT
 2    d.name                                          AS database_name,
 3    d.recovery_model_desc,
 4    d.state_desc,
 5    MAX(bs.backup_finish_date)                      AS last_full_backup,
 6    CASE
 7        WHEN MAX(bs.backup_finish_date) IS NULL
 8            THEN 'NEVER BACKED UP'
 9        WHEN MAX(bs.backup_finish_date) < DATEADD(DAY, -1, GETDATE())
10            THEN CAST(DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS VARCHAR) + ' hours ago'
11        ELSE 'OK'
12    END                                             AS backup_status,
13    DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS hours_since_last_backup
14FROM sys.databases AS d
15LEFT JOIN msdb.dbo.backupset AS bs
16    ON  bs.database_name = d.name
17    AND bs.type = 'D'
18WHERE d.database_id > 4          -- exclude master, model, msdb, tempdb
19    AND d.state_desc = 'ONLINE'
20    AND d.is_read_only = 0
21GROUP BY d.name, d.recovery_model_desc, d.state_desc
22HAVING MAX(bs.backup_finish_date) IS NULL
23    OR  MAX(bs.backup_finish_date) < DATEADD(DAY, -1, GETDATE())
24ORDER BY last_full_backup ASC;

The query uses a LEFT JOIN rather than INNER JOIN so that databases with no backup history at all are still returned — they appear with last_full_backup as NULL. The HAVING clause filters to databases that are either never-backed-up (IS NULL) or overdue (older than 24 hours).

database_id > 4 excludes the four system databases (master, tempdb, model, msdb). state_desc = 'ONLINE' skips databases that are in RESTORING, OFFLINE, or SUSPECT states — those cannot be backed up and would create false positives. is_read_only = 0 skips read-only databases that may intentionally have no recent backup because their data does not change.

The threshold of one day (DATEADD(DAY, -1, ...)) is appropriate for most OLTP environments. Adjust the value to match your RTO/RPO policy — for example, 4 hours for a critical database or 7 days for a low-activity reporting database.

Script 2: Full Backup Status Report for All User Databases

 1SELECT
 2    d.name                                              AS database_name,
 3    d.recovery_model_desc,
 4    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full_backup,
 5    MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_differential_backup,
 6    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup,
 7    DATEDIFF(HOUR,
 8        MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END),
 9        GETDATE())                                      AS full_backup_hours_ago,
10    CASE
11        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) IS NULL
12            THEN 'NO BACKUP'
13        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END)
14                < DATEADD(DAY, -1, GETDATE())
15            THEN 'OVERDUE'
16        ELSE 'OK'
17    END                                                 AS full_backup_status
18FROM sys.databases AS d
19LEFT JOIN msdb.dbo.backupset AS bs
20    ON  bs.database_name = d.name
21    AND bs.type IN ('D', 'I', 'L')
22WHERE d.database_id > 4
23    AND d.state_desc = 'ONLINE'
24    AND d.is_read_only = 0
25GROUP BY d.name, d.recovery_model_desc
26ORDER BY full_backup_status DESC, last_full_backup ASC;

This extended version adds differential and log backup columns alongside the full backup date. It is useful for checking log backup frequency on databases in FULL recovery model — a database in FULL recovery without regular log backups will have a growing transaction log that cannot be shrunk until a log backup occurs.

The CASE WHEN bs.type = 'D' pattern inside MAX() is a conditional aggregation technique that pivots multiple backup types into columns without requiring a subquery or multiple joins to the same table.

Key Benefits and Use Cases

  • Identifies databases that have never been added to a backup plan
  • Detects silently failing scheduled backup jobs before a restore is needed
  • Provides a compliance report showing RPO status for all user databases
  • Reveals FULL recovery databases with no log backup (log will grow unbounded)
  • Can be wrapped in a SQL Server Agent alert job that emails the DBA if any database is overdue

Performance Considerations

  • msdb query performance: msdb.dbo.backupset can grow large on busy instances. If performance is slow, add WITH (NOLOCK) to the backupset table hint for a monitoring-only query where a dirty read is acceptable.
  • Retention window: Adjust the DATEADD threshold to match how long your msdb backup history is retained. If history is purged after 30 days, a database with no backup in 60 days may still appear as NULL in msdb even if it was backed up and the record was purged.
  • Availability Groups: On AG secondary replicas, backups may be offloaded to the secondary. The database_name in msdb.dbo.backupset is always the database name, so the query works across all nodes by querying msdb on each replica if needed.

Practical Tips

To convert this into an automated alert, wrap the first query in a SQL Server Agent job step and add a RAISERROR or sp_send_dbmail call when the result set contains any rows:

 1-- Agent job step: alert if any database is overdue
 2IF EXISTS (
 3    SELECT 1
 4    FROM sys.databases AS d
 5    LEFT JOIN msdb.dbo.backupset AS bs
 6        ON  bs.database_name = d.name
 7        AND bs.type = 'D'
 8    WHERE d.database_id > 4
 9        AND d.state_desc = 'ONLINE'
10        AND d.is_read_only = 0
11    GROUP BY d.name
12    HAVING MAX(bs.backup_finish_date) IS NULL
13        OR MAX(bs.backup_finish_date) < DATEADD(DAY, -1, GETDATE())
14)
15BEGIN
16    EXEC msdb.dbo.sp_send_dbmail
17        @profile_name = 'DBA Alerts',
18        @recipients   = 'dba@yourcompany.com',
19        @subject      = 'WARNING: SQL Server database backup overdue',
20        @body         = 'One or more user databases have not been backed up in the last 24 hours. Review backup jobs immediately.';
21END

Schedule this job to run every morning before the business day starts so the DBA team is alerted before any data loss window becomes critical.

Conclusion

Proactive backup gap detection is a fundamental DBA responsibility. This script turns a silent risk — a database with no recent backup — into a visible, actionable alert by querying the backup history SQL Server already records. Run it on a schedule, not just when something goes wrong.

References

Posts in this series