Back up SQLite safely

If every connection is closed, you can copy the database file. While the database is active, use a SQLite-aware backup method.

The reason is timing. A plain cp reads the file from start to finish while writes may land in the middle, so the copy can contain half of one transaction. Do not copy only notes.db while writes continue. In WAL mode, committed data may still be in notes.db-wal, so the copy misses transactions that were committed minutes ago.

Two safe methods

From the SQLite shell, create a consistent backup with:

.backup notes-backup.db

.backup uses SQLite’s online backup API. It copies the database page by page while coordinating with active connections, and the result is a valid snapshot even if writes happen during the copy. You can run it from a script too:

sqlite3 notes.db ".backup notes-backup.db"

Alternatively, use your library’s SQLite backup API or VACUUM INTO when that behavior fits the application:

VACUUM INTO 'notes-backup.db';

VACUUM INTO writes a fresh, defragmented copy — it’s a backup and a cleanup in one, and the output file is often smaller than the original. The trade-off is that it rebuilds the database rather than copying pages, so it does more work on large databases.

Either way, back up to a different disk or machine. A backup file sitting next to the original dies with the same disk.

A backup you haven’t tested is a guess

Open the backup separately and verify it:

sqlite3 notes-backup.db

PRAGMA integrity_check;
PRAGMA foreign_keys = ON;
PRAGMA foreign_key_check;
SELECT COUNT(*) FROM notes;

integrity_check should return ok. foreign_key_check should return no rows. The COUNT(*) should look plausible against what you know the database holds — a count of zero on a database you know is full means you backed up the wrong file.

A backup is useful only after you prove it restores. The realistic failure isn’t a corrupt copy; it’s a cron job that has been silently backing up an empty or stale file for months, discovered on the day you need it. Automate the verification queries alongside the backup itself, and alert when integrity_check says anything but ok.