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.

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?