Back Up SQLite’s WAL Before a Migration—Then Prove the Restore Works

A task database runs in WAL mode. You copy only app.db, apply a destructive migration, and discover that recent rows lived in app.db-wal. A backup that was never restored is only a hopeful file copy.

A 30-minute drill

PRAGMA journal_mode=WAL;
CREATE TABLE tasks(id INTEGER PRIMARY KEY, title TEXT NOT NULL);
INSERT INTO tasks(title) VALUES ('before-checkpoint');

Use SQLite's online backup mechanism, or force a reviewed checkpoint before copying a closed database. Do not copy an actively written main file and assume the sidecars are irrelevant.

sqlite3 app.db '.backup backup.db'
sqlite3 backup.db 'PRAGMA integrity_check; SELECT count(*) FROM tasks;'

Then run the migration, deliberately fail halfway, replace the database from backup.db, and execute both integrity and application-level assertions.

Failure	Evidence required
main file copied without WAL	expected row count differs
truncated backup	integrity check fails
newer schema opened by old app	compatibility test fails
restore to wrong path	application still sees migrated schema

A tiny manifest makes the artifact portable:

{"schema":7,"rows":{"tasks":101},"integrity":"ok","createdAt":"UTC","sha256":"..."}

Exit nonzero if any value differs after restore. Keep the broken fixture so the script proves it detects loss before demonstrating success.

This is a single-host SQLite drill. It does not cover replicated databases, storage snapshots, or zero-downtime migration. What assertion beyond integrity_check would catch a logically incomplete restore in your application?