SQL Server / Advanced / Lesson 15

Backups, restore chains, and recovery models

Design recovery from a concrete loss scenario. Connect recovery models, full/differential/log backups, restore sequencing, and an isolated recovery drill.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Translate RPO and RTO into a backup design.
Explain a valid restore chain.
Distinguish backup verification from a successful recovery drill.

Start from acceptable loss and downtime

Recovery point objective, RPO, describes acceptable data-loss exposure; recovery time objective, RTO, describes acceptable restoration time. A nightly backup might expose nearly a day's changes even if it restores quickly. A long chain of tiny log backups may protect recent transactions but take too long to restore without an appropriate full/differential strategy. Define objectives with the business before selecting a schedule.

Inventory everything needed beyond data pages: encryption keys or certificates, credentials handled through approved processes, instance settings, application deployment, and external dependencies. A backup file on the same failed storage is not meaningful disaster protection. Retention, access control, immutable or otherwise protected copies, and documented ownership are part of recovery engineering.

Separate a recovery model from a backup type

SIMPLE allows log-space reuse without log backups, subject to active work and other constraints, and does not support transaction-log backup recovery to arbitrary points. FULL supports log backups and point-in-time recovery when the required chain is available. Switching to FULL does not itself create a complete backup strategy; an initial data backup and ongoing log backups must be planned.

BULK_LOGGED supports minimal logging for eligible operations but complicates point-in-time choices: a log backup containing minimally logged work cannot generally be stopped within that backup. Do not switch recovery models merely to shrink a log. Inspect log_reuse_wait_desc to understand why space is retained, then address the actual backup or transaction issue through the approved operational process.

sql Inspect recovery configuration and log reuse

SELECT name,recovery_model_desc,log_reuse_wait_desc
FROM sys.databases WHERE database_id=DB_ID();

A value such as LOG_BACKUP or ACTIVE_TRANSACTION is diagnostic context, not permission to change the database.

Build a valid restore sequence

A full backup provides a restore base; a differential contains changes since its differential base; log backups continue the log chain. A differential is not simply “changes since yesterday's differential.” A copy-only full backup can provide an ad hoc copy without changing the normal differential base. Retain enough metadata to select compatible files and detect gaps.

Restore the chosen full backup with NORECOVERY, optionally the matching differential with NORECOVERY, and required log backups in sequence. Recover only at the intended final stage. For point-in-time recovery, use the correct STOPAT on the relevant restore sequence and validate the chosen time convention. A tail-log backup may protect recent work after a failure when the log remains accessible, but incident conditions determine feasibility.

Inspect media before an isolated drill

RESTORE HEADERONLY reads backup-set metadata and FILELISTONLY describes logical files. RESTORE VERIFYONLY checks that a backup set is complete and readable with supported validation, but it does not fully validate database structure or prove the application can recover. The example paths are fictional lab media; they are documentation, not existing assets or an executed restore.

Real restores require an explicitly isolated target, checked file mappings, enough capacity, required certificates, and a plan that avoids overwriting another database. Do not add WITH REPLACE reflexively when a restore complains about the target. That safeguard is a reason to review identity and file destinations. This course intentionally provides inspection syntax rather than an executable destructive recovery recipe.

sql Inspect a fictional lab backup

-- Fictional backup media for a recovery-plan review.
RESTORE HEADERONLY FROM DISK=N'D:\TrainingBackups\Shop_full.bak';
RESTORE FILELISTONLY FROM DISK=N'D:\TrainingBackups\Shop_full.bak';
RESTORE VERIFYONLY FROM DISK=N'D:\TrainingBackups\Shop_full.bak'
 WITH CHECKSUM;

Results would depend on actual backup headers, files, permissions, and checksums. No successful verification is claimed.

Measure recovery, not backup job success

A drill restores protected copies to an isolated environment, checks integrity, and verifies business facts such as a known order before and after the chosen point. Measure elapsed recovery time including file retrieval, key access, restore, validation, and application readiness. Compare the recovered point and measured duration with RPO and RTO.

Keep evidence of the chain used, checks performed, and missing dependencies. A successful backup job or VERIFYONLY result is useful but insufficient. Periodically rehearse the procedure with the people who would operate during an incident. Recovery is a tested capability with accountable ownership, not merely a folder of files bearing recent timestamps.

Common errors and fixes

The log keeps growing under FULL.

Why it happens: Required log backups or another reuse prerequisite are missing.
How to fix it: Inspect reuse waits and repair the approved backup or transaction process.
Prevent it next time: Monitor log usage and backup age.

A differential cannot be applied.

Why it happens: Its differential base does not match the restored full backup.
How to fix it: Select the correct base using backup metadata.
Prevent it next time: Retain chain and base metadata.

VERIFYONLY passed but recovery failed.

Why it happens: Readability was mistaken for a full recovery test.
How to fix it: Perform an isolated restore with keys, integrity, and application validation.
Prevent it next time: Schedule measured recovery drills.

Check your understanding

1. A full backup is Sunday and differentials run daily. Which base is Wednesday's differential based on?

Show worked solution

Normally the applicable full differential base, not Tuesday's differential. Restore that full and Wednesday's compatible differential, then any required logs in sequence. Inspect metadata rather than relying only on filenames.

2. Nightly backups restore in ten minutes. Does that satisfy a five-minute RPO?