Why copying the file is not a backup, what to do instead, and how to prove the result actually opens.
SQLite keeps a whole database in a single file, which makes the backup strategy look obvious: copy the file. It works, it is fast, and it produces a file that opens cleanly afterwards — which is exactly what makes the failure so hard to notice. The copy opens. It is simply older than you think, or internally inconsistent in a way nothing complains about until the day you need it.
In the default journal mode, a write is applied to the main database file and an accompanying journal holds the information needed to roll it back. Copy the main file mid-transaction and you get a database that contains a half-applied write with no journal to undo it.
In write-ahead logging mode — which most people turn on, because it lets readers and a writer work at the same time — the arrangement is inverted, and this is the part that catches people. New writes are appended to a separate write-ahead log file and are not in the main database file at all. They are folded back into it later, at a checkpoint. Between checkpoints, the main file is a valid database that is simply missing everything recent.
So copying just the .db file on a system in WAL mode gives you a file that opens without error, passes an integrity check, and is missing however much work has happened since the last checkpoint. That might be seconds. On a low-traffic application it can be days.
If there is a -wal file and a -shm file sitting next to your database, the database is in write-ahead logging mode and a copy of the main file on its own is not a backup. Copying all three together is better, but only if nothing writes during the copy — and nothing guarantees that.
SQLite has an online backup interface for exactly this. It takes a read lock, copies the database page by page, notices if a page changes underneath it and re-copies that page, and produces a single consistent file with the write-ahead log already folded in. It works safely while the application is running and writing.
From the command line that is sqlite3 live.db ".backup /path/to/backup.db". From Python it is the standard library's Connection.backup(). Both are one line, and both are the entire reason not to reach for cp.
Run PRAGMA integrity_check; against the backup — it should answer ok and nothing else. This catches structural damage, but not staleness, which is the failure mode that actually bites.
Staleness is only detectable against expectation, so the backup job should record a number it can be judged by: the row count of the busiest table, or the newest timestamp in it. A backup whose newest record is four days old is a failed backup that reported success, and comparing that figure to the live one turns an invisible failure into an obvious one.
Once a month, take the most recent backup, copy it to a scratch directory, point a throwaway instance of the application at it and open the thing. This is the only step that actually tests a backup, and it is the one step everybody skips. It also rehearses the restore, so that on the day it matters you are following a procedure you have done before rather than improvising against a deadline.
The old rule still holds and is worth restating for a home setup, where the temptation is to keep the backup on the machine being backed up. Three copies of the data. On at least two different kinds of storage. At least one of them physically somewhere else — because a fire, a theft or a flood takes the machine and the external drive sitting next to it in the same event.
Retention matters as much as frequency. A single backup that is overwritten every night protects against hardware failure and nothing else: corrupt a table on Tuesday and Tuesday night's job faithfully backs up the corruption. Keep a rolling set — a week of dailies, a few monthlies — so there is a version from before the mistake.
And check what the backup itself is exposed to. A backup that a compromised machine can reach and overwrite is not protection against the thing most likely to destroy your data on purpose. Pull backups from elsewhere, or write them somewhere the source machine cannot then delete.