How to back up a SQLite database safely

Why cp is not a safe backup

A SQLite database is a single file, so copying it looks like a backup. It is only safe when no connection has the database open. Otherwise you can lose data in two ways:

• Torn copies. If a transaction writes while cp runs, the copy holds some old pages and some new ones. SQLite's own documentation lists this as a way to corrupt a database.
• Missing commits in WAL mode. In write-ahead log mode, a commit is appended to app.db-wal, not written into app.db. SQLite moves it into the main file at a checkpoint: by default when the WAL reaches 1,000 pages, or when the last connection closes.

The second one is quiet. Say the app has committed 2,500 orders and the last 500 are still in the WAL. A copy of app.db alone opens without complaint, passes PRAGMA integrity_check, and has 2,000 orders.

Check which mode your database uses. It prints wal, or delete for the default rollback-journal mode:

sqlite3 /var/lib/myapp/app.db "PRAGMA journal_mode;"

In WAL mode you also see app.db-wal and app.db-shm next to the database while it is open. The -wal file holds committed data; the -shm file is an index SQLite can rebuild.

Install the sqlite3 shell

The commands below use the sqlite3 command-line shell. On Debian and Ubuntu:

sudo apt install sqlite3

Ubuntu 24.04 ships SQLite 3.45.1; VACUUM INTO needs 3.27.0 or newer. Run the backup as root or as a user that can write to the database's directory: in WAL mode even a reader may need to create the -shm file.

Back up with .backup

.backup uses SQLite's online backup API. It copies the database page by page through SQLite's own locking, so it includes everything committed in the WAL and never picks up half a transaction.

sqlite3 /var/lib/myapp/app.db ".timeout 10000" ".backup '/var/backups/sqlite/app.db'"

• Each quoted argument after the database runs in order, as if typed at the sqlite> prompt.
• .timeout 10000 waits up to 10 seconds for a lock instead of failing at once with database is locked. That matters in rollback-journal mode, where a writer briefly locks readers out. In WAL mode, readers and writers don't block each other.
• The result is a bit-for-bit copy of the database, journal mode included.

One catch: the shell copies 100 pages at a time and releases its lock in between. If another connection writes during the copy, SQLite starts it over. On a large database written every second, .backup can take a long time or never finish. Use VACUUM INTO there.

Back up with VACUUM INTO

sqlite3 /var/lib/myapp/app.db ".timeout 10000" "VACUUM INTO '/var/backups/sqlite/app.db'"

VACUUM INTO writes a new, compacted database from one consistent read. It leaves out free pages, so the copy can be smaller, and deleted rows leave no trace in it. It uses more CPU than .backup but does not restart when the database changes, which suits busy databases.

• The target must not exist, or must be empty. Otherwise it fails with output file already exists.
• Tables without an explicit INTEGER PRIMARY KEY may get new rowids in the copy. If your app stores those implicit rowids anywhere, use .backup, which copies pages exactly.

Back up as SQL with .dump

sqlite3 /var/lib/myapp/app.db .dump | gzip -c > /var/backups/sqlite/app.sql.gz

.dump writes the schema and every row as SQL text, reading inside one transaction, so it is consistent too. Text is easy to diff, compresses well and can be loaded into another engine, but it restores more slowly than a database file. A complete dump ends with COMMIT;. If the shell hit errors while reading, the last line is ROLLBACK; -- due to errors, and loading it restores nothing:

zcat /var/backups/sqlite/app.sql.gz | tail -n 1

Which method to use

sqlite3_rsync comes with SQLite 3.47.0 and later, so it is not in Ubuntu 24.04's package. Streaming replication tools go further and copy the WAL to object storage as it is written, which can cut the writes you could lose from a day to seconds, at the cost of one more process to run and watch.

Check the copy

Open the copy, not the live database, and run an integrity check. A healthy file returns one line, ok:

sqlite3 /var/backups/sqlite/app.db "PRAGMA integrity_check;"

integrity_check looks for out-of-order or malformed records, missing pages, broken indexes, and UNIQUE, CHECK and NOT NULL violations. On large files, PRAGMA quick_check; skips the UNIQUE and index checks and runs much faster. Neither looks at foreign keys; PRAGMA foreign_key_check; returns no rows when they are intact:

sqlite3 /var/backups/sqlite/app.db "PRAGMA foreign_key_check;"

Then compare a count you know with the live database:

sqlite3 /var/backups/sqlite/app.db "SELECT count(*) FROM orders;"

Restore

Stop everything that writes to the database. Move the current file aside together with its -wal and -shm files, put the backup in its place, fix the owner and start the app:

sudo systemctl stop myapp

sudo mkdir -p /var/lib/myapp/before-restore

sudo mv /var/lib/myapp/app.db* /var/lib/myapp/before-restore/

sudo cp /var/backups/sqlite/app.db /var/lib/myapp/app.db

sudo chown myapp:myapp /var/lib/myapp/app.db

sudo systemctl start myapp

The shell can also restore in place with .restore, which writes the backup into the database through SQLite's locking, so a leftover -wal cannot interfere. Stop the app first anyway:

sqlite3 /var/lib/myapp/app.db ".restore '/var/backups/sqlite/app.db'"

To restore a .dump, load it into a new file and move that into place the same way:

zcat /var/backups/sqlite/app.sql.gz | sqlite3 /var/lib/myapp/app-restored.db

If the live file is damaged and you have no good backup, .recover salvages what it can as SQL, where .dump stops at the first sign of damage:

sqlite3 /var/lib/myapp/before-restore/app.db .recover | sqlite3 /var/lib/myapp/recovered.db

Apps that use SQLite

• Laravel: new apps use SQLite by default, in database/database.sqlite. See backing up Laravel for the rest of the app.
• Rails: new apps default to SQLite, with the database files under storage/. Back up every .sqlite3 file there.
• Vaultwarden: data/db.sqlite3, with attachments as separate files. Its wiki recommends .backup and notes that its Docker image has no sqlite3 binary, so run the backup on the host.

For any app in Docker, run sqlite3 on the host against the file in the bind mount or volume, as above; see Docker volume backups. Back up uploaded files too. The database is rarely the whole app.

Run it every night with cron

This script makes a .backup copy under a temporary name, refuses to keep it unless integrity_check says ok, compresses it and deletes copies older than seven days:

#!/usr/bin/env bash
set -euo pipefail

DB="/var/lib/myapp/app.db"
BACKUP_DIR="/var/backups/sqlite"
KEEP_DAYS=7
OUT="$BACKUP_DIR/app-$(date +%Y-%m-%d_%H%M).db"

mkdir -p "$BACKUP_DIR"
trap 'rm -f "$OUT.partial"' EXIT

sqlite3 "$DB" ".timeout 10000" ".backup '$OUT.partial'"

result=$(sqlite3 "$OUT.partial" "PRAGMA integrity_check;")
if [ "$result" != "ok" ]; then
  echo "integrity_check failed: $result" >&2
  exit 1
fi

mv "$OUT.partial" "$OUT"
gzip "$OUT"

find "$BACKUP_DIR" -name 'app-*.db.gz' -type f -mtime +"$KEEP_DAYS" -delete

sudo chmod 755 /usr/local/bin/sqlite-backup.sh

20 2 * * * root /usr/local/bin/sqlite-backup.sh >> /var/log/sqlite-backup.log 2>&1

A copy on the same disk dies with the server. Send /var/backups/sqlite off the machine as well, for example with rclone, and see cron backup schedules for catching a job that stops running.

Common errors

Frequently asked questions

Can I copy a SQLite file while the app is running?

Not safely. Use .backup or VACUUM INTO. A plain copy is only consistent when no connection has the database open, and any leftover -wal or -journal file must be copied with it.

Do I need to back up the -wal and -shm files?

Not with .backup, VACUUM INTO or .dump, which read committed data from both files. If you copy files with the app stopped, copy the -wal file too if it exists. The -shm file is not needed.

Is VACUUM INTO better than .backup?

It makes smaller copies and does not restart when the database changes, at the cost of more CPU. .backup makes an exact copy with less work.

Does a backup lock a SQLite database?

It reads under a shared lock. In WAL mode writers carry on; in rollback-journal mode a writer has to wait for each read to finish before it can commit.