SQLite Hot Backup: 11 Rounds, and a Copy 14,500 Rows Short

A reader sent me a question I could not answer with a shrug: the backup job runs every night, every log line is green, so why was there nothing to restore on the day it mattered?

I turned that question into something measurable. One SQLite database, four common ways of "taking a backup", five to six rounds each, with writes running the whole time. Then I compared every snapshot row by row.

The result is worse than I expected. A backup that opens is not necessarily a correct backup. The most common approach — copy the .db file somewhere else — lost committed data in four of five rounds. The worst round was 14,500 rows short, and PRAGMA integrity_check returned ok in all five.

Everything below is what I actually ran: Windows, CPython 3.13, the bundled sqlite3 3.53.1. No Docker, no cloud console, no vendor dashboard. You can reproduce every number on your own machine. The body of this post has no product affiliate links and no product recommendations; the book-list module at the foot of the page is mounted site-wide and has nothing to do with this topic.

Three findings first

Each of these has data behind it further down.

1. A single-file copy is consistent but stale. What you get is a perfectly legal state of the database from some earlier moment, so every structural check passes. The only way to catch it is to compare it against the live database, not against itself.

2. Copying all three files loses nothing, but it fails at random. One failure in five rounds, two in six, with the error [WinError 33] another program has locked a portion of the file.

3. Raise the checkpoint frequency and the single-file copy stops being stale and starts being corrupt. Two rounds out of six produced database disk image is malformed on open.

There are only two correct approaches: the sqlite3 backup API, or VACUUM INTO. On a 43 MB database they averaged 386 ms and 460 ms respectively, and both produced zero-loss snapshots in every round.

Setup, and how I decided what "consistent" means

The schema is one table: events(id INTEGER PRIMARY KEY, ts INTEGER, payload TEXT), where payload is a 200-byte filler string. 200,000 rows, 43.3 MB.

The writer runs in a separate process: 500 rows per batch, 50 ms between batches, one commit per batch, which works out to roughly 10,000 rows per second. The backup runs in the parent process while the writer is still going, and the writer is allowed to finish naturally afterwards.

To decide whether a snapshot is consistent I used a property that can actually be proven rather than eyeballed: because the writer commits whole batches, any consistent snapshot must be a contiguous prefix — ids from 0 upward with no gaps, a row count divisible by 500, and max(id) + 1 equal to the row count.

The deviation column below is snapshot rows minus rows visible immediately before the copy. A negative number means committed transactions were lost. A positive number just means the writer got a few more batches in while the copy was happening, which is normal.

I also measured the cost of verification itself. On the 43 MB database, PRAGMA quick_check took 70–120 ms and PRAGMA integrity_check took 71–108 ms. At this size, verifying every backup is effectively free — which matters, because it removes "too expensive" as an excuse for skipping the check.

Single-file copy: consistent, and 1.45 seconds behind

This is what almost everyone does: shutil.copy2(db_path, backup_path), or cp, or a single-file rsync, or a cloud drive syncing the .db. With the default checkpoint settings, five rounds:

Round	Visible before copy	Snapshot rows	Deviation	integrity_check
1	203,500	200,000	−3,500	ok
2	232,000	232,000	0	ok
3	262,500	248,000	−14,500	ok
4	292,500	287,000	−5,500	ok
5	323,000	319,000	−4,000	ok

Four out of five rounds lost data. The worst was 14,500 rows — at 10,000 rows per second, that copy is 1.45 seconds behind. Round 2 came out at zero deviation because that copy happened to land in the window just after a checkpoint finished. So it is not "occasionally inaccurate"; it is "usually inaccurate, occasionally lucky".

The official documentation is blunt about why. A WAL-mode database consists of the main file plus -wal and -shm, and the WAL file is not scratch space:

The WAL file is part of the persistent state of the database and should be kept with the database if the database is copied or moved.

The sentence right after it states the consequence:

If a database file is separated from its WAL file, then transactions that were previously committed to the database might be lost, or the database file might become corrupted.

SQLite checkpoints automatically when the WAL reaches 1000 pages, about 4 MB, moving those frames back into the main file and recycling the WAL. So the main file holds the state as of the last checkpoint, and the WAL holds everything committed since. Copy only the main file and you have copied a snapshot from the last checkpoint.

What makes this dangerous is that it is entirely legal. This is not corruption, it is time travel. It really is a complete, valid database as of some real moment, so integrity_check says ok and the page structure, indexes and constraints are all fine. Any check that asks "does it open, is the structure sound" will be satisfied.

One related trap: if your plan is "delete the WAL, then copy", the documentation recognises exactly one safe way to remove a WAL file — open the database with sqlite3_open() and immediately close it with sqlite3_close(), letting SQLite run its final checkpoint first.

Three-file copy: zero loss, random failure

If one file is not enough, copy all three. No rows were lost in five rounds, but one round threw:

PermissionError: [WinError 33] another program has locked a portion of the file.

In the aggressive-checkpoint group of six, the same error appeared twice. Windows takes byte-range locks on a -wal file held by the writing process, and shutil.copy2 gets blocked when it opens it. Linux usually will not raise this — but do not celebrate. Copying three files is not atomic: the main file, -wal and -shm are read at three different instants. If the writer commits a batch between two of those reads, your three files come from three different moments. Whether that splice opens at all depends on whether the WAL frame checksums and salt values happen to line up. That is a probability, not a guarantee.

It worked in all five rounds here and I still will not recommend it. A backup method that throws at random, after it has already overwritten half of the destination, is a time bomb in a script. When cp a b fails, b may already be a half-written file. If your job only checks the exit code and never checks the artifact, that half file becomes today's backup.

Raise the checkpoint frequency and the copy is simply corrupt

Both groups above used default settings. Plenty of projects set wal_autocheckpoint to 1 so the WAL cannot grow, or call PRAGMA wal_checkpoint(TRUNCATE) on a schedule. I set the writer's wal_autocheckpoint to 1 — a checkpoint on every transaction — and ran six rounds:

Outcome	Rounds	Note
Snapshot valid, zero deviation	4/6	The main file is almost always current, so it "works"
DatabaseError: database disk image is malformed	2/6	Fails on open; not even integrity_check can run

The same operation, under two checkpoint configurations, produces two completely different failure modes: silent staleness in one, hard corruption in the other. And the aggressive configuration is exactly the "optimisation" teams apply to stop the WAL from growing. The optimisation breaks the backup, and it breaks it non-deterministically.

This is the point I most want to make from this round: backup correctness depends on runtime configuration, not on the backup script. Change one PRAGMA that looks unrelated and your backup reliability changes class.

The two approaches the documentation actually recommends

SQLite offers two supported ways to snapshot a database that is in use, and the costs are measurable.

The backup API. The guarantee is bit-for-bit identity:

The effect of completing the backup call sequence is to make the destination a bit-wise identical copy of the source database as it was when the copying commenced. (The destination becomes a "snapshot.")

In Python it is three lines:

src = sqlite3.connect(db_path)
dst = sqlite3.connect(backup_path)
src.backup(dst)
dst.close(); src.close()

On the 43 MB database this averaged 386 ms. On an 82.5 MB, 380,000-row database, a single copy took 438 ms — 188 MB/s. Zero loss in every round. The documentation also warns that if the source is modified mid-backup, SQLite detects it and "usually restarts the backup process", and that "if the backup process is restarted frequently enough it may never run to completion". On a write-heavy database, put a timeout on it.

VACUUM INTO. The documentation names it as an alternative:

The VACUUM command with an INTO clause is an alternative to the backup API for generating backup copies of a live database.

The trade-off is spelled out too: the output is minimal in size, deleted content is purged, no forensic traces remain; in exchange it burns more CPU and cannot be incremental. Measured: 460 ms average on the 43 MB database, zero loss in five rounds. That is about 19% slower than the backup API, and what you buy is a smaller artifact with deleted rows genuinely gone — which matters if the backup leaves your control or is archived long-term.

One limitation you must know: the target file must not already exist, or must be empty, otherwise it fails. I pointed it at an existing 1 KB file and got:

DatabaseError: file is not a database

That message is actively misleading. It reads like "your source is broken"; the actual problem is that the destination path holds something that is not a database.

The restore drill: 340 ms end to end, and almost everyone verifies the wrong thing

Getting the backup right is only the first gate. The second is what the restore drill actually verifies.

Start with the cost, because cost is the usual excuse. An 82.5 MB, 380,000-row artifact, restored to a new path and fully checked:

• copy the file: 64 ms
• open it, COUNT(*), integrity_check: 276 ms
• total: 340 ms

340 milliseconds. "Drills are expensive, so we do one a quarter" does not survive contact with that number. You can run this after every single backup.

But speed is not the point. Verifying the right thing is. I tested two traps.

Trap one: a zero-byte file also returns ok. I created an empty file with open(path, "wb").close() and connected to it:

• PRAGMA integrity_check → ok
• tables in sqlite_master → 0
• SELECT ... FROM events → OperationalError: no such table: events

So if your drill is "open it and run integrity_check", an empty file scores full marks. The realistic way you get an empty backup is a job that creates the destination with > redirection, fails halfway and exits, leaving a zero-byte file — while your alerting only watches whether the job exited 0.

Trap two: a stale snapshot also returns ok. Already demonstrated above: the copy that was 14,500 rows short passed integrity_check in every round. A structural check answers "is this file internally coherent", not "is this the moment I wanted".

So a drill should verify three numbers that can be reconciled, not whether the file opens:

1. Row count against the live database, or against the last known baseline, within an expected window;

2. Max primary key, SELECT MAX(id), which surfaces tail loss far better than a row count;

3. Newest business timestamp, which translates directly into "how many minutes of data would we lose if we restored right now" — your RPO.

Then add one read-only business query to confirm the tables, indexes and views are all present. All four together are still a few hundred milliseconds, and together they catch the empty file, the stale snapshot and the structural damage — which happen to be every failure mode that appeared in this experiment.

A drill checklist you can copy

1. Take the snapshot with src.backup(dst) or VACUUM INTO. Do not copy files.

2. Immediately record metadata: row count, MAX(id), MAX(ts), file size, elapsed time.

3. Restore the snapshot to a temporary path — never over the live database — and open it.

4. PRAGMA integrity_check must return the single row ok. Multiple rows mean structural problems, and it reports at most 100 of them by default.

5. Reconcile row count, MAX(id) and MAX(ts) against the live database. Any mismatch is an alert. This step is the actual verification.

6. Run a real read-only business query, not SELECT 1, and confirm tables, indexes and views exist.

7. Record the total elapsed time. That is your RTO floor.

The division of labour between steps 4 and 5 matters. integrity_check verifies formatting and consistency — page ordering, record format, missing pages, index entries, UNIQUE/CHECK/NOT NULL constraints, the freelist, sections used more than once or not at all. It does not check foreign keys: "PRAGMA integrity_check does not find [FOREIGN KEY] errors", which is what PRAGMA foreign_key_check is for. And it cannot check whether the data is current, because it has no idea what the live database contains.

On the performance split: quick_check skips UNIQUE constraints and index-versus-table consistency, and the documentation gives O(N) versus O(N log N). At 43 MB both land around 100 ms and you cannot tell them apart. At tens of GB, quick_check for routine monitoring and a full integrity_check before a release is a reasonable split.

Four errors, verbatim

All four were reproduced on this machine.

PermissionError: [WinError 33] another program has locked a portion of the file.

Raised while copying -wal. On Windows the active WAL file carries byte-range locks held by the writer. Retrying does not fix this properly — a successful retry only means you happened to pick a gap between two commits; it does not mean the three files came from the same instant. Switch to the backup API or VACUUM INTO.

DatabaseError: database disk image is malformed

Two of six rounds under per-transaction checkpointing with a single-file copy. It fails on open, before any verification can run. The main file was being rewritten in place while it was read, so you captured half-written pages. When you see this, suspect the backup method before you suspect the source database.

DatabaseError: file is not a database

Two sources. Opening a -wal file as if it were a database (I used a real 136.8 KB WAL file), and pointing VACUUM INTO at a destination that already holds a non-empty file. The second is the more misleading, because the message describes the file while the bug is in the argument.

OperationalError: no such table: events

This is the illusion of a successful restore: the file opens, integrity_check returns ok, and the database contains no tables at all. A zero-byte file lands here. If your restore script prints "restore successful" right after integrity_check, this error is invisible — until the day you actually need to query the data.

Where these conclusions stop

• Measured only on SQLite 3.53.1, bundled with CPython 3.13. PostgreSQL and MySQL behave differently; do not transplant this. If your problem is PostgreSQL WAL growth, the replication-slot experiment I ran on 18.6 covers the same family of failure from the other side: PostgreSQL 18.6 replication slots.
• Local Windows disk. WAL does not work over a network filesystem — "WAL does not work over a network filesystem", because WAL requires all processes to share a small amount of memory. If the database lives on NFS or SMB, the premise of hot backup is already gone.
• Timings are hardware-specific. 188 MB/s and 386 ms are my machine; yours may be twice as fast or half. The two mechanism-level findings — single-file copies go stale, three-file copies throw lock errors — do not change with hardware.
• I measured snapshot correctness, not off-site disaster recovery. A perfect backup on the same disk protects you from a mistaken DELETE, not from a dead drive.
• If your actual question is "did the data survive a schema change", that is a different layer. My Alembic autogenerate experiment covers what migration scripts silently miss, and it does not overlap with backups.

The takeaway

The expensive lesson here is not "file copies are bad". It is that the verification method does not match the failure mode. We prove backups work by opening them, and none of the three failures in this experiment — zero bytes, 1.45 seconds stale, page-level corruption — was caught by "it opens". The first two were not caught by integrity_check either.

What catches them is reconciliation: row count, max primary key, newest timestamp, compared against the live database. No extra tooling, one SQL statement each, a few hundred milliseconds. Moving a backup from "the job succeeded" to "the numbers reconcile" is not a technical gap. It is the check that keeps getting skipped.

If your backup script currently only inspects the exit code, change it tonight to inspect the artifact — row count first, then MAX(id), and integrity_check last. The other order works too, but reconciliation first tells you sooner.

👉 Join Xiaomi MiMo Platform: Leading AI model platform with cost-effective inference

👉 Join Aliyun AI: Top AI products with exclusive coupons for business innovation

📌 This article was AI-assisted generated and human-reviewed | TechPassive — An AI-driven content testing site focused on real tool reviews