Chapter 16 · Backup, Export, Recovery, Integrity, and Disaster Preparedness
.recover, Recovery Limitations, Restore Drills, and a Backup Policy
Treat recovery as best-effort salvage rather than backup, validate recovered or restored data rigorously, and design a practical backup/restore runbook around explicit recovery objectives.
Learning outcomes
Recovery is the last line of defense, not the first. The best incident is the one resolved by restoring a recent, tested backup. SQLite’s recovery tooling exists for the harder case: the database is damaged and no trustworthy copy contains all needed data.
Explain .recover as best-effort page-level salvage rather than a constraint-preserving restore.
Use current documented .recover workflow on a duplicate of the damaged file.
List the kinds of logical defects recovered content may contain.
Run a restore drill from a known backup and validate identity, schema, integrity, and business counts.
Define RPO and RTO in terms appropriate to an embedded SQLite application.
Create a production backup runbook covering retention, off-host copies, verification, and escalation.
.recover reads around corruption instead of stopping at it
The CLI .dump uses the normal database interface and can stop when corruption prevents ordinary reading. .recover, added to the CLI in SQLite 3.29.0, instead tries to reconstruct content directly from recoverable pages. That difference is why it may salvage data that ordinary queries cannot read.
# Always work from a protected duplicate of the damaged source.cp corrupt-original.sqlite corrupt-working-copy.sqlite# Emit best-effort SQL reconstruction.sqlite3 corrupt-working-copy.sqlite .recover > recovered.sql# Rebuild into a brand-new destination.rm -f recovered.sqlitesqlite3 recovered.sqlite < recovered.sql# Now validate aggressively.sqlite3 recovered.sqlite "PRAGMA integrity_check;"sqlite3 recovered.sqlite "PRAGMA foreign_key_check;"Current .recover also supports options such as --ignore-freelist. Use options only after reading the documentation for the exact CLI version and preserving an untouched source copy.
Recovery can resurrect or distort data
SQLite’s recovery documentation is intentionally blunt: perfect reconstruction is the exception. Previously deleted content may reappear, some content may be missing or altered, and recovered values can have changed types. Constraints are not guaranteed.
| Possible recovery result | Why validation is mandatory |
|---|---|
| Missing rows/pages | Overwritten or unreadable content cannot be reconstructed. |
| Deleted rows reappear | Old payload may remain in freelist/unallocated space and be salvaged. |
| Changed values/types | Damage can alter bytes interpreted as row values. |
| CHECK/UNIQUE violations | Recovered rows are extracted, not necessarily replayed through original invariants. |
| Foreign-key violations | Parent/child pages may survive differently. |
| STRICT type violations | Recovered content can violate rigid type constraints. |
lost_and_found rows | Recovery found records it could not confidently attribute to a normal table/index. |
Treat the rebuilt database as evidence. Reconcile it against backups, logs, external systems, domain rules, and user expectations before it becomes a production source of truth.
Prefer a restore drill when a known-good backup exists
A restore drill proves that the backup can actually become a working application database. The course fixture restores the verified physical backup into a fresh target and runs the same checks an operator would use before cutover.
PRAGMA integrity_check; -- expected: okPRAGMA foreign_key_check; -- expected: no rowsPRAGMA application_id; -- expected application file identifierPRAGMA user_version; -- expected migration levelSELECT count(*) FROM device; -- expected: 3 in this fixtureSELECT count(*) FROM maintenance_note; -- expected: 1200SELECT count(*) FROM device WHERE status='active'; -- expected: 2The local drill produced exactly those results: integrity=ok, zero FK violations, expected application/user versions, 3 devices, 1,200 notes, and 2 active devices. That verifies much more than “the file copied successfully.”
RPO and RTO turn “back up regularly” into an engineering target
| Objective | Question | SQLite example |
|---|---|---|
| RPO — Recovery Point Objective | How much committed data can the business tolerate losing after a disaster? | If RPO is 15 minutes, a once-nightly backup cannot meet it by itself. |
| RTO — Recovery Time Objective | How long can the service remain unavailable while restoring and validating? | If RTO is 10 minutes, a multi-hour SQL dump restore is not your primary recovery path. |
| Retention | How far back must you be able to restore? | Keep multiple generations so silent corruption discovered later does not poison every retained copy. |
| Failure-domain separation | What if the host/disk/site is lost? | Keep at least one copy outside the primary host/device/failure domain. |
Design a backup policy from failure scenarios
Do not begin with “daily backups.” Begin with events you need to survive: accidental DELETE, bad deployment, device loss, filesystem corruption, ransomware, or a host failure. Then map each event to recovery artifacts and retention.
| Failure | Useful protection |
|---|---|
| Accidental row/table deletion discovered quickly | Recent point-in-time generations; application audit/history if required. |
| Bad migration | Pre-release verified backup + tested rollback/forward-fix plan. |
| Disk/host loss | Off-device/off-host backup copy. |
| Silent corruption discovered days later | Multiple retained generations + periodic integrity/restore validation. |
| Large-scale site failure | Copy in an independent failure domain with documented credentials/access path. |
| No good backup remains | Last-resort .recover attempt on preserved damaged copy. |
A production backup runbook
BEFORE[ ] Identify database path, application_id, user_version, SQLite runtime version.[ ] Know journal mode and whether source remains live.[ ] Select cold copy / online backup / VACUUM INTO intentionally.[ ] Create a unique temporary destination; never overwrite the only good backup.CREATE[ ] Run the selected SQLite-aware snapshot process.[ ] Record start/end time, source host/app release, result code, size, hash.VERIFY[ ] Open the backup as a separate database.[ ] PRAGMA integrity_check -> ok.[ ] PRAGMA foreign_key_check -> no unexpected rows.[ ] Verify application_id + user_version.[ ] Run critical row counts/domain invariants.[ ] Promote/rename the verified backup to its final immutable name.RETAIN[ ] Copy according to retention tiers.[ ] Keep at least one copy outside the source host/device/failure domain.[ ] Protect backup credentials and deletion permissions.DRILL[ ] Restore into a clean environment on a schedule.[ ] Measure restore + validation time against RTO.[ ] Confirm newest recoverable point against RPO.[ ] Document gaps and remediate them.Failure injection for the runbook
A useful drill injects operational failures without corrupting valuable data. Examples: make the backup destination unwritable, remove free disk space in a disposable environment, terminate a backup job before publication, supply a wrong expected application_id, or restore an older user_version. The expected behavior is that automation refuses to publish/cut over the unverified artifact.
if backup_process_failed: keep_source_untouched() mark_temp_artifact_failed() alert()elif verification_failed: quarantine_backup() alert()else: atomically_publish_verified_backup() apply_retention_policy()When to escalate beyond self-service recovery
Escalate when the database contains safety/financial/legal records, corruption appears to be ongoing, storage hardware is suspect, encryption/key material is involved, recovery results conflict with other systems of record, or there is no tested backup and destructive experiments would risk the only copy. Preserve evidence before attempting more writes.
Chapter synthesis
Backup and recovery readiness
Answer as an operator, not just as a SQL learner.
- Why is .recover not a substitute for a backup?
- What must be checked before a restored database is promoted?
- How do RPO and RTO affect backup frequency and restore technology?
- Why keep multiple generations rather than only the newest backup?
- Where should at least one backup live relative to the source host?
- What is the first thing to do with a suspected corrupt database before experimenting?
Review the answers
.recover can lose, resurrect, alter, or de-constrain data; it is salvage. Promotion requires structural, FK, identity/version, and business validation. RPO determines tolerated data loss and therefore snapshot frequency; RTO constrains restore/validation time. Multiple generations protect against late-discovered damage. At least one copy should be outside the source failure domain. Preserve an untouched copy/evidence before recovery experiments.
Bridge to schema evolution
Backups are especially important immediately before schema changes. Chapter 17 turns this operational foundation into disciplined evolution: current ALTER TABLE capabilities, versioned migrations, table-rebuild procedures, database tests, and compatibility checks across SQLite versions/builds.