Backing up a live SQLite database

Copying the database file while an application writes to it can produce a copy that is half old and half new. In WAL mode it is worse: recent commits live in the -wal file, so a copy of the main file alone silently misses them.

Use the backup API

The sqlite3 shell exposes SQLite's online backup API. It takes a consistent snapshot while readers and writers keep working:

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

Since SQLite 3.27 there is also VACUUM INTO. It writes a compacted copy, which is handy when the file has accumulated free pages:

sqlite3 /var/lib/app/app.db "VACUUM INTO '/var/backups/app/app.db'"

Check the copy

A backup that has never been opened is a hope, not a backup:

sqlite3 /var/backups/app/app-2026-01-01.db 'PRAGMA integrity_check'

The answer should be a single ok.

Keep a few, not all

find /var/backups/app -name 'app-*.db' -mtime +14 -delete

Two weeks of daily copies is usually enough for a small service. Anything older than that belongs off the machine anyway.