cp/rsync of a live SQLite database misses uncommitted rows held in the WAL sidecar, use sqlite3 .backup for an atomic snapshot

SQLite databases running in WAL journal mode (default in modern SQLite, and what most application embeddings use) hold recent writes in <dbname>-wal and <dbname>-shm sidecar files until a checkpoint folds them into the main file. A plain cp <dbname> or rsync <dbname> copies only the main file, so any rows written since the last checkpoint are invisible in the copy, the destination opens cleanly with no warning, the schema looks right, and queries silently return a stale row set. Symptom seen 2026-05: SELECT COUNT(*) against the source returned N rows but the same query against the cp'd copy returned N−1 (the most recent insert lived in local.db-wal). Verify by listing the parent dir: presence of *-wal and *-shm files alongside *.db is the tell. DELETE-mode journals don't have this issue (everything's in the main file), but you should not assume mode.

When you need a point-in-time snapshot of a SQLite DB that another process may still be writing to, prefer sqlite3 SRC ".backup DEST" (atomic, merges WAL) over cp/rsync. If you must use file-level copy, either checkpoint first (sqlite3 SRC "PRAGMA wal_checkpoint(TRUNCATE);") or copy all sidecars together (SRC, SRC-wal, SRC-shm) as a set, but .backup is simpler and safer. Quick check before trusting a copy: ls SRC-wal SRC-shm against the source; if either exists, file-level copies are not safe. Verify the row count matches between source and destination via SELECT COUNT(*) against the table you care about.