Automating SQLite Backups Safely Under Load

SQLite databases are beloved precisely because a copy of one file is a backup, until a write is happening when you copy it. Under load, a naive cp can capture a page half-written, producing an archive that fails integrity checks or, worse, silently reads wrong. The good news: SQLite ships a purpose-built online backup API, wrapped in the .backup dot-command, that produces a consistent copy even while applications write. Automating it on a VPS is a small, near-bulletproof pipeline.

Why cp fails mid-write

SQLite writes pages to the database file in groups, so a byte-level copy at the wrong moment can mix old and new versions of different pages. In WAL mode the durable data mostly lives in the -wal sidecar file: copying only the main file misses committed transactions, while copying both at staggered moments breaks the transaction ledger. The online backup API walks the b-tree and copies it page by page, retrying pages that changed during the copy, under the protection of the database's own locking. The result is atomic from the reader's point of view.

The .backup command

The simplest consistent backup is one line. The first argument is the source database path; the second, given in SQLite syntax, is the destination file:

sqlite3 /srv/data/app.db ".backup '/var/backups/sqlite/app.db'"

Then make the copy prove itself before you trust it:

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

A healthy database answers with the single word ok.

The online backup API in code

The same mechanism is available to applications directly. sqlite3.Connection.backup performs an incremental online backup with optional progress callbacks, and makes a scheduled Python job trivial:

import sqlite3

src = sqlite3.connect('/srv/data/app.db')
dst = sqlite3.connect('/var/backups/sqlite/app.db')
with dst:
    src.backup(dst)
dst.close()
src.close()

On SQLite 3.27 and later, VACUUM INTO achieves the same result in one statement without taking a write lock: it writes a new, compact database to the given path while the source keeps serving reads.

Automate without breathing down the database's neck

A cron wrapper that snapshots nightly, keeps a short retention, and verifies each copy is enough for most single-node apps:

#!/usr/bin/env bash
# /usr/local/bin/sqlite_backup.sh
set -euo pipefail
SRC=/srv/data/app.db
DST_DIR=/var/backups/sqlite
stamp=$(date +%F)
mkdir -p $DST_DIR
sqlite3 $SRC ".backup '$DST_DIR/app.$stamp.db'"
sqlite3 $DST_DIR/app.$stamp.db "PRAGMA integrity_check;" | grep -q ok
find $DST_DIR -name 'app.*.db' -mtime +14 -delete

If your writers hold write locks long enough to collide with the backup, give sqlite3 a busy timeout so a transient "database is locked" does not silently produce nothing:

sqlite3 -cmd ".timeout 5000" "$SRC" ".backup '$DST_DIR/app.db'"

Under high load: set the journal mode once

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;

WAL lets readers and a single writer coexist, which is exactly the load profile that makes plain file copies unreliable and the online backup API shine.

Takeaway

SQLite moved past "stop the app to copy the file" long ago. Use .backup for one-liners, the Connection.backup API or VACUUM INTO in code, verify with integrity_check, and schedule the lot under cron. It is a small pipeline, and it fits neatly on a low-cost VPS from netbayhosts.in.