The first time I watched a backup strategy fail, the backups had been succeeding for eight months. Green checkmarks every night, alerts on failure, a compliance binder with graphs. What nobody had done in eight months was restore one. When the day came, the log chain had a hole in it from a recovery-model flip nobody remembered, and the honest RPO of that system was not the fifteen minutes printed in the binder. It was thirty-six hours: the age of the last restorable chain. Backup is a promise. Restore is the only audit of that promise.
What follows is the discipline I run on SQL Server 2019 and 2022, on-prem and on Azure SQL Managed Instance; Azure SQL Database manages backups for you, which removes the chore and most of the control. Chain design as an RPO/RTO contract, the three things that silently break chains, what CHECKSUM and VERIFYONLY do and do not prove, and the weekly restore drill that makes the promise real.
How do FULL, DIFF, and LOG backups form an RPO/RTO contract?
The three backup types compose into two numbers you owe the business. RPO, how much data you can afford to lose, is set by log backup frequency alone: log backups every fifteen minutes means a worst-case loss of fifteen minutes of transactions, full stop. RTO, how long recovery takes, is set by chain length: restore the full backup your differential is based on — the latest scheduled conventional full, never a copy-only one, since a copy-only full cannot be a differential base — then the latest differential built on it, then every log backup since that differential, in order, ending WITH RECOVERY. The arithmetic that matters at 3 AM is how many files stand between you and that last RECOVERY.
That arithmetic argues for weekly full, nightly differential, log every fifteen minutes. Worst case is one full, one diff, and at most a day of logs, ninety-six files, which RESTORE handles fine from a script and terribly from SSMS clicking. Stretching the full to monthly to save storage multiplies the log count in every restore, and RTO is where storage savings go to die. Then write the numbers down and get the business to sign them: fifteen minutes of loss, ninety minutes of restore, drill-verified. A backup strategy that is not a signed contract is a hope with a schedule.
What silently breaks the log chain?
Three things, and only one of them is obvious. The obvious one is the recovery-model flip: switching from FULL to SIMPLE ends the chain, because SIMPLE truncates the log on checkpoint, and switching back to FULL does not resume log backups until a new full or differential establishes a base. The flip takes thirty seconds, leaves a permanent hole in point-in-time coverage, and the only evidence is in msdb and the error log.
The sneaky one is the ad-hoc full backup. A developer, or worse a second backup tool, takes a full backup to a random disk. It does not break the log chain, log backups do not care about extra fulls, but it silently becomes the differential base: your next DIFF contains only changes since that ad-hoc full, and restoring now requires producing that file, which lives on a laptop in another city. COPY_ONLY exists precisely for this: a full backup WITH COPY_ONLY does not reset the differential base. Any full taken outside the schedule without COPY_ONLY is a booby trap for your next restore. The third breaker is a lost file, one trn deleted by a retention script or a storage hiccup, because a chain is exactly as strong as its weakest link.
One adjacent trap worth naming: the log file itself. A log backup chain that restores flawlessly can still restore slowly if the log is shredded into tens of thousands of virtual log files, because recovery processes VLFs one by one. The growth-pattern discipline behind that is in my notes on transaction log and VLF management, and it belongs in the same review as the chain design.
What do CHECKSUM and RESTORE VERIFYONLY actually prove?
Less than their reputations. BACKUP WITH CHECKSUM makes the engine verify page checksums wherever a page actually carries one — pages written before checksum protection existed can hold corruption that sails straight through — and write a checksum over the backup stream itself, so media rot in the file is caught at backup time rather than at 3 AM; it costs a few percent of CPU and there is no serious argument against it, but it is a media check, not a logical-corruption check, and it has never replaced DBCC CHECKDB. RESTORE VERIFYONLY reads the file, checks the header, and verifies the checksums where they exist. That is all. It does not allocate pages, does not replay the log, and does not prove the backup can be restored into a database. Microsoft documents it as a readability check, full stop.
BACKUP DATABASE Sales TO DISK = 'D:\SQLBackups\Sales_full.bak'
WITH CHECKSUM, COMPRESSION, INIT;
RESTORE VERIFYONLY FROM DISK = 'D:\SQLBackups\Sales_full.bak'
WITH CHECKSUM;I have watched VERIFYONLY pass on a backup that then failed a real restore, because nothing short of a restore allocates and replays everything. VERIFYONLY is the smoke test you run because it is cheap, and it earns its keep catching truncated files and bad media early. But the sentence "we verify our backups" means nothing unless the verb is restore. If your verification never ends with a database you can run DBCC CHECKDB against, you have proven that a file exists.
How do I monitor the chain with msdb.dbo.backupset?
Every backup lands a row in msdb.dbo.backupset: database name, type (D for full, I for differential, L for log), finish time, size, and the LSN columns that let you prove continuity. The monitoring query that belongs in every alerting stack is just "how stale is each backup type, per database," and it would have caught the eight-month hole from my opening story in eight seconds:
SELECT d.name AS database_name,
d.recovery_model_desc,
MAX(CASE WHEN b.type = 'D' AND b.is_copy_only = 0 THEN b.backup_finish_date END) AS last_full,
MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
ON b.database_name = d.name
WHERE d.database_id > 4
AND d.state_desc = 'ONLINE'
GROUP BY d.name, d.recovery_model_desc
ORDER BY last_log;Note the last_full line excludes copy-only backups on purpose: an ad-hoc copy-only full cannot serve as the next differential base, so it must not make the scheduled conventional full look fresh. Alert on last_log older than your RPO for databases in FULL recovery, on last_full older than your schedule, and on any database in FULL recovery with no log backups ever, the classic "someone created a database and the backup job never picked it up." The recovery-model flip surfaces here too, as a database in SIMPLE that the wiki page insists has point-in-time coverage. Five minutes in msdb beats a month of assumptions.
What does a restore sequence look like under pressure?
Calm, if you have rehearsed it, starting with the step everyone forgets: before restoring over a damaged database, take a tail-log backup with NORECOVERY, capturing the log right up to the failure and taking the database offline so nothing else commits. Then the chain: full WITH NORECOVERY, latest differential WITH NORECOVERY, every log in LSN order WITH NORECOVERY, and for point-in-time, STOPAT on the final log to halt recovery just before the bad transaction, then RECOVERY. Each NORECOVERY leaves the database in the restoring state, which is correct until the very last step; the classic panic move is recovering early and invalidating the rest of the chain.
-- 1. capture the tail while the damaged database is still reachable
BACKUP LOG Sales TO DISK = 'D:\SQLBackups\Sales_tail.trn' WITH NORECOVERY;
-- 2. walk the chain, NORECOVERY until the final step
RESTORE DATABASE Sales FROM DISK = 'D:\SQLBackups\Sales_full.bak'
WITH NORECOVERY, REPLACE;
RESTORE DATABASE Sales FROM DISK = 'D:\SQLBackups\Sales_diff.bak'
WITH NORECOVERY;
RESTORE LOG Sales FROM DISK = 'D:\SQLBackups\Sales_log_0815.trn' WITH NORECOVERY;
RESTORE LOG Sales FROM DISK = 'D:\SQLBackups\Sales_log_0830.trn' WITH NORECOVERY;
-- 3. the tail holds the 08:31 point-in-time target; it is the final log applied
RESTORE LOG Sales FROM DISK = 'D:\SQLBackups\Sales_tail.trn'
WITH NORECOVERY, STOPAT = '2026-07-24T08:31:00';
RESTORE DATABASE Sales WITH RECOVERY;Under pressure the enemy is file hunting, which log follows which diff, so script chain discovery from msdb ahead of time and keep the script beside the backups, not in a wiki. And if you run an availability group, remember the log chain lives on the primary and keeps growing while a secondary falls behind; the send-and-redo side of that is covered in AG lag monitoring.
What does a weekly restore drill look like?
Automated, small, and relentless. A job picks the latest full, diff, and logs for one database, restores them to a scratch instance, runs DBCC CHECKDB on the restored copy, runs one application sanity query, drops the database, and logs the timings to a table. Rotate the database each week so everything gets drilled on a cycle. The drill proves three things at once: the files are restorable, the chain is complete, and the RTO figure on the contract is honest, because it is measured rather than estimated.
The first run of the drill is always humbling. Missing permissions on the backup share. A chain gap from someone's COPY_ONLY-less full. A restore that takes four hours against a ninety-minute contract. Each one is a disaster found on a Tuesday afternoon instead of during an incident, which is the entire point of the exercise. A backup you have never restored is not a backup. It is a file.
Watching backup health with MonPG when SQL Server support ships
The telemetry that matters is contract-shaped: per-database time since last full, diff, and log against their targets; restore-drill durations against the RTO number; chain gaps flagged straight from backupset history. Backup failures rarely page you; they accumulate quietly until the day you need them. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and backup-contract telemetry is on the list. Status lives on the SQL Server monitoring (coming soon) page. Until it ships, msdb.dbo.backupset and a weekly drill are the whole safety net, and they are enough, provided you actually run the drill.