$ guides / microsoft-sql-server / microsoft-sql-server-restore-readiness ▌

Operations Guides

SQL Server restore readiness: why a backup you never test is not a backup

The most common SQL Server backup monitoring answers one question: did the job succeed? A green checkmark means SQL Server wrote bytes to a file. It does not mean those bytes can be read back, decrypted, applied through a recovery sequence, and brought online within your RTO.

The gap between “backup succeeded” and “we can recover” is where most DR failures actually live. Backups fail to restore because of media corruption the backup job never validated, because a TDE or backup-encryption certificate was not backed up alongside the data, because an ad-hoc operation broke the log chain and silently invalidated hours of log backups, or because the restore takes four times longer than anyone measured. None of those surface in backup-completion monitoring.

This article covers how to validate backups beyond the job’s success flag, how to schedule and measure real restore tests, what cryptographic material you must protect alongside the data, and how to keep log chains intact when you pull ad-hoc copies for testing.

What restore readiness actually means

Restore readiness is the verified ability to recover a database to a defined point in time, on a target instance, within a measured duration. It has four components.

Backup freshness. A recoverable backup exists and is recent enough to meet RPO.
Backup integrity. The backup media is readable and the data inside it is internally consistent.
Restore feasibility. Every prerequisite the restore needs is in place: certificates, private keys, reachable storage, a compatible target instance, and an unbroken log chain.
Measured RTO. Someone has actually run the restore end-to-end and timed it.

Backup-completion monitoring covers only the first. It is necessary and not sufficient.

Why backup success is not recoverability

flowchart TD
    A[Backup job success] --> B[VERIFYONLY passes]
    B --> C[Restore test passes]
    C --> D[CHECKDB clean on restored copy]
    D --> E[Restore duration measured]
    A -.- A1[Only proves bytes were written]
    B -.- B1[Misses page corruption without CHECKSUM]
    C -.- C1[Catches missing cert and broken chain]
    D -.- D1[Only true corruption validation]
    E -.- E1[Only real RTO number]

The first trap is RESTORE VERIFYONLY. It is widely used as the post-backup integrity check, and Ola Hallengren’s maintenance solution wires it in via @Verify = 'Y'. Per Microsoft’s documentation, VERIFYONLY checks that the backup set is complete and the backup is readable, but it does not attempt to verify the structure of the data contained in the backup volumes. In practice it catches media-level and header problems but not page-level corruption unless BACKUP ... WITH CHECKSUM was used at backup time. A backup that passes VERIFYONLY can still fail to restore, or can restore a corrupt database.

The second trap is msdb.dbo.backupset. A job can mark itself successful without a row ever being written to backupset, for example when the job script swallows errors or when the target path is unreachable after the file handle closes. Always check the authoritative record, not the job history table.

The third trap is cryptographic material. TDE-encrypted databases and backup-encrypted backups require the server certificate (and its private key) to be present on the restore target. If the only copy of that certificate lived on the source instance, and the source instance is the thing you are recovering from, the backup is unrecoverable. Certificate loss equals backup loss.

The fourth trap is log chain continuity. Point-in-time recovery requires an unbroken sequence of log backups anchored to a full backup. Switching recovery models from FULL to SIMPLE and back, detaching and reattaching a database, or letting a log shipping target come online with RECOVERY can all break the chain. A new full or differential backup re-anchors it, but the window between the break and the re-anchor is not point-in-time recoverable.

The restore-readiness procedure

1. Track backup freshness from the authoritative source

-- Authoritative backup history per database
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_diff_backup,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup,
    DATEDIFF(HOUR, MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END), GETDATE()) AS hours_since_full
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON d.name = bs.database_name
WHERE d.database_id > 4  -- exclude master, tempdb, model, msdb
  AND d.state_desc = 'ONLINE'
GROUP BY d.name, d.recovery_model_desc
ORDER BY hours_since_full DESC;

This is the source of truth for backup freshness. Job status alone is insufficient: a job can return success while the backupset row is never written because the target path was unreachable after the file handle closed.

2. Validate media with RESTORE VERIFYONLY

-- Validate a backup file's media and header integrity
RESTORE VERIFYONLY
FROM DISK = N'/backups/prod/MyDb_full_20260720.bak'
WITH CHECKSUM;

WITH CHECKSUM only adds value if the backup was taken with BACKUP ... WITH CHECKSUM. Without checksums at backup time, VERIFYONLY falls back to header and media checks. Treat VERIFYONLY success as “media is readable”, not “restore will succeed”.

3. Take ad-hoc backups safely with COPY_ONLY

-- Ad-hoc backup that does NOT break the log chain or reset the differential base
BACKUP DATABASE [MyDb]
TO DISK = N'/restoretests/MyDb_copyonly_20260720.bak'
WITH COPY_ONLY, CHECKSUM, STATS = 10;

Copy-only backups are the correct way to capture a database for restore testing on a separate target without disrupting the production recovery chain. A regular full backup would establish a new differential base LSN and force the next differential cycle to rebuild from scratch.

4. Run periodic end-to-end restore tests

This is the gold standard. On a separate, non-production instance, restore the most recent full backup, the latest differential, and the log sequence to a chosen point in time. Time the entire operation. The measured duration is your empirical RTO for that database, and it is almost always longer than the number in the DR plan.

-- Destructive on the target instance only. Restores under a different name.
RESTORE DATABASE [MyDb_RestoreTest]
FROM DISK = N'/restoretests/MyDb_full.bak'
WITH MOVE N'MyDb' TO N'/data/MyDb_RestoreTest.mdf',
     MOVE N'MyDb_log' TO N'/log/MyDb_RestoreTest.ldf',
     NORECOVERY, REPLACE, STATS = 10;

RESTORE DATABASE [MyDb_RestoreTest]
FROM DISK = N'/restoretests/MyDb_diff.bak'
WITH NORECOVERY, STATS = 10;

RESTORE LOG [MyDb_RestoreTest]
FROM DISK = N'/restoretests/MyDb_log.trn'
WITH RECOVERY, STOPAT = '2026-07-20T12:00:00', STATS = 10;

The REPLACE and MOVE clauses let you land the restore under a different name on different paths. NORECOVERY between full and differential keeps the restore sequence open until the final RECOVERY.

Cadence depends on tier. Tier-1 systems typically need monthly restore tests; lower tiers may test quarterly or annually. Whatever you choose, record the date and the duration per database. A backup that has never been restored is not a recovery asset, it is a hypothesis.

5. Verify DBCC CHECKDB against the restored copy

-- Run integrity check against the restored database
DBCC CHECKDB ('MyDb_RestoreTest') WITH NO_INFOMSGS, ALL_ERRORMSGS;

CHECKDB against the restored copy is the only way to detect corruption that lived undetected in the source. VERIFYONLY cannot find it. A clean CHECKDB on the restored database is the strongest evidence the backup is usable.

6. Back up and track certificates and keys

-- Back up the TDE / backup-encryption certificate with its private key
BACKUP CERTIFICATE MyTdeCert
TO FILE = N'/securekeys/MyTdeCert.cer'
WITH PRIVATE KEY (
    FILE = N'/securekeys/MyTdeCert.pvk',
    ENCRYPTION BY PASSWORD = '<strong-password>'
);

Store certificate and key backups in a separate location from the database backups, with their own access controls. If the database backup and the certificate that decrypts it sit on the same array, a single failure takes both.

-- Certificate expiry (certificates expiring within 90 days)
SELECT name, subject, expiry_date,
       DATEDIFF(DAY, GETDATE(), expiry_date) AS days_until_expiry
FROM sys.certificates
WHERE expiry_date < DATEADD(MONTH, 3, GETDATE())
ORDER BY expiry_date;

-- TDE encryption state per database
SELECT d.name, dek.encryption_state,
    CASE dek.encryption_state
        WHEN 1 THEN 'Unencrypted'
        WHEN 2 THEN 'Encryption in progress'
        WHEN 3 THEN 'Encrypted'
        WHEN 4 THEN 'Key change in progress'
        WHEN 5 THEN 'Decryption in progress'
        WHEN 6 THEN 'Protection change in progress'
    END AS state_desc
FROM sys.dm_database_encryption_keys dek
JOIN sys.databases d ON dek.database_id = d.database_id;

After TDE certificate rotation, the old certificate is still required to restore log backups taken while it was active. Dropping the old certificate before every dependent backup has aged out of retention breaks the restore chain silently.

Common pitfalls

Treating backup job success as proof of recoverability. Always corroborate with msdb.dbo.backupset and periodic restore tests.
Skipping WITH CHECKSUM. Without checksums at backup time, VERIFYONLY cannot detect page-level corruption. Make CHECKSUM the default on production backup jobs.
Losing the certificate. TDE and backup encryption depend on a certificate and private key that are not inside the backup file. Lose the cert, lose the backup. Back it up separately and store it off-array.
Dropping rotated certificates too early. Older log backups still in the retention window need the certificate under which they were taken.
Taking regular full backups for restore testing. Use COPY_ONLY to avoid resetting the differential base and forcing a rebuild on the next production differential.
Switching recovery models to SIMPLE and back. This breaks the log chain. A full or differential backup is required to re-anchor it, and during the gap point-in-time recovery is impossible.
Ignoring restore duration. A backup that takes 20 minutes to write but 6 hours to restore does not meet a 2-hour RTO. Measure end-to-end.
Untested restore targets. The test target instance must be the right version, edition, and patch level, with enough storage. Discovering at restore time that the target is one version too old is the worst possible moment to learn that.
Backup files on the same volume as live databases. A volume failure takes both the database and the backup that was supposed to recover it.

Signals to monitor

Signal	Why it matters	Warning sign
Hours since last full backup per database	Direct measure of recovery point exposure	Production database more than 24h without a full; more than 7d is critical
`log_reuse_wait_desc` per database	Tells you why the log cannot truncate; chain breaks surface here	`ACTIVE_TRANSACTION`, `REPLICATION` (when not configured), or `AVAILABILITY_REPLICA` persisting. Note: `LOG_BACKUP` is normal between log backups on FULL recovery databases
Certificate days until expiry	TDE and backup encryption break silently on expiry	Less than 90 days warrants rotation planning
TDE `encryption_state`	Stuck states indicate stalled encryption work	Prolonged state 2, 4, 5, or 6
Restore-test date per database	Untested backups are unproven	More than 30 days for tier-1, more than 90 days for any production database
Measured restore duration per database	Empirical RTO; the only real number	Duration exceeds SLA RTO
`msdb.dbo.backupset` row per expected backup	Authoritative record that a backup actually exists	Expected backup type missing for a database
DBCC CHECKDB result on restored copy	Catches corruption VERIFYONLY cannot	Any error output

How Netdata helps

Backup-freshness tracking from msdb.dbo.backupset surfaces hours-since-last-full and hours-since-last-log per database, so a job that “succeeds” without writing a backupset row is visible.
Per-database transaction-log usage with log_reuse_wait_desc exposes a broken or stalled log chain as a non-LOG_BACKUP, non-NOTHING value long before error 9002.
Certificate expiry tracking puts TDE and backup-encryption certificates on a clock, so rotation happens before the cert blocks a restore.
Database-state monitoring catches databases stuck in RESTORING or RECOVERY_PENDING after a test or a real recovery.
Correlating restore-test windows with I/O stall, CPU, and TempDB metrics on the test target reveals whether measured RTO is dominated by storage, by the recovery pass, or by something else.
Disk-space monitoring on backup and restore-test volumes catches the common failure where a restore cannot complete because the target has no room.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

The Netdata solution

Microsoft SQL Server monitoring with Netdata

Netdata monitors SQL Server with per-second metrics, pre-built dashboards, and ML-powered anomaly detection. Correlate wait statistics, blocking chains, transaction-log and TempDB pressure, Page Life Expectancy, memory grants, per-file I/O stalls, and AlwaysOn send/redo queues against the rest of your stack so you catch the incidents in these runbooks before they page anyone.

See SQL Server monitoring → Start monitoring free