SQLite WAL Mode: The 3 Files You Must Back Up Together

If you enable WAL (write-ahead log) mode in SQLite — and you should for most web services — your database stops being one file. It becomes three:

app.db      # the main database (committed pages)
app.db-wal  # pending writes not yet merged back
app.db-shm  # shared-memory index (rebuilt automatically)

Copying only app.db while a -wal file exists silently drops every write that has not been checkpointed yet. The backup looks fine; the newest data is missing. This is the most common SQLite backup mistake I see.

What WAL actually does

WAL lets readers and a writer proceed without blocking each other: new writes go to the -wal file first, and only get merged into the main file at a checkpoint. A checkpoint happens automatically when the WAL reaches a size threshold or the database closes cleanly.

The result: the main .db file on disk is often behind the truth. The -wal holds the recent commits.

How to back up safely

Two reliable options:

Option A — copy all three files together (only while the service is stopped, or accept a small window):

cp app.db app.db-wal app.db-shm /backup/

Option B — take a consistent snapshot while running (best):

VACUUM INTO 'backup.db';

VACUUM INTO reads the database and writes a fresh, consistent copy to a new file — safe to run while the service is up, and it also compacts the file. For a SQLite service, this is the backup command I reach for first.

The discipline that matters

• Back up all three files, or use VACUUM INTO.
• After an unclean shutdown, run PRAGMA wal_checkpoint or let SQLite recover the WAL on next open — never delete the -wal file by hand.
• Test a restore on a schedule. A backup you never restored is a wish.

Sizing a database before you build it? The estimator below gives you a number instead of a guess.