I Copied a Live SQLite Database and the Copy Had No Tables: WAL Mode Keeps Commits in a Second File

How can a byte-for-byte copy of a SQLite database file be missing a table the writer had already committed? I copied a live database with shutil.copy, opened the copy, and asked it for the table I had just created. It said there was no such table.

The writer had committed every row. The copy was a faithful duplicate of the file I pointed it at. That file was not the whole database.

Disclosure: I make an Android camera app. Nothing below uses its code or describes how it stores anything. Every script here ran on one Linux machine with Python 3.10.12 and the SQLite library it links (3.37.2), against throwaway databases the scripts create in their own folder.

What the tests showed:

• In WAL mode, committed data can live entirely outside the main .db file. A copy of that file alone came back without the table, let alone the rows.
• The same copy works perfectly once the app closes, which is why a backup script can pass every test you run on a quiet machine and still produce empty backups in production.
• Forcing a checkpoint first can fail. One open read transaction in another connection kept the checkpoint from moving a single frame into the main file.
• Python already ships the safe tools. Connection.backup() and VACUUM INTO both produced complete, single-file copies while the writer was still connected.

Why one file is not the database

By default SQLite uses a rollback journal: changes go straight into the database file, and the journal holds the old content in case of a rollback. Write-ahead logging flips that around. SQLite's WAL documentation puts it plainly: "The original content is preserved in the database file and the changes are appended into a separate WAL file."

So while any connection is open, a WAL database is a set of files: app.db, app.db-wal, and a shared-memory index called app.db-shm. A commit is a record appended to the -wal file. The main file catches up later, during a step called a checkpoint.

The documentation is explicit about what that means for copying: "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." And on what goes wrong if you do not: "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."

Test one: copy the file while the writer is open

copy_test.py turns on WAL mode, creates a table, commits a batch of rows, and then copies the database three ways: the main file alone, the main file plus its -wal, and the main file alone again after the connection closes.

import os, shutil, sqlite3

def count(path):
    c = sqlite3.connect(path)
    try:
        return c.execute("SELECT count(*) FROM events").fetchone()[0]
    except sqlite3.Error as e:
        return "error: %s" % e
    finally:
        c.close()

def files(prefix):
    return sorted(f for f in os.listdir(".") if f.startswith(prefix))

db = sqlite3.connect("app.db")
print("journal_mode:", db.execute("PRAGMA journal_mode=WAL").fetchone()[0])
db.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, note TEXT)")
db.executemany("INSERT INTO events (note) VALUES (?)",
               [("event %d" % i,) for i in range(500)])
db.commit()
print("rows committed:", db.execute("SELECT count(*) FROM events").fetchone()[0])
print("files while open:", files("app.db"))

shutil.copy("app.db", "copy_main_only.db")
print("copy of app.db alone:", count("copy_main_only.db"))

shutil.copy("app.db", "copy_with_wal.db")
shutil.copy("app.db-wal", "copy_with_wal.db-wal")
print("copy of app.db + app.db-wal:", count("copy_with_wal.db"))

db.close()
print("files after close:", files("app.db"))
shutil.copy("app.db", "copy_after_close.db")
print("copy of app.db after close:", count("copy_after_close.db"))

Output:

journal_mode: wal
rows committed: 500
files while open: ['app.db', 'app.db-shm', 'app.db-wal']
copy of app.db alone: error: no such table: events
copy of app.db + app.db-wal: 500
files after close: ['app.db']
copy of app.db after close: 500

The first copy did not just lose recent rows. It lost the CREATE TABLE too, because the schema change was also a commit sitting in the -wal file. As far as the copied app.db knew, it was an empty database.

The second copy, with the -wal file carried alongside it, opened with every row. That is the documented behavior: SQLite reads the log when it opens the copy.

The third line is the trap. After db.close(), the -wal and -shm files were gone and a plain copy of app.db was complete. The WAL page describes exactly this: "When the last connection to a database closes, that connection does one last checkpoint and then deletes the WAL and its associated shared-memory file, to clean up the disk."

That is why this bug hides. If you test a backup script by stopping the app, running the script, and opening the result, you are testing the one moment when copying a single file is correct. The same script, run on a schedule while the app is up, copies a file that may be missing everything since the last checkpoint.

It also matters how the app stopped. The WAL page qualifies the clean-up rule: "if the last process to have the database open exits without cleanly shutting down the database connection ... then the WAL file might be retained on disk after all connections to the database have been closed." crash_test.py checks that by having a child process commit the same table and 500 rows, then exit with os._exit(0) without closing its connection.

import os, shutil, sqlite3, subprocess, sys

def count(path):
    c = sqlite3.connect(path)
    try:
        return c.execute("SELECT count(*) FROM events").fetchone()[0]
    except sqlite3.Error as e:
        return "error: %s" % e
    finally:
        c.close()

child = r'''
import os, sqlite3
db = sqlite3.connect("crash.db")
db.execute("PRAGMA journal_mode=WAL")
db.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, note TEXT)")
db.executemany("INSERT INTO events (note) VALUES (?)", [("event %d" % i,) for i in range(500)])
db.commit()
os._exit(0)   # the process ends without closing the connection
'''
subprocess.run([sys.executable, "-c", child], check=True)
print("files after the writer exited:", sorted(f for f in os.listdir(".") if f.startswith("crash.db")))
shutil.copy("crash.db", "crash_copy.db")
print("copy of crash.db alone:", count("crash_copy.db"))

Output:

files after the writer exited: ['crash.db', 'crash.db-shm', 'crash.db-wal']
copy of crash.db alone: error: no such table: events

Nothing had the database open, and the main-file copy still had no table. "Stop the app, then copy" is only safe if the app shut down cleanly, and a crashed, killed or force-stopped app is the case a backup is most likely to meet.

Is copying both files safe, then?

It worked above, but the two copies happen at different moments. If a write lands between them, the pair no longer describes one point in time.

SQLite's page on how to corrupt a database describes this risk for any background backup job: "The backup copy then might contain some old and some new content, and thus be corrupt." It also gives the condition under which a plain file copy is fine: "It is also safe to make a copy of an SQLite database file as long as there are no transactions in progress while the copy is taking place." A generic file-sync tool has no way to know whether that is true at the moment it reads the file.

Test two: checkpoint first, then copy

The obvious fix is to force a checkpoint right before the copy so the main file is current. checkpoint_test.py tries that with a second connection holding a read transaction open, the kind of thing a long-running report query or a forgotten cursor does.

import os, shutil, sqlite3

for f in os.listdir("."):
    if f.startswith(("ck.db", "ckcopy")):
        os.remove(f)

def count(path):
    c = sqlite3.connect(path)
    try:
        return c.execute("SELECT count(*) FROM events").fetchone()[0]
    finally:
        c.close()

w = sqlite3.connect("ck.db", isolation_level=None)
w.execute("PRAGMA journal_mode=WAL")
w.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, note TEXT)")
w.execute("INSERT INTO events (note) VALUES ('first batch')")
w.execute("PRAGMA wal_checkpoint(TRUNCATE)")      # the table + 1 row now live in ck.db

# a second connection opens a read transaction and keeps it open
r = sqlite3.connect("ck.db", isolation_level=None)
r.execute("BEGIN")
print("reader sees:", r.execute("SELECT count(*) FROM events").fetchone()[0])

w.executemany("INSERT INTO events (note) VALUES (?)",
              [("later %d" % i,) for i in range(200)])
print("writer sees:", w.execute("SELECT count(*) FROM events").fetchone()[0])

print("checkpoint while reader open (busy, log, checkpointed):",
      w.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone())
shutil.copy("ck.db", "ckcopy_during.db")
print("main-file copy after that checkpoint:", count("ckcopy_during.db"))

r.execute("COMMIT")
print("checkpoint after reader ends (busy, log, checkpointed):",
      w.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone())
shutil.copy("ck.db", "ckcopy_after.db")
print("main-file copy after a clean checkpoint:", count("ckcopy_after.db"))
w.close(); r.close()

Output:

reader sees: 1
writer sees: 201
checkpoint while reader open (busy, log, checkpointed): (1, 200, 0)
main-file copy after that checkpoint: 1
checkpoint after reader ends (busy, log, checkpointed): (0, 0, 0)
main-file copy after a clean checkpoint: 201

PRAGMA wal_checkpoint returns three numbers: whether it was blocked, how many frames are in the log, and how many it moved into the database. With the reader open, it reported blocked, with every frame still waiting in the log and none copied across. The main-file copy taken right after held only the first row.

The WAL page explains why the checkpointer backs off: "the checkpoint must stop when it reaches a page in the WAL that is past the end mark of any current reader." Once the reader committed, the same pragma ran to completion and a plain copy had every row.

So a checkpoint-then-copy script is correct only if it checks the pragma's result and refuses to copy when the first number is not zero.

Test three: let SQLite make the copy

Both safe options are already in Python's standard library. Connection.backup() wraps SQLite's online backup API, and VACUUM INTO writes a compacted copy of the current state to a new file. SQLite's corruption page lists both as approaches that "will work even on a live database."

safe_backup.py wraps the first one, switches the copy to the default rollback journal so it is a single file with no sidecars, and runs PRAGMA integrity_check before declaring success.

import sqlite3

def safe_backup(src_path, dst_path):
    """Copy a live SQLite database into one self-contained file, then check it."""
    src = sqlite3.connect(src_path)
    dst = sqlite3.connect(dst_path)
    try:
        src.backup(dst)
        dst.execute("PRAGMA journal_mode=DELETE")
        result = dst.execute("PRAGMA integrity_check").fetchone()[0]
        if result != "ok":
            raise RuntimeError("backup failed integrity_check: %s" % result)
    finally:
        dst.close()
        src.close()

live_backup_demo.py keeps a writer connected the whole time and makes one copy each way.

import os, sqlite3
from safe_backup import safe_backup

for f in os.listdir("."):
    if f.startswith(("live.db", "nightly", "vacuumed")):
        os.remove(f)

writer = sqlite3.connect("live.db")
writer.execute("PRAGMA journal_mode=WAL")
writer.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, note TEXT)")
writer.executemany("INSERT INTO events (note) VALUES (?)",
                   [("event %d" % i,) for i in range(300)])
writer.commit()
print("live files:", sorted(f for f in os.listdir(".") if f.startswith("live.db")))

safe_backup("live.db", "nightly.db")
writer.execute("VACUUM INTO 'vacuumed.db'")

for name in ("nightly.db", "vacuumed.db"):
    c = sqlite3.connect(name)
    rows = c.execute("SELECT count(*) FROM events").fetchone()[0]
    mode = c.execute("PRAGMA journal_mode").fetchone()[0]
    c.close()
    extra = [f for f in os.listdir(".") if f.startswith(name + "-")]
    print("%s: %d rows, journal_mode=%s, side files=%s" % (name, rows, mode, extra))
writer.close()

Output:

live files: ['live.db', 'live.db-shm', 'live.db-wal']
nightly.db: 300 rows, journal_mode=delete, side files=[]
vacuumed.db: 300 rows, journal_mode=delete, side files=[]

Both copies had every row while the live database still had its -wal and -shm files on disk, and neither copy came with side files of its own. That last part matters for whatever moves the backup afterward: a single file cannot be split from its log, because it has none.

One difference: VACUUM INTO rebuilds the database, so its copy is not byte-identical to the source. Connection.backup() copies pages and accepts optional pages and progress arguments for large files.

What to do instead

• Do not copy a SQLite file with cp, shutil.copy, or a sync tool while anything might have it open. Use Connection.backup() or VACUUM INTO and copy the result.
• Write the backup to a new path, run PRAGMA integrity_check on it, then move it into place.
• Count rows in one or two key tables in both the source and the copy, and fail loudly if the copy has fewer than you expect.
• If a checkpoint-then-copy step has to stay, read the pragma's result and stop when the first value is not zero.
• Test the backup with the app running, and once more after killing it, not only after a clean stop.

Things I did not test

• Concurrent writers during the backup. The writer in these tests was idle while each copy ran. I did not measure how Connection.backup() behaves under steady writes, or how long it takes to finish on a busy database.
• Other SQLite versions. Only 3.37.2 was tested. The WAL page also documents a rare corruption bug, the WAL-reset bug, present from 3.7.0 through 3.51.2 and fixed in 3.51.3. It involves two connections writing or checkpointing at the same instant, not copying, and these tests do not exercise it.
• Android, network drives and cloud-sync folders. Everything ran on a local Linux disk. The WAL page states that WAL "does not work over a network filesystem". That is a separate problem from backups and was not tested here.
• The sqlite3_rsync utility. SQLite's corruption page lists it as a third safe option for copying a live database over SSH. It was not available in this environment.

Sources. Quotes are from SQLite's Write-Ahead Logging page and its page on how to corrupt an SQLite database file. Test results come from the scripts above, each run once in the order shown, with the output pasted unedited.

If you want an old phone to record clips in the background so there is something on disk worth backing up, the app I make is Background Camera RemoteStream on Google Play. Nothing above depends on it.

If ls shows an app.db-wal next to app.db, a plain copy of app.db is missing whatever that log still holds.