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');

Enter fullscreen mode Exit fullscreen mode

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;'

Enter fullscreen mode Exit fullscreen mode

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":"..."}

Enter fullscreen mode Exit fullscreen mode

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?