Backing up SQLite in WAL mode without stopping writers

Copying the database file while a writer is active gives you a file that opens fine and is quietly missing the last few transactions, because they still live in the -wal file. Copying both files is a race.

Use the online backup API

The .backup command takes a consistent snapshot page by page and restarts if a writer changes a page it already copied.

sqlite3 app.db ".backup '/var/backups/app-$(date +%F).db'"

On busy databases set a busy timeout first, otherwise the backup gives up on the first lock.

VACUUM INTO

VACUUM INTO produces a compacted copy in one statement. It holds a read transaction for the whole run, so the WAL cannot be checkpointed until it finishes. Fine for a 200 MB file, unpleasant for 20 GB.

Verify

Run PRAGMA integrity_check on the copy, not the source. A backup you have never opened is a hope, not a backup.