Quick Scan Report – No Recent Backups

What this check looks for

Every database in sys.databases with no row in msdb.dbo.backupset in the last 14 days.

Three databases are excluded because a backup of them means nothing: tempdb, which is recreated at every restart and cannot be backed up at all, model, and DBHealthHistory, which is this product’s own collected history and is rebuildable. Databases that are OFFLINE are excluded too, because a database nobody has brought online is a different finding.

Why it matters

This is the check that decides whether everything else in this report matters.

Nearly every other finding is about performance, or about risk, or about tidiness. This one is about whether the data still exists tomorrow. A database with no backup is one disk failure, one bad deployment, one ransomware event or one DELETE without a WHERE clause away from being gone permanently.

The reason it persists is that it is silent in both directions. A database that is not being backed up produces no error, because nothing is running to fail. And a backup job that covers nine databases out of ten succeeds every night, reports success, and says nothing about the tenth. The usual causes are all of that shape:

• A database added after the backup job was written, where the job names its databases explicitly rather than looping over all of them.
• A job that skips a database it cannot back up, for instance one in a state it does not handle, and carries on to report success.
• A database restored for a project and never added to anything.
• A third party backup tool that has the database excluded, or has had its licence or its agent quietly stop working for that one.

14 days is a deliberately generous window. A database that has not been backed up in two weeks is not “due a backup”, it is not covered.

How to confirm it yourself

SELECT d.[name],
       d.[recovery_model_desc],
       d.[state_desc],
       MAX(bs.[backup_finish_date])                              AS [last_backup],
       DATEDIFF(DAY, MAX(bs.[backup_finish_date]), GETDATE())    AS [days_ago]
  FROM sys.databases AS d WITH (NOLOCK)
  LEFT JOIN msdb.dbo.backupset AS bs WITH (NOLOCK)
         ON bs.[database_name] = d.[name]
        AND bs.[type] = 'D'
 WHERE d.[name] NOT IN ('tempdb')
 GROUP BY d.[name], d.[recovery_model_desc], d.[state_desc]
 ORDER BY CASE WHEN MAX(bs.[backup_finish_date]) IS NULL THEN 0 ELSE 1 END,
          [last_backup];

A null last_backup is the worst row in the result, and it sorts first. It means this database has never been backed up on this instance, not that the backup is old.

Then ask the second question, which is just as important and which this check does not cover: are the databases in full recovery having their log backed up?

SELECT d.[name],
       d.[recovery_model_desc],
       MAX(CASE WHEN bs.[type] = 'D' THEN bs.[backup_finish_date] END) AS [last_full],
       MAX(CASE WHEN bs.[type] = 'L' THEN bs.[backup_finish_date] END) AS [last_log]
  FROM sys.databases AS d WITH (NOLOCK)
  LEFT JOIN msdb.dbo.backupset AS bs WITH (NOLOCK)
         ON bs.[database_name] = d.[name]
 WHERE d.[recovery_model_desc] <> 'SIMPLE'
   AND d.[database_id] > 4
 GROUP BY d.[name], d.[recovery_model_desc]
 ORDER BY [last_log];

A full recovery database with full backups and no log backups is a database whose transaction log grows forever and whose point in time recovery does not exist. That has its own check.

How to fix it

Back it up now, by hand, before doing anything else. The investigation can wait; the exposure should not.

BACKUP DATABASE [YourDatabase]
  TO DISK = N'D:\Backups\YourDatabase_manual.bak'
  WITH CHECKSUM, COMPRESSION, STATS = 5;

Then fix the cause rather than the symptom.

1. Find out what was supposed to be backing it up. Check the Agent jobs, the maintenance plans, and any third party tool.
2. Make the schedule cover databases rather than names. This is the single change that stops it recurring. A job that enumerates sys.databases picks up tomorrow’s database automatically; a job with a list does not. Ola Hallengren’s DatabaseBackup with @Databases = 'USER_DATABASES' is the usual way to express that.
3. Decide the recovery model deliberately. Full recovery without log backups is worse than simple recovery, because it gives you the log growth without the point in time recovery you were paying for it with.
4. Set up an alert. A job that fails should reach a person. See the checks on operators and on jobs without failure notification.
5. Restore one. A backup nobody has restored is a hope rather than a plan. Restore it somewhere else and run DBCC CHECKDB against the copy.

Back up the system databases too. master, msdb and model are usually the ones missing from a hand written job, and rebuilding an instance without them means rebuilding every login, job and operator by hand.

How long it takes

About two hours to cover the gap and change the job to enumerate databases rather than list them. The immediate backup takes as long as the database is large.

Related reports

Related checks

Frequently asked questions

The database is a copy and does not need backing up. Then say so deliberately: put it in simple recovery, and consider whether it belongs on a production instance at all. A database nobody intends to back up still appears here every time somebody runs a scan.

Why 14 days and not one? Because this is a scan of the instance’s state, not a nightly backup monitor. Fourteen days distinguishes “not covered” from “last night’s job has not run yet”.

We use a third party tool and it does not write to msdb. Most do, because SQL Server records the backup regardless of what issued it. A tool using VSS snapshots may not, and that is worth confirming rather than assuming, because it also means every backup report in this product cannot see them.

Is a copy only backup enough? It protects the data and it does not disturb the differential chain, which is why it exists. It is not a substitute for a scheduled backup.