Back Up SQLite with Python: WAL Snapshots and Restore Checks

A batch job stores progress in SQLite. You copy its .sqlite file, restore it, and discover that recent committed records are missing. In WAL mode, those records may still be in the -wal file. This tutorial uses Python’s backup API to produce a standalone snapshot, then copies it into a fresh directory and queries it.

This beginner-to-intermediate tutorial follows SQLite checkpoints for batch jobs. The demo uses an invented database and needs no third-party Python packages.

1. Method: back up through a SQLite connection

The SQLite WAL documentation explains that WAL belongs to the database’s persistent state. Copying just the main file may miss committed data. Copying a live database and its journal files one at a time also allows their state to change between copies.

Use Python Connection.backup() to let SQLite read the database state. This example backs up only main, excluding other ATTACH databases and external output files. Use a trusted, writable local directory on a filesystem that supports hard links.

The sequence is: back up into a sibling temporary directory → change the backup’s journal mode → check its structure → close connections → create the final filename. The source opens with mode=ro and receives no write SQL. An existing destination is rejected.

2. Setup

Use Python 3.10 or newer with the standard sqlite3 module. The examples were tested on Linux with Python 3.11.4. In a fresh working directory, download backup_sqlite.py, demo_wal.py, and verify_backup.py, then run:

python3 --version
python3 -c 'import sqlite3; print(sqlite3.sqlite_version)'
python3 backup_sqlite.py --help

The second command reports SQLite as linked by your Python build. Concurrent WAL applications should use a build containing the official fixes. SQLite’s WAL-reset fix notes list patched releases including 3.51.3, 3.50.7, and 3.44.6; distributions may also backport patches. This demo does not run writes and checkpoints concurrently.

3. Complete backup script

Here is backup_sqlite.py:

"""Back up one SQLite database without overwriting a destination. Python 3.10+."""
import argparse
from contextlib import closing
import os
from pathlib import Path
import sqlite3
import sys
import tempfile


def backup_database(source, destination):
    source = Path(source).resolve(strict=True)
    destination = Path(destination).absolute()
    if not source.is_file():
        raise ValueError("source must be a regular file")
    if os.path.lexists(destination):
        raise FileExistsError(f"destination already exists: {destination}")
    if not destination.parent.is_dir():
        raise ValueError("destination parent directory must exist")

    # A sibling temporary directory keeps the hard link on one filesystem.
    with tempfile.TemporaryDirectory(prefix=".sqlite-backup-", dir=destination.parent) as work:
        temporary = Path(work) / "backup.sqlite"
        with closing(sqlite3.connect(source.as_uri() + "?mode=ro", uri=True,
                                     isolation_level=None)) as src:
            with closing(sqlite3.connect(temporary, isolation_level=None)) as dst:
                src.backup(dst, pages=256, sleep=0.1)
                # Make the result a standalone file even when the source uses WAL.
                mode = dst.execute("PRAGMA journal_mode=DELETE").fetchone()[0]
                if mode.lower() != "delete":
                    raise ValueError("could not select DELETE journal mode")
                checks = dst.execute("PRAGMA quick_check").fetchall()
                if checks != [("ok",)]:
                    raise ValueError(f"quick_check failed: {checks}")
        os.chmod(temporary, 0o600)
        # Unlike replace(), link() fails if another process created the destination.
        os.link(temporary, destination)
    return destination


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("source", type=Path)
    parser.add_argument("destination", type=Path)
    args = parser.parse_args()
    try:
        destination = backup_database(args.source, args.destination)
    except (OSError, ValueError, sqlite3.Error) as exc:
        print(f"ERROR: {exc}", file=sys.stderr)
        return 1
    except KeyboardInterrupt:
        print("INTERRUPTED", file=sys.stderr)
        return 130
    print(f"BACKUP OK: {destination}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

resolve(strict=True) and the read-only URI prevent a misspelled source from becoming a new database. isolation_level=None avoids implicitly opening write transactions here and works on Python 3.10/3.11.

os.link() creates the final name for the completed temporary file. If another process creates that destination first, the link fails without overwriting it. Both names reside on the same filesystem; the temporary name is cleaned up afterward. The backup has permissions 0600, allowing only its owner to read or write it on Linux. This workflow has not been tested for power-loss durability.

4. Build a demo with real WAL data

Save this as demo_wal.py. It keeps the source connection open, commits two rows, invokes the backup in a separate process, and inserts a third row only after the backup finishes.

"""Keep committed rows in WAL while a separate backup process runs."""
from pathlib import Path
import sqlite3
import subprocess
import sys

folder = Path("sqlite-backup-demo")
folder.mkdir(exist_ok=True)
source = folder / "live.sqlite"
if source.exists():
    raise SystemExit("Use a fresh demo directory")
con = sqlite3.connect(source, isolation_level=None)
try:
    assert con.execute("PRAGMA journal_mode=WAL").fetchone()[0] == "wal"
    con.execute("PRAGMA wal_autocheckpoint=0")
    con.execute("CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT NOT NULL)")
    con.executemany("INSERT INTO jobs VALUES (?, ?)", [(1, "done"), (2, "done")])
    assert Path(str(source) + "-wal").stat().st_size > 0
    print("WAL has committed data", flush=True)
    subprocess.run([sys.executable, "backup_sqlite.py", str(source),
                    str(folder / "snapshot.sqlite")], check=True)
    con.execute("INSERT INTO jobs VALUES (?, ?)", (3, "later"))
    print("LIVE rows=3; snapshot should still have 2", flush=True)
finally:
    con.close()

wal_autocheckpoint=0 is for this demo, keeping automatic checkpoints from clearing the small sample’s WAL early. Do not copy it into a production job; production needs its own checkpoint policy.

5. Verify an independent restore and query

Save this as verify_backup.py. It copies only the backup into a new temporary directory containing none of the source’s -wal or -shm files.

"""Read the backup in a fresh directory without the source or its WAL files."""
from contextlib import closing
from pathlib import Path
import shutil
import sqlite3
import tempfile

backup = Path("sqlite-backup-demo/snapshot.sqlite").resolve(strict=True)
with tempfile.TemporaryDirectory(prefix="sqlite-restore-") as work:
    restored = Path(work) / "restored.sqlite"
    shutil.copyfile(backup, restored)
    with closing(sqlite3.connect(restored.as_uri() + "?mode=ro", uri=True,
                                 isolation_level=None)) as con:
        assert con.execute("PRAGMA quick_check").fetchall() == [("ok",)]
        assert con.execute("PRAGMA journal_mode").fetchone()[0] == "delete"
        rows = con.execute("SELECT id, status FROM jobs ORDER BY id").fetchall()
        assert rows == [(1, "done"), (2, "done")], rows
print("RESTORE OK: [(1, 'done'), (2, 'done')]")

Run:

python3 demo_wal.py
python3 verify_backup.py

Expected output, with the absolute path depending on your working directory:

WAL has committed data
BACKUP OK: /your/workdir/sqlite-backup-demo/snapshot.sqlite
LIVE rows=3; snapshot should still have 2
RESTORE OK: [(1, 'done'), (2, 'done')]

The snapshot contains IDs 1 and 2. ID 3, inserted later into the source, does not enter an old snapshot. PRAGMA quick_check is a fast structural check; it does not check all index consistency or business correctness. Use integrity_check for broader structural checks, and verify key records, counts, and business constraints during a real restore.

6. Check overwrite refusal, then use it for your jobs

Back up to the same path again. Check the nonzero status and that the old snapshot remains readable:

python3 backup_sqlite.py sqlite-backup-demo/live.sqlite sqlite-backup-demo/snapshot.sqlite
printf 'exit=%s\n' "$?"
python3 verify_backup.py

Expect destination already exists, exit=1, and RESTORE OK. Choose a new filename instead of deleting the existing backup to bypass this check.

Generate a new name with a timestamp:

mkdir -p backups
python3 backup_sqlite.py sqlite-backup-demo/live.sqlite "backups/progress-$(date +%Y%m%d-%H%M%S).sqlite"

This makes another snapshot of the demo source, which now contains the third row. The two-row assertions above apply only to the first demo snapshot. Change the verification query for your own database. Two invocations within the same second can still collide; the script fails safely in that case.

For scheduled runs, follow the Python path and working-directory guide, use absolute paths, and check exit status. Arrange retention, off-host copies, and restore drills separately. Copies on the same disk do not cover disk failure.

7. Troubleshooting

Symptom	Concrete fix
Missing source	Check the name with `ls -l /actual/path/progress.sqlite`; the script does not create an empty source.
`destination parent directory must exist`	Run `mkdir -p backups`, then choose a new filename inside it.
`Permission denied`	Check the running user and directory permissions with `ls -ld backups`. A read-only WAL connection also needs suitable side-file or directory permissions; see SQLite’s read-only WAL conditions.
`Operation not supported`	Choose a local backup directory supporting hard links. Failure leaves the final filename unpublished.
`database is locked` or a long-running backup	Avoid long transactions or heavy write periods; test the demo first. `sleep=0.1` is a retry interval, not an overall timeout.
`No space left on device`	Check `df -h backups`, follow the disk-full troubleshooting guide, then create a new backup.

The SQLite backup API explanation describes how frequent writes can restart a backup repeatedly. Schedule large backups for a suitable window. This example does not promise a snapshot of the exact invocation instant or impose an overall backup deadline.

8. Summary

Back up through SQLite, finish the checks, and publish a new filename to avoid overwriting an existing copy. Start with the WAL demo, then adapt the independent restore query to your important business data. Keep earlier snapshots and practice restoring them.