Backing up SQLite without stopping the app

Copying a SQLite file with cp while the app is writing to it gives you a file that usually opens. Usually is not a word I want near backups.

Option 1: VACUUM INTO

Since SQLite 3.27:

VACUUM INTO '/backups/app-2025-08-20.db';

It writes a consistent, compacted copy of the database to a new file. It runs inside a read transaction, so writers in WAL mode keep working.

Option 2: the online backup API

sqlite3 /srv/app/app.db ".backup '/backups/app.db'"

This uses the online backup API, which copies pages incrementally and restarts if the source changes underneath. For big, busy databases VACUUM INTO tends to be more predictable; for small ones either is fine.

Then check it

A backup you haven't restored is a hope. My nightly job does:

sqlite3 "$OUT" "PRAGMA integrity_check;" | grep -qx ok || exit 1
zstd -q --rm "$OUT"

and once a month I restore the latest one on my laptop and click around.

WAL mode

If you haven't already, PRAGMA journal_mode=WAL; is the single best change for an app with concurrent readers. It also makes both backup methods above friendlier.