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.
Match recovery model, differential base, required logs, recovery fork and target without inferring recoverability from timestamps or file existence alone.
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.
| Recovery model | Log backups | Recovery conditions |
|---|---|---|
| SIMPLE | Not supported | Restore to a data-backup endpoint; log-based point-in-time recovery is unavailable |
| FULL | Required | A valid data base and continuous logs must cover the target |
| BULK_LOGGED | Required | Point-in-time restore is restricted within a log backup containing minimally logged changes |
Understand the roles of full, differential and log backups
| Type | Role | Common confusion |
|---|---|---|
| Full | A data base for the database restore | It is not a base for any arbitrary differential |
| Differential | Changes since its differential base | It is not merely changes since the preceding differential |
| Transaction log | Required change history applied in sequence | A 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.
Differential base F0; the required log start after this full is stipulated as position 100.
Base F0, fork A; required log start after applying it is stipulated as position 150.
Fork A, ending 10:30; covers positions 100 inclusive to 200 exclusive.
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
- Start from F0; if using D1, confirm that its differential base is F0.
- After D1, position 150 is required. L2 covers 150 through 200, and L3 continues from 200 through 300.
- Target 250 lies inside L3. Keep the database ready for subsequent restore steps until the target has been applied.
- 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.
| Changed condition | Conclusion in this fixture |
|---|---|
| All material present | 150 →200 →250 is continuous and covers the target |
| L3 missing | L2 stops before200 and cannot cover target 250 |
| D1 has another full base | The F0-plus-D1 plan fails; distinguish a separate valid F0-and-logs plan |
| L3 belongs to fork B | It cannot simply continue the fork A sequence |
| RECOVERY completed before required logs | The 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.
Check, then explain in your own words
- Explain the recovery model separately from backup types.
- Fix base, fork, required log start and target.
- 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.
- SQL Server recovery modelsChecked:
Scope and conditionsSimple/Full/Bulk-logged and log recovery prerequisites; a model alone is not a recovery guarantee.
Used in this lessonA recovery model is not a backup type Distinguish engine recovery from RDS restore mechanisms
Scope and conditionsData backups, required logs, and final RECOVERY; no live restore.
Used in this lessonA recovery model is not a backup type Understand the roles of full, differential and log backups Distinguish an optional differential from required logs Distinguish engine recovery from RDS restore mechanisms
- Differential backupsChecked:
Scope and conditionsDifferentials depend on their full base; copy-only full does not reset that base.
Used in this lessonUnderstand the roles of full, differential and log backups Distinguish engine recovery from RDS restore mechanisms
- Transaction log backupsChecked:
Scope and conditionsFull/Bulk-logged log backups and log-chain continuity independent of data backups.
Used in this lessonUnderstand the roles of full, differential and log backups
- Introduction to RDS backupsChecked:
Scope and conditionsDB-instance backups/snapshots, retention and recovery conditions.
Used in this lessonDistinguish engine recovery from RDS restore mechanisms