Proving the Restore, Part 1: The Backup Job Said Success
A maintenance window rebooted a server in the middle of its weekly full backup. The job was killed mid-write, the partial file was discarded, and the next scheduled run was seven days out. Differentials kept running each night. Log backups kept running every fifteen minutes. All of them reported success, correctly.
Eight days later the newest usable full backup was eight days old, and the monitoring had reported green throughout.
Two different questions
Whether the backup job succeeded and whether you can restore the database are separate questions, and most monitoring answers the first one.
A job run reports on one operation at one moment. Restorability is a property of a set of files spanning days, which have to link together in an unbroken sequence. A differential is useless without the full it was based on. A log backup is useless without every log backup before it, back to a full or differential. Any one of those files can be missing while every job that produced the others reports success.
Over the next several posts I’ll work through how to prove a restore chain is intact using what SQL Server already records, and how to generate a restore script from that proof. Every check here is something you can run before you need it.
What the instance already knows
msdb records every backup this instance performed. backupset has one row per backup operation, and type tells you which kind: D for a full database backup, I for a differential, L for a log.[1]
Those are character codes. Reading backup files directly returns the same concept as a smallint with different values, which I’ll come back to in a later part.
Here’s the query I’d run first on an unfamiliar instance:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT [database_name] = [d].[name] , [last_full] = MAX(CASE WHEN [b].[type] = 'D' THEN [b].[backup_finish_date] END) , [last_diff] = MAX(CASE WHEN [b].[type] = 'I' THEN [b].[backup_finish_date] END) , [last_log] = MAX(CASE WHEN [b].[type] = 'L' THEN [b].[backup_finish_date] END) , [recovery_model] = [d].[recovery_model_desc] FROM [sys].[databases] AS [d] LEFT JOIN [msdb].[dbo].[backupset] AS [b] ON [b].[database_name] = [d].[name] AND [b].[is_copy_only] = 0 WHERE [d].[database_id] <> 2 /* tempdb is never backed up */ AND [d].[state] = 0 /* ONLINE only */ GROUP BY [d].[name] , [d].[recovery_model_desc] ORDER BY [last_full]; |
Sorting by last_full ascending puts the problem at the top. On the instance that prompted this series, one database sat eight days above everything else, and it had been sitting there in plain sight the whole time.
Two details in that query are deliberate. is_copy_only = 0 excludes copy-only backups, because a copy-only full does not become the base for later differentials and shouldn’t be counted as your most recent usable full.[2] Filtering to state = 0 keeps offline and restoring databases from cluttering the result, though you may want to see those separately.
The part msdb can’t tell you
msdb.dbo.backupset records that a backup happened. It does not record whether the file still exists.
Retention scripts delete files. Storage migrations move them. Someone reclaims space on a share at quarter end. None of that touches the row in backupset, so an instance may report a healthy backup history for files that were deleted weeks ago.
backupmediafamily holds the path each backup was written to, and joining it in at least tells you where to look:[3]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT TOP (20) [b].[database_name] , [b].[type] , [b].[backup_finish_date] , [size_mb] = CAST([b].[backup_size] / 1048576.0 AS decimal(18, 1)) , [m].[physical_device_name] FROM [msdb].[dbo].[backupset] AS [b] INNER JOIN [msdb].[dbo].[backupmediafamily] AS [m] ON [m].[media_set_id] = [b].[media_set_id] WHERE [b].[database_name] = N'YourDatabase' AND [b].[type] = 'D' ORDER BY [b].[backup_finish_date] DESC; |
That gives you paths to verify. Confirming the files are readable is a separate step, and the subject of the next several posts.
msdb is also per-instance. Restore a database onto a different server and that server’s msdb knows nothing about the backups taken before the move. In an availability group, backups taken on a secondary are recorded in that secondary’s msdb, so no single node holds the whole history. Any check built purely on msdb inherits those blind spots.
Alert on the gap, not on the job
Most backup alerting is wired to job failure. That catches the case where a job runs and fails. It misses the case where a job doesn’t run at all, and it misses the case where the job succeeded but the file is gone.
The check that would have caught the eight-day gap is an age threshold on the newest full, evaluated on a schedule that has nothing to do with the backup job:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DECLARE @max_full_age_hours int = 192; /* 8 days */ SELECT [database_name] = [d].[name] , [last_full] = MAX([b].[backup_finish_date]) , [age_hours] = DATEDIFF(HOUR, MAX([b].[backup_finish_date]), SYSDATETIME()) FROM [sys].[databases] AS [d] LEFT JOIN [msdb].[dbo].[backupset] AS [b] ON [b].[database_name] = [d].[name] AND [b].[type] = 'D' AND [b].[is_copy_only] = 0 WHERE [d].[database_id] <> 2 AND [d].[state] = 0 GROUP BY [d].[name] HAVING MAX([b].[backup_finish_date]) IS NULL OR DATEDIFF(HOUR, MAX([b].[backup_finish_date]), SYSDATETIME()) > @max_full_age_hours; |
Set the threshold above your normal cycle with enough room that one missed run doesn’t page anyone, and low enough that two missed runs do. For a weekly full, eight days is about right. The IS NULL branch matters as much as the age test, because a database that has never been backed up returns no rows from the age comparison.
Run it from a monitoring or operational database rather than as a step inside the backup job, so a job that stops running doesn’t also stop the check that would report it.
Where this goes next
Age checks tell you a file should exist. They say nothing about whether it’s readable, whether it belongs to the database you think it does, or whether the differentials and logs around it form an unbroken chain.
For that you have to open the backup files and read their headers. RESTORE HEADERONLY does that, and it returns a different number of columns depending on which version of SQL Server runs it, which is where the next post starts.
Have you been bitten by a backup that reported success and turned out to be unrestorable? I’d like to hear how you found out. Come tell me on Bluesky or LinkedIn.
References
- backupset – Microsoft Learn. Documents the
typecolumn codes, including D for database, I for differential, and L for log, and theis_copy_onlyflag used above. ↩ - Copy-Only Backups – Microsoft Learn. Explains why a copy-only full does not serve as the differential base, which is the reason those rows are excluded from the “most recent full” calculation. ↩
- backupmediafamily – Microsoft Learn. Holds
physical_device_name, the path each backup was written to, joined tobackupsetthroughmedia_set_id. ↩