Why Copying a Live Database File Can Be Unsafe

Learning outcomes

A SQLite database looks like one convenient file, which makes ordinary file-copy tools tempting. That mental model is safe only when the database is genuinely quiescent. During normal operation, SQLite may have transaction state in a rollback journal or committed state in a WAL file, so “copy the .sqlite file” is not automatically the same as “capture the database.”

One database can have several files in play

The main database file stores database pages. In rollback-journal modes, SQLite may temporarily create a journal that preserves original page content while a transaction updates the main file. In WAL mode, committed page versions are appended to the -wal file and later transferred back into the main database by checkpoints. A -shm file normally backs the WAL index used for coordination and fast lookup.

Cold copy means quiescent, not merely “the app looks idle”

A cold copy is a filesystem copy made after all processes that can use the database have closed it cleanly, or after a maintenance procedure has otherwise guaranteed quiescence. In normal WAL operation, closing the last connection usually performs a final checkpoint and removes the WAL/SHM files. At that point, copying the main file is straightforward.

STOP or quiesce every process that can open fieldnotes.sqliteVERIFY no database connection remains openVERIFY the expected database file is the one being copiedCOPY fieldnotes.sqlite to a new destinationOPEN the copy with SQLiteRUN integrity + foreign-key + business checksRECORD timestamp/version/application metadataONLY THEN mark the copy as a usable backup

Controlled WAL experiment: the main file can be stale

The following disposable experiment creates the schema before WAL mode, disables automatic checkpointing for the demonstration, commits two new rows into the WAL, and copies only the main database file while the connection remains open.

from pathlib import Pathimport shutil, sqlite3, tempfileroot = Path(tempfile.mkdtemp(prefix="wal-copy-demo-"))live = root / "live.sqlite"con = sqlite3.connect(live)con.execute("PRAGMA journal_mode=DELETE")con.execute("CREATE TABLE event(event_id INTEGER PRIMARY KEY, payload TEXT NOT NULL)")con.commit()assert con.execute("PRAGMA journal_mode=WAL").fetchone()[0] == "wal"con.execute("PRAGMA wal_autocheckpoint=0")con.execute("PRAGMA wal_checkpoint(TRUNCATE)")con.execute("INSERT INTO event(payload) VALUES ('wal-1'),('wal-2')")con.commit()print("live rows:", con.execute("SELECT count(*) FROM event").fetchone()[0])print("WAL exists:", Path(str(live) + "-wal").exists())print("SHM exists:", Path(str(live) + "-shm").exists())main_only = root / "main-only.sqlite"shutil.copy2(live, main_only)copy_con = sqlite3.connect(main_only)print("main-only rows:", copy_con.execute("SELECT count(*) FROM event").fetchone()[0])copy_con.close()con.close()

In the course verification run, the live connection reported 2 rows while the copied main file reported 0 rows. The copy was readable, but it represented an older state because the committed rows were still in the WAL. That is especially dangerous because a stale backup can look healthy.

Rollback-journal mode has a different failure shape

Rollback mode writes original page content to a journal before changing database pages. During an active write transaction, a -journal file can therefore exist even though the transaction is not committed. After a crash, SQLite may need a hot journal to roll the database back to a consistent state.

con = sqlite3.connect("rollback-demo.sqlite")con.execute("PRAGMA journal_mode=DELETE")con.execute("CREATE TABLE IF NOT EXISTS t(id INTEGER PRIMARY KEY, v TEXT)")con.commit()con.execute("BEGIN IMMEDIATE")con.execute("INSERT INTO t(v) VALUES ('not committed')")# Inspect directory now: rollback-demo.sqlite-journal normally exists.con.rollback()con.close()

This is an observation lab, not a recipe for copying or deleting journals. If you see a journal beside a database after an abnormal stop, let SQLite open the database and perform its documented recovery instead of manually “cleaning up” files.

Filesystem snapshots can be safe only with the right guarantee

Storage snapshots—from volume managers, filesystems, virtual-machine platforms, or cloud block storage—can be useful when they capture all relevant database state at one atomic point. But “snapshot” is not itself a guarantee: some systems capture each file independently, some require a filesystem freeze, and some are crash-consistent rather than application-consistent.

Backup decision tree

Is the database completely closed/quiescent?  YES -> Cold file copy is simple; verify the copied database.  NO  -> Is an SQLite-aware live snapshot required?           YES -> Online Backup API / CLI .backup                  OR VACUUM INTO when compact-copy tradeoffs fit.           NO  -> Does infrastructure provide an atomic crash-consistent                  snapshot of all database state?                    YES -> Use only with documented guarantees + restore test.                    NO  -> Do not invent a multi-file cp/copy sequence.Need human-readable / source-controlled SQL instead of a physical snapshot?  -> Use .dump, understanding that it is a logical export, not the same artifact.

Failure diagnosis before action

Checkpoint

Bridge to SQLite-aware backups

Now that the file-level failure modes are concrete, Lesson 2 introduces mechanisms that ask SQLite itself to construct a consistent snapshot: the CLI .backup/.restore commands, the online backup API that they build upon conceptually, and VACUUM INTO for compact copies.

Authoritative references

• SQLite Write-Ahead Logging
• SQLite Atomic Commit
• SQLite Online Backup API
• SQLite CLI