Finding Databases That Have Never Been Backed Up

A database that has never been backed up is easy to miss, because the backup job still shows green. The job protects the databases it selected. It says nothing about the one created last month. Let me show you how to find that database, and why a missing row needs a second look.

A green job and a missing database

Picture a manager asking, “Are all our databases backed up?” You open the job history. Green, every night. You say yes. Then an installer creates a new database, nobody adds it to the job, and the green tick keeps smiling.

The truth lives in msdb, which keeps a row for every backup. Compare it with the list of databases and the gaps show up. Everything below only reads msdb, apart from a demo database that I create and remove. The demo also writes a few history rows into msdb and deletes them at the end. Run it from master.

Create a database nobody backs up

DROP DATABASE IF EXISTS SqlAuthorityDemo;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SqlAuthorityDemo';

CREATE DATABASE SqlAuthorityDemo;

Ask msdb who has never been backed up

Full backups are type D in msdb.dbo.backupset. Take the latest finish date per database, then LEFT JOIN from sys.databases, so databases with no row at all still appear. I leave out tempdb and snapshots. The variable at the top narrows the output to the demo. Set it to NULL to scan your whole server.

DECLARE @OnlyDatabase sysname = N'SqlAuthorityDemo';

WITH FullBackups AS (
    SELECT database_name, MAX(backup_finish_date) AS last_full
    FROM msdb.dbo.backupset
    WHERE type = 'D'
    GROUP BY database_name)
SELECT d.name, d.create_date, d.state_desc, b.last_full,
       CASE WHEN b.last_full IS NULL THEN 'No retained full-backup record'
            ELSE 'Last full backup predates database creation' END AS finding
FROM sys.databases AS d
LEFT JOIN FullBackups AS b ON b.database_name = d.name
WHERE d.name <> N'tempdb' AND d.source_database_id IS NULL
  AND (b.last_full IS NULL OR b.last_full < d.create_date)
  AND (@OnlyDatabase IS NULL OR d.name = @OnlyDatabase)
ORDER BY d.name;

One row comes back: SqlAuthorityDemo, ONLINE, last_full NULL, and the finding says no retained full-backup record. That is your list of databases to chase.

Backups older than the database

Now the sneaky case. I take a backup, then drop the database and create a new one with the same name. The old history stays in msdb. By name, the database looks protected. By date, it is not.

The backup goes to NUL, so no file is created. That is only for this demo, because a backup sent to NUL cannot be restored. Never do it on a real database. I use COPY_ONLY so it stays out of any differential chain.

BACKUP DATABASE SqlAuthorityDemo TO DISK = 'NUL' WITH COPY_ONLY;

SELECT database_name, type, is_copy_only, backup_finish_date
FROM msdb.dbo.backupset
WHERE database_name = N'SqlAuthorityDemo';

The row shows type D and is_copy_only 1. So a copy-only backup counts as a full backup in this report. Decide for yourself whether that is acceptable. Now recreate the database and run the findings query again. A one second pause first keeps the two dates from tying.

WAITFOR DELAY '00:00:01';
DROP DATABASE SqlAuthorityDemo;
CREATE DATABASE SqlAuthorityDemo;

DECLARE @OnlyDatabase sysname = N'SqlAuthorityDemo';

WITH FullBackups AS (
    SELECT database_name, MAX(backup_finish_date) AS last_full
    FROM msdb.dbo.backupset
    WHERE type = 'D'
    GROUP BY database_name)
SELECT d.name, b.last_full, d.create_date,
       CASE WHEN b.last_full IS NULL THEN 'No retained full-backup record'
            ELSE 'Last full backup predates database creation' END AS finding
FROM sys.databases AS d
LEFT JOIN FullBackups AS b ON b.database_name = d.name
WHERE d.name = @OnlyDatabase
  AND (b.last_full IS NULL OR b.last_full < d.create_date);

Now the finding says the last full backup predates database creation. The backup exists, but it belongs to the database that used to have this name.

Check identity with the database GUID

Dates are a good hint. The GUID is stronger evidence. Each backup row carries the GUID of the database it came from. Compare it with the GUID of the database running today.

SELECT d.name, b.backup_finish_date,
       CASE WHEN b.database_guid = r.database_guid THEN 'same database' ELSE 'different database' END AS identity_check
FROM sys.databases AS d
JOIN sys.database_recovery_status AS r ON r.database_id = d.database_id
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name AND b.type = 'D'
WHERE d.name = N'SqlAuthorityDemo'
ORDER BY b.backup_finish_date DESC;

It says different database. Good. That settles the argument about the name.

History can fool you in both directions

Missing history does not prove there was never a backup. Someone may have purged msdb, or a backup tool may not write history the way you expect. Here is a purge, which is all it takes to make a protected database look unprotected.

SELECT COUNT(*) AS history_rows FROM msdb.dbo.backupset WHERE database_name = N'SqlAuthorityDemo';

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SqlAuthorityDemo';

SELECT COUNT(*) AS history_rows FROM msdb.dbo.backupset WHERE database_name = N'SqlAuthorityDemo';

One row, then zero. So treat every row from the findings query as a lead. Check the backup files, your backup tool and your retention settings before you tell anyone the database was never protected. And remember the opposite: a history row is not a promise that you can restore. Test a restore.

Then fix the real gap. Add the database to the backup policy, check the output, and repeat this inventory after every installer or upgrade. The last block removes the demo database and its history.

DROP DATABASE IF EXISTS SqlAuthorityDemo;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SqlAuthorityDemo';

Run the findings query on your server this week, and see who is missing from the list.

Missing history is not proof of no backups, it is a lead to investigate.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.