A live database may span three files
In WAL mode, recent committed pages can live in database-wal while shared-memory coordination lives in database-shm. Copying only the main file can produce a stale or unusable result. Copying all three with ordinary file commands still has a race: the application can change them between copies.
sqlite3 /srv/app/app.db 'PRAGMA journal_mode;'
ls -l /srv/app/app.db*Stopping the application before a file-level copy is valid when downtime is acceptable. For an online backup, use SQLite's own backup API.
Create a consistent snapshot
set -Eeuo pipefail
umask 077
stamp=$(date -u +%Y%m%dT%H%M%SZ)
dest="/var/backups/app/$stamp"
install -d -m 0700 "$dest"
sqlite3 /srv/app/app.db \
".timeout 10000" \
".backup '$dest/app.db'"The backup API coordinates with SQLite and produces a transactionally consistent destination database while the application stays online. A busy timeout gives short write transactions time to finish instead of failing immediately.
Use a SQLite binary compatible with the database. Applications that require SQLCipher, custom VFS modules, or loadable extensions need a backup tool built for the same format.
Verify the backup, not the source
backup="file:$dest/app.db?mode=ro&immutable=1"
sqlite3 -readonly "$backup" 'PRAGMA quick_check;'
sqlite3 -readonly "$backup" 'PRAGMA integrity_check;'
test -z "$(sqlite3 -readonly "$backup" 'PRAGMA foreign_key_check;')"
(cd "$dest" && sha256sum app.db > SHA256SUMS)
(cd "$dest" && sha256sum -c SHA256SUMS)quick_check is fast enough for every run. integrity_check is more expensive and can run on a schedule. foreign_key_check is separate: a structurally sound file can still contain broken references.
Do not require backup bytes to match the live database. The live file may change immediately after the snapshot, and SQLite can represent equivalent databases with different page layouts.
Rehearse restore away from production
A checksum proves stored bytes have not changed. It does not prove the application can use them. Open the backup in a temporary location, inspect the expected schema, and run a few non-secret row-count or metadata queries.
sqlite3 -readonly "$backup" '.tables'
sqlite3 -readonly "$backup" \
"SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"A real restore belongs in a maintenance window: stop the writer, preserve failed live files, install the verified backup atomically, fix ownership and mode, then start the service and run an application-level check. Never discover that sequence for the first time during an outage.
Backup checklist
- Use the SQLite backup API or stop all writers.
- Store snapshots in a root-only directory.
- Run quick, integrity, and foreign-key checks at suitable intervals.
- Create and verify a checksum manifest.
- Keep retention and off-host copy policies separate from snapshot creation.
- Monitor backup age and job failures.
- Rehearse restore against a temporary application instance.