Backing Up a Live SQLite WAL Database: A Practical Developer Guide

A tested toy example, the backup API and the checks a real recovery plan still needs.

Use SQLite's own backup interface to create a separate database file, then reopen and validate the copy. In WAL mode, committed changes can still be in the write-ahead log, so copying only the main .db file from a running service can miss data. The example below was executed against an isolated toy database; it never opens an existing application or customer database.

Choose a database-aware copy
Run an example that cannot accept a live database path
What the local execution actually showed
Turn a copied file into a recovery procedure
Handle busy writers and checkpoints separately

Choose a database-aware copy

Python exposes SQLite's Online Backup API as source.backup(destination). The source and destination are connections, with a different destination file. SQLite coordinates the reads and locking. Incremental backups can release the source between batches, while writes from other connections can cause work to restart. Schedule and monitor a busy database's backup; there is no universal duration.

WAL mode records changes in a separate log before checkpointing them into the main database. A checkpoint and a backup answer different questions: a checkpoint transfers WAL content to the database, whereas a backup creates a separately retained copy. Manually copying .db, -wal and -shm at unrelated moments does not establish a common snapshot. Do not delete a live WAL to make the file set easier to copy. See the SQLite WAL explanation and online backup guide.

Two documented live-backup approaches

Approach	Useful distinction	Check before using it
Online Backup API / Python `Connection.backup()`	Can copy incrementally; the destination is a separate SQLite connection.	Destination ownership, exceptions, write contention, completion and validation.
`VACUUM INTO`	Creates a compacted copy with the same logical content, rather than a byte-identical file.	The destination must not exist or must be empty; an open transaction can prevent the command. Interrupted output can be incomplete.

These tradeoffs come from the VACUUM documentation. Neither method turns a single local copy into off-site retention, continuous replication or a complete disaster-recovery system.

Run an example that cannot accept a live database path

The complete Python example accepts no filename arguments. It creates its own temporary directory, inserts three invented rows, commits them in WAL mode, keeps the source open during backup(), and opens the resulting file through a new connection. It compares every toy row and runs PRAGMA integrity_check. Its cleanup applies only to that temporary directory.

# The complete downloadable example creates and owns both temporary files.
with closing(sqlite3.connect(backup_path)) as destination:
    source.backup(destination)
with closing(sqlite3.connect(backup_path)) as restored:
    actual = restored.execute(
        "SELECT id, value FROM sample ORDER BY id"
    ).fetchall()
    integrity = restored.execute("PRAGMA integrity_check").fetchall()
    if actual != expected or integrity != [("ok",)]:
        raise RuntimeError("Toy backup verification failed")

This excerpt is not a standalone production command: source, the paths and expected are initialized in the complete example. The file uses contextlib.closing to close connections explicitly; a SQLite connection's transaction context manager alone does not close the connection.

What the local execution actually showed

On October 23, 2026, Python 3.12.14 with SQLite 3.53.4 returned WAL mode, confirmed that the toy WAL existed during backup, recovered all three expected rows and returned ok from the integrity check. The execution record and code hash make the scope reproducible.

This is a small functional demonstration. It does not simulate a competing writer, interrupted power, disk failure, a large database or recovery of a deployed application. It also does not test a filesystem-copy procedure. A matching row count alone would miss changed values; that is why this toy compares the actual rows as well.

Turn a copied file into a recovery procedure

Observe successful completion and keep incomplete artifacts separate from accepted backups.
Reopen the destination independently. Check application-relevant data and schema, not only file existence or size.
Use documented integrity checks. SQLite notes that integrity_check does not find foreign-key violations; use foreign_key_check for those when relevant.
Rehearse a restore in an isolated environment with the application's configuration and access requirements. Do not overwrite the live database as a test.
Define retention, protected storage, encryption and an off-site failure boundary appropriate to the application.

The PRAGMA documentation describes what those checks cover. A valid SQLite file cannot prove that an application's required external assets, encryption keys or other services are recoverable.

Handle busy writers and checkpoints separately

Read the Python backup method documentation before choosing page counts, progress reporting or sleep behavior. The C examples explain locking and restarts, but are not a Python workload benchmark. A quiet backup window may help a repeatedly changing source; measure completion in your own workload.

Replication continuously propagates changes according to its own design. A backup is a retained recovery artifact. A WAL checkpoint is database maintenance. Combining those terms can hide a missing recovery copy, so document the artifact, its point in time and how it is restored.

Generated with Ithy Pro; AI-assisted editorial and primary-source checks completed October 23, 2026. The three independent model perspectives are retained with corrections. Original examples and test limits are identified.

Primary references

Compare the AI responses

Read each model’s complete response below.

GPT-6 Luna

Editorial source check: October 23, 2026. This independent research perspective is retained with factual corrections; the checked main article adds current primary references and the stated examples.

For live WAL databases, use SQLite-aware copying, keep a separate destination and validate the result. The checked article above adds a locally executed toy backup with explicit connection closing, complete row comparison and an integrity check. It does not demonstrate competing writers, crash recovery or customer-database restoration.

Use Python’s standard-library `sqlite3.Connection.backup()` to copy a database into a separate SQLite connection and file. Python’s documentation shows the method and says it was added in Python 3.7. SQLite describes online backup as copying a running database; its example copies pages in batches, allowing the source database to be unlocked between batches. This is a backup operation, not replication: the evidence describes a copy, not a continuously maintained replica. [1.5] [2.1]
SQLite WAL commits changes to the separate write-ahead log before checkpointing them into the main database. A main-file-only live copy may therefore omit committed content. Collecting companion files at unrelated moments does not establish one snapshot. Use the database-aware backup interface rather than deleting a WAL or assuming filenames establish consistency.
An online backup can interact with concurrent writes. SQLite documents that its batched example releases the source read lock between batches, allowing other connections to write; if a write happens while the backup sleeps, SQLite usually restarts the backup process on the next step. This is not a promise that every workload completes without delay or contention, so account for busy writers in operational planning. [1.2] [1.3]
SQLite documents VACUUM INTO as a compacted copy with the same logical content. The destination must be absent or empty, and an interrupted operation can leave incomplete output. The backup API can work incrementally; choose from the complete documentation and your recovery requirements.
A checkpoint transfers WAL content into the database; a backup creates a separately retained copy. Neither a checkpoint nor one backup call establishes continuous replication. Keep artifact creation, retention and a restore rehearsal distinct.
Example (toy data only). This code creates a temporary directory and removes only that directory and its contents. It leaves unrelated files alone. It calls Python’s documented backup method, then checks that the copied table has the expected row count. It is an illustrative example, not a claim that it was executed or tested on a customer database. [2.1] [2.4]
```python import sqlite3 import tempfile from pathlib import Path from contextlib import closing with tempfile.TemporaryDirectory(prefix="sqlite-backup-toy-") as tmp: root = Path(tmp) source_path = root / "toy.db" backup_path = root / "toy-backup.db" with closing(sqlite3.connect(source_path)) as src: src.execute("PRAGMA journal_mode=WAL") src.execute("CREATE TABLE sample (value TEXT)") src.executemany( "INSERT INTO sample(value) VALUES (?)", [("toy-one",), ("toy-two",)], ) src.commit() with closing(sqlite3.connect(backup_path)) as dst: src.backup(dst) count = dst.execute("SELECT count(*) FROM sample").fetchone()[0] assert count == 2, f"unexpected toy row count: {count}" # TemporaryDirectory cleans up only this example's directory. ``` [2.1] [2.4]
A row-count check is narrower than checking values, schema and application behavior. SQLite integrity_check has documented structural coverage and does not find foreign-key violations. Validate the copy and rehearse a restore; no one successful check guarantees every failure mode.

Claude Haiku 5.5

Editorial source check: October 23, 2026. This independent research perspective is retained with factual corrections; the checked main article adds current primary references and the stated examples.

In WAL mode, use a database-aware backup so committed changes in the WAL are included in a separate copy. A checkpoint, a backup and continuous replication are distinct operations. Python exposes the Online Backup API through Connection.backup; VACUUM INTO is another documented live-copy option. The checked main article adds a tested disposable example with explicit connection closing, complete row checks and an integrity check, while leaving production and concurrent-write behavior untested.

The SQLite WAL documentation describes that committed changes in WAL mode are written to a separate write-ahead log file, so the main database file alone does not always contain all committed content until a checkpoint occurs. [10.1]
The SQLite Backup API documentation states that the online backup process copies pages and, when the source database is changed by another connection during the copy, SQLite detects this and usually restarts the backup step, which lets other threads and connections keep writing to the source. [1.3] [1.4]
The SQLite Backup API documentation contrasts a single-call copy, which holds a read-lock on the source for the entire operation and blocks other writers, with a stepwise copy that releases the lock between steps. [1.2] [1.1]
The Python sqlite3 documentation shows connection.backup(target) copying a source database to a target connection, and this method has been available since Python 3.7. [2.1]
The SQLite WAL documentation notes the wal-index in shared memory means all readers must exist on the same machine, and the WAL implementation does not work on a network filesystem, which bears on copying or moving live WAL database files. [4.6]
The SQLite documentation says the only safe way to remove a WAL file is to open the database with sqlite3_open() and close it with sqlite3_close(), so manually deleting or manipulating -wal and -shm files is not a safe backup technique. [4.2]
The SQLite VACUUM documentation describes VACUUM INTO as an alternative to the backup API for generating backup copies of a live database, which can produce a single compacted output file. [9.1]
Practical guidance: prefer connection.backup() or VACUUM INTO over file copying, verify the backup by opening it and checking row counts or PRAGMA integrity_check, keep backup, checkpoint and replication as separate concerns, and test restores. These steps are reasoned recommendations, not guarantees that backups never fail.

The toy execution does not measure heavy-write contention or timeouts. Check the documented backup parameters and observe completion under your own workload before deciding scheduling and failure handling.

One successful toy copy is not evidence of crash-proof recovery under every failure mode. Keep validation, isolated restore rehearsal, retention and storage-failure boundaries in the operational plan.

Claude Sonnet 5.5

Editorial source check: October 23, 2026. This independent research perspective is retained with factual corrections; the checked main article adds current primary references and the stated examples.

Python documents sqlite3.Connection.backup(target), available since Python 3.7, as a connection-to-connection copy operation. The complete example above opens a WAL source and a separate file-backed destination, and explicitly closes both connections.
SQLite WAL commits changes to the separate write-ahead log before checkpointing them into the main database. A main-file-only live copy may therefore omit committed content. Collecting companion files at unrelated moments does not establish one snapshot. Use the database-aware backup interface rather than deleting a WAL or assuming filenames establish consistency.
SQLite says the only safe way to remove a WAL file is to open the database with sqlite3_open and immediately close it with sqlite3_close. Treat manual file handling around -wal as hazardous. The excerpt does not directly prove that every live copy of .db, -wal and -shm is inconsistent, so present that point cautiously. [4.2]
The backup API trades locking against restarts. Copying the whole source in one sqlite3_backup_step call requires a read lock for the whole operation and prevents other users from writing. The stepwise example copies 5 pages, releases locks, sleeps, and repeats. If another thread or process writes during the sleep, SQLite usually restarts the backup when the next step is called. With busy writers, this means a backup may take longer or restart; do not promise completion time. [1.2] [1.3] [1.5] [1.6]
SQLite documents VACUUM INTO as a compacted copy with the same logical content. The destination must be absent or empty, and an interrupted operation can leave incomplete output. The backup API can work incrementally; choose from the complete documentation and your recovery requirements.
A checkpoint transfers WAL content into the database; a backup creates a separately retained copy. Neither a checkpoint nor one backup call establishes continuous replication. Keep artifact creation, retention and a restore rehearsal distinct.
The checked Python example uses tempfile.TemporaryDirectory and contextlib.closing so that its own connections are closed before its own temporary files are removed. SQLite connection context managers manage transactions, not automatic connection closing. The complete downloadable code and actual toy execution record appear above.