Chapter 16 · Backup, Export, Recovery, Integrity, and Disaster Preparedness
.dump, SQL Logical Exports, and Migration/Interchange Uses
Use logical SQL exports for inspection, migration, and reconstruction while understanding how they differ from physical SQLite backups and where SQLite-specific schema features reduce portability.
Learning outcomes
A logical export describes database content as SQL statements. It is not a page-level snapshot and it is not the same artifact as the file produced by .backup. This distinction matters for restore speed, inspection, source control, cross-version migration, and specialized SQLite objects.
Distinguish physical database backups from logical SQL exports.
Use current CLI .dump and .schema commands and understand object-pattern selection.
Restore an SQL dump into a fresh SQLite database and compare expected schema/data state.
Identify SQLite-specific DDL, pragmas, triggers, virtual tables, and extensions that reduce cross-engine portability.
Explain why logical export can be slow or large for big databases.
Use dumps for human inspection/version control without treating them as the only disaster-recovery mechanism.
Physical and logical backups answer different questions
| Question | Physical SQLite backup | Logical SQL dump |
|---|---|---|
| What is copied? | Database pages into another SQLite database | DDL and DML text capable of reconstructing content. |
| How fast to restore? | Usually direct database use/copy | Must parse/execute SQL and rebuild indexes/structures. |
| Human-readable? | No | Yes, mostly. |
| Preserves exact file layout/free pages? | Backup API preserves database content as pages; VACUUM INTO rebuilds layout | No; reconstruction chooses a new physical layout. |
| Useful for code review/schema diff? | Limited | Often very useful. |
| Cross-engine portable? | No | Only partially; emitted SQL can contain SQLite-specific syntax/features. |
The CLI .dump and .schema commands
Current CLI help describes .dump ?OBJECTS? as rendering database content as SQL and .schema ?PATTERN? as showing matching CREATE statements. Dot-commands run in the shell, so they are not valid SQL sent through sqlite3_prepare().
# Exportsqlite3 fieldnotes.sqlite .dump > fieldnotes.sql# Inspect schema onlysqlite3 fieldnotes.sqlite .schema > fieldnotes-schema.sql# Restore into a NEW databasesqlite3 restored-from-dump.sqlite < fieldnotes.sql# Verify the rebuilt databasesqlite3 restored-from-dump.sqlite "PRAGMA integrity_check; PRAGMA foreign_key_check;"When selecting objects, test the exact CLI version you deploy and inspect .help dump. The broad lesson is stable: selection by object/pattern is useful for targeted interchange, but dependencies can make a partial dump incomplete as a standalone restore.
A dump is executable input—treat it as code
A dump can contain CREATE statements, INSERTs, triggers, virtual-table declarations, and other SQL. Before executing a dump from an unknown or untrusted source, review it and use an appropriately isolated workflow. Current SQLite CLI also has a --safe mode for reducing host-side effects when processing untrusted scripts, but safe restore policy still belongs to your deployment environment.
SQL can change database content and some SQLite configurations expose functions/extensions with host side effects. A dump should be handled like executable migration code, not like a passive CSV file.
Restore into a fresh database
A logical restore is easiest to reason about when the destination file does not already contain application objects. Create a new target, execute the dump, then compare both structure and data.
-- Run on source and restored database and compare results.SELECT type, name, tbl_nameFROM sqlite_schemaWHERE name NOT LIKE 'sqlite_%'ORDER BY type, name;SELECT 'site' AS object_name, count(*) AS rows FROM siteUNION ALLSELECT 'device', count(*) FROM deviceUNION ALLSELECT 'maintenance_note', count(*) FROM maintenance_note;PRAGMA application_id;PRAGMA user_version;PRAGMA integrity_check;PRAGMA foreign_key_check;The local executable analogue used Python’s Connection.iterdump() because no standalone CLI is installed. Restoring that script recreated 1,200 maintenance notes and the expected six user-visible table/index/view schema objects in the test fixture.
Why SQLite dumps are not universally portable SQL
| Feature | Portability issue |
|---|---|
| Dynamic typing / SQLite declared types | Another DBMS can map types differently or reject SQLite-specific declarations. |
WITHOUT ROWID, STRICT, generated columns | Syntax/semantics differ across engines and versions. |
| Triggers and views | SQL dialect and trigger semantics vary significantly. |
| FTS5/RTree/virtual tables | Require SQLite modules; another engine will not understand the virtual-table declaration. |
| PRAGMAs | SQLite-specific configuration, not portable SQL standard. |
| ROWID assumptions | Another engine may not provide SQLite rowid semantics. |
| Conflict/UPSERT syntax | Similar features exist elsewhere but details differ. |
If the goal is moving data to another product, it is often better to export explicit relational tables to an interchange format and separately translate schema/constraints than to assume .dump is a universal migration compiler.
Dump ordering and constraints deserve attention
SQLite’s dump output is designed for reconstruction by SQLite, but application-specific validation is still required. Foreign-key enforcement is connection-scoped, triggers may execute during custom/manual restore scripts, and extension-backed objects require their modules to be available. For complex deployments, test the exact dump/restore process in CI or release rehearsal rather than discovering an ordering dependency during an incident.
set -erm -f restore-test.sqlitesqlite3 restore-test.sqlite < fieldnotes.sqlsqlite3 restore-test.sqlite "PRAGMA integrity_check;"sqlite3 restore-test.sqlite "PRAGMA foreign_key_check;"sqlite3 restore-test.sqlite "SELECT count(*) FROM maintenance_note;"Size and speed tradeoffs
For a large database, SQL text can be much larger than a compact binary database and restoration must parse every statement, insert rows, and build indexes. Compression often helps because SQL text is repetitive, but that does not remove restore CPU time. Measure both backup and restore duration using realistic data.
| Use case | Dump fit |
|---|---|
| Schema review in pull requests | Strong fit: readable DDL can be reviewed. |
| Tiny reference dataset checked into source control | Often useful if secrets/private data are excluded. |
| Cross-version SQLite reconstruction | Useful, but test specialized features and target version. |
| Fast recovery of a very large production DB | Usually physical backup is operationally faster. |
| Cross-engine migration | Potential starting artifact, not guaranteed portable migration. |
Reproducible logical-export lab
import sqlite3source = sqlite3.connect("fieldnotes.sqlite")dump_sql = "".join(source.iterdump()) + ""source.close()with open("fieldnotes.sql", "w", encoding="utf-8") as f: f.write(dump_sql)restored = sqlite3.connect("logical-restore.sqlite")restored.executescript(dump_sql)print(restored.execute("SELECT count(*) FROM maintenance_note").fetchone()[0])print(restored.execute("PRAGMA integrity_check").fetchone()[0])restored.close()This validates the concept with the locally available driver. For production CLI behavior, use the official .dump command of the actual SQLite CLI version shipped in your environment.
Checkpoint
Physical or logical?
Choose the artifact intentionally.
- You want a human-reviewable representation of schema and seed data.
- You need the fastest practical restore for a large SQLite application.
- You plan to feed a dump directly into PostgreSQL without review.
- Your database contains FTS5 virtual tables.
- You want to compare expected row counts after reconstructing a database.
Review the answers
A dump is well suited to the first and fifth. A physical backup is usually the better operational starting point for the second. The third is unsafe as a portability assumption. For the fourth, the target SQLite environment must provide compatible FTS5 behavior and the dump/restore workflow must be tested.
Bridge to integrity diagnosis
Whether the artifact is a physical backup or a logical reconstruction, “SQLite opened the file” is only the first verification layer. Lesson 4 builds a health-check stack around quick_check, integrity_check, foreign_key_check, and application-specific invariants.