INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

Topic lessonTopic: SQL Server backup and restore

SQL Server backup and restore: recovery models and log continuity

SQL Server restores combine full, differential and log backups with the correct base and a continuous sequence. The database recovery model determines logging and recovery capabilities. Use a fictional document database and a 10:45 target to distinguish having backups from having a viable restore chain.

What you will learn

Match recovery model, differential base, required logs, recovery fork and target without inferring recoverability from timestamps or file existence alone.

Prerequisite

Backups supply restore material. RPO describes acceptable data loss; RTO describes acceptable recovery time. This lesson covers engine concepts without operating a live database.

A recovery model is not a backup type

A recovery model is a database property governing logging and recovery capabilities. A full backup is a type of data backup. Selecting the FULL recovery model and taking a full database backup are different operations.

Match logging to the recovery objective
Recovery modelLog backupsRecovery conditions
SIMPLENot supportedRestore to a data-backup endpoint; log-based point-in-time recovery is unavailable
FULLRequiredA valid data base and continuous logs must cover the target
BULK_LOGGEDRequiredPoint-in-time restore is restricted within a log backup containing minimally logged changes

Understand the roles of full, differential and log backups

Three kinds of restore material
TypeRoleCommon confusion
FullA data base for the database restoreIt is not a base for any arbitrary differential
DifferentialChanges since its differential baseIt is not merely changes since the preceding differential
Transaction logRequired change history applied in sequenceA required interval cannot be skipped

A differential requires its corresponding base. Distinguish a conventional full backup from a copy-only full: a copy-only full does not reset the differential base. Data-backup capture and the log-backup sequence are separate concerns; taking a full backup does not justify ignoring required logs.

Fix the conditions for a 10:45 restore

Use an independent fictional CorpDocs-B0 database, FULL recovery and recovery fork A. A recovery fork identifies a branch in recovery history. Matching timestamps do not permit logs from a different branch. This database is not synchronized with the connection example.

Restore material: F0, its D1, and continuous L2/L3
F0: full / 08:00

Differential base F0; the required log start after this full is stipulated as position 100.

D1: differential / 10:00

Base F0, fork A; required log start after applying it is stipulated as position 150.

L2: [100, 200)

Fork A, ending 10:30; covers positions 100 inclusive to 200 exclusive.

L3: [200, 300)

Fork A, ending 11:00; target 10:45 is independently stipulated as position 250.

Numbers organize the material. Positions are teaching abstractions, not literal SQL Server LSN metadata or values calculated from timestamps.

Distinguish an optional differential from required logs

  1. Start from F0; if using D1, confirm that its differential base is F0.
  2. After D1, position 150 is required. L2 covers 150 through 200, and L3 continues from 200 through 300.
  3. Target 250 lies inside L3. Keep the database ready for subsequent restore steps until the target has been applied.
  4. Complete recovery last. Data consistency and the application’s required reads still require separate verification.

Under the stipulated conditions, F0 → D1 → L2 → the target inside L3 forms a covered plan. D1 is optional: F0 → L2 → L3 is another covered plan because all logs required from F0 are available. Omitting D1 is different from omitting required L3.

Change one condition and explain the failure
Changed conditionConclusion in this fixture
All material present150 →200 →250 is continuous and covers the target
L3 missingL2 stops before200 and cannot cover target 250
D1 has another full baseThe F0-plus-D1 plan fails; distinguish a separate valid F0-and-logs plan
L3 belongs to fork BIt cannot simply continue the fork A sequence
RECOVERY completed before required logsThe same restore sequence cannot simply continue; replan from an appropriate starting point

NORECOVERY keeps the restore sequence open; RECOVERY completes recovery. Understand why completion belongs at the end rather than copying commands. This lesson runs no restore operation and does not establish a successful live recovery.

Distinguish engine recovery from RDS restore mechanisms

RDS snapshots and automated backups are not the same operational interface as the SQL Server full/differential/log sequence here. The FULL recovery model, a full backup and an RDS snapshot are distinct concepts. Check RDS-specific documentation for supported operations, targets and limitations.

Choose an option to see the result and explanation.

Can simple recovery use log backups for point-in-time restore?
Read the answer and reason

Answer: No; log backups are not supported · Simple recovery does not support transaction-log backups.

In this restore example, F0, its differential D1 and logs L2/L3 cover the required log positions continuously. How should a restore to 10:45 be planned?
Read the answer and reason

Answer: F0 → its differential D1 → required chronological logs · The correct base and continuous restore chain are required.

Are SQL Server recovery models and RDS snapshots the same mechanism?
Read the answer and reason

Answer: No; recovery model is a SQL Server concept, snapshot an RDS mechanism · Engine logging and managed-service backup differ.

Check, then explain in your own words

  1. Explain the recovery model separately from backup types.
  2. Fix base, fork, required log start and target.
  3. Distinguish missing L3 from omitted D1 and explain why recovery completes last.
Check your understanding: Does having a log file ending 11:00 establish a restore to 10:45?

No. The data base, fork, continuous required logs, target and restore state also matter.

Timestamps do not substitute for compatibility or continuity. Even target position 250 is separately stipulated in this example.

Sources and conditions checked

Primary references are collected here with their scope, conditions and corresponding sections. Independent expert review and live-environment validation have not been performed.