Quick Scan Report – Missing Backup Files

What this check looks for

Full and differential backups taken in the last 7 days, from msdb.dbo.backupset joined to msdb.dbo.backupmediafamily, whose physical_device_name does not exist on disk when the check looks for it.

Two kinds of device are excluded because a file path test does not apply to them: names beginning with {, which are the GUID style names third party VSS and snapshot tools use, and names beginning with http, which are backups to a URL such as Azure blob storage. Copy only backups and offline databases are excluded as well.

Why it matters

Your backup history and your file system disagree, and the history is the one everybody reads.

Every report that answers “is this database backed up”, including the ones in this product, reads msdb. So does the restore dialog in SQL Server Management Studio, which will happily build a restore sequence from these rows and then fail partway through when it reaches the file that is not there.

The check’s own description says it first: this may be a false alarm. That is honest, and it is the reason this page spends as much time on ruling it out as on fixing it. Plenty of perfectly sound backup arrangements move, compress or archive the file after SQL Server writes it:

• A script that copies the backup to a network share or to tape and deletes the local copy.
• A compression step that turns .bak into .7z.
• A retention policy that deletes local files after a day or two.

None of those is wrong. What they have in common is that the restore path is no longer the path in the history, so anyone restoring under pressure has to know where the file really went. That knowledge living only in somebody’s head is the actual risk.

The genuinely bad cases look identical from here:

• The backup drive filled and the file was removed to make room.
• Somebody tidied up an unfamiliar folder.
• The archive job deleted the local copy and its own copy failed, so nothing exists anywhere.
• Ransomware. Backup files are a first target precisely because they are the way out.

The differential case is the sharpest. A missing full backup makes every differential taken since it useless, because a differential restores only on top of its base.

How to confirm it yourself

SELECT bs.[database_name],
       CASE bs.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' ELSE bs.[type] END AS [type],
       bs.[backup_finish_date],
       bmf.[physical_device_name]
  FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
 INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
         ON bmf.[media_set_id] = bs.[media_set_id]
 WHERE bs.[backup_start_date] >= DATEADD(DAY, -7, GETDATE())
   AND bs.[is_copy_only] = 0
   AND bs.[type] IN ('D', 'I')
   AND bmf.[physical_device_name] NOT LIKE '{%'
   AND bmf.[physical_device_name] NOT LIKE 'http%'
 ORDER BY bs.[database_name], bs.[backup_finish_date] DESC;

To test one path from inside SQL Server:

DECLARE @exists INT;
EXEC master.dbo.xp_fileexist N'D:\Backups\YourDatabase_full.bak', @exists OUTPUT;
SELECT @exists AS [file_exists];   -- 1 yes, 0 no

And the test that actually matters, which reads the file rather than just looking for it:

RESTORE HEADERONLY FROM DISK = N'D:\Backups\YourDatabase_full.bak';
RESTORE VERIFYONLY FROM DISK = N'D:\Backups\YourDatabase_full.bak' WITH CHECKSUM;

A file that exists but cannot be read is the case a file existence check cannot find, and VERIFYONLY WITH CHECKSUM is what finds it.

How to fix it

First, establish which of the two you have, because the answers are opposite.

If the files were moved deliberately, nothing is broken and the work is to write it down:

• Record where backups actually live and how to get one back, somewhere a person under pressure at 2am will find it.
• Include the retrieval time in your recovery estimate. A backup on tape in an offsite vault is a real backup with a real restore time, and the plan should say what that time is.
• Consider backing up to the final location rather than moving afterwards, which removes the discrepancy entirely.

If the files are genuinely gone, treat it as the outage-in-waiting it is:

1. Take a fresh full backup immediately, to a location you have verified.
2. Work out what remains restorable. A differential whose base is missing is not, and RESTORE HEADERONLY across what you still have tells you where the chain actually starts.
3. Find what deleted them. Retention scripts, the archive job, a full disk, or something worse. If you cannot account for it, treat it as a security question rather than a housekeeping one.
4. Separate the backup storage from the database storage, so filling one does not empty the other. There is a separate check for backups on the same drive as the database.

Either way, restore one. This finding is a prompt to do the thing that resolves both cases at once: pick a backup, restore it somewhere else, and run DBCC CHECKDB on the result. That answers “can we get the data back” in a way no amount of reading msdb can.

How long it takes

About half an hour to determine which case you are in. Acting on the bad case takes as long as a full backup of the affected databases.

Related reports

Related checks

Frequently asked questions

We archive backups to tape and delete the local copy. Is this a false positive? It is the expected result of that arrangement, and the check says so in its own description. Treat it as a prompt to make sure the retrieval path is documented rather than as an error.

Why only full and differential backups? Because those are the ones a restore starts from, and a missing full makes its differentials useless. Log backups are usually far more numerous and far more often archived away.

Our backups go to Azure blob storage. Those are excluded, because their device name begins with http and a file path test does not apply.

Why seven days? It keeps the check on the backups a restore would realistically use. Run the query above without the date filter to audit further back.