Tuesday, November 17, 2026

When Copying SQLite’s Main File Is Not the Same as Backing Up the Database

SQLite’s write-ahead logging (WAL) mode creates a practical backup distinction: the file named database.db may not contain the newest committed records by itself. A small, bounded experiment made that distinction visible without relying on a product incident or a benchmark.

The test used Python 3.14.9 with SQLite 3.53.1. It created a WAL-mode database, inserted three baseline records, committed them, and checkpointed the WAL. It then committed a fourth record while the source connection remained open. At that point the source database reported four records, and both its -wal and -shm sidecar files existed.

Two copies were made from that state. The first used an ordinary file copy of only the main .db file. The second used Python’s sqlite3.Connection.backup() against a new destination database.

  • The source contained 4 records.
  • The main-file-only copy opened normally but contained 3 records: only the checkpointed baseline.
  • The SQLite backup API copy contained all 4 records, including the post-checkpoint commit.

The useful detail is that the file-only copy was not obviously broken. It opened and returned valid rows; it was simply behind the state the source had already committed. In this setup, the newer record was in the WAL sidecar rather than in the copied main file.

This is a narrow experiment, not a universal verdict on every way of copying SQLite. A coordinated copy of the database and its sidecars, a quiesced application, or a different journal mode can change the result. The test also did not simulate a crash, concurrent writes, encryption, retention, or a complete restore procedure.

For a SQLite-backed application, the practical question is therefore not only whether a backup job copied a file successfully. It is whether the method captures SQLite’s current committed state and whether the resulting database can be restored. In this comparison, the SQLite-aware backup path did that; the main-file-only copy did not.

No comments:

Post a Comment

Comments are allowed as long as they touch the post in question and they do no contain any spam or crap.