SQLite Basics: Back Up a Database with Python and Verify the Copy

A database backup should be opened and checked, not merely counted as a file. This local exercise copies a committed SQLite database through its backup API, changes the source afterward, and verifies the saved rows using a fresh connection.

Prepare a scratch directory

Tested on Linux with CPython 3.12.14 and SQLite 3.53.1. You need Python with SQLite support, Bash, a text editor, and standard mktemp, rm, and rmdir utilities. No third-party Python packages are required. Run Python without -O so the checks remain enabled.

Keep the same terminal open. Stop if setup fails, before saving the source:

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Run the example

Save this as demonstrate-backup-api.py:

from contextlib import closing
from pathlib import Path
import sqlite3
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    source_path = Path(directory) / "source.db"
    backup_path = Path(directory) / "backup.db"
    with closing(sqlite3.connect(source_path)) as source:
        source.execute("CREATE TABLE tasks (id INTEGER PRIMARY KEY, label TEXT)")
        source.executemany(
            "INSERT INTO tasks VALUES (?, ?)", [(1, "draft"), (2, "review")]
        )
        source.commit()
        assert not source.in_transaction
        assert not backup_path.exists()
        with closing(sqlite3.connect(backup_path)) as destination:
            source.backup(destination, pages=1)

        source.execute("INSERT INTO tasks VALUES (?, ?)", (3, "publish"))
        source.commit()
        print("Source rows:", source.execute("SELECT count(*) FROM tasks").fetchone()[0])

    with closing(sqlite3.connect(backup_path)) as restored:
        rows = restored.execute("SELECT id, label FROM tasks ORDER BY id").fetchall()
        assert rows == [(1, "draft"), (2, "review")]
        check = restored.execute("PRAGMA integrity_check").fetchone()[0]
        assert check == "ok"
        print("Backup rows:", len(rows))
        print("Backup labels:", ", ".join(label for _, label in rows))
        print("Integrity:", check)

Run it:

python3 -B demonstrate-backup-api.py

Observed output:

Source rows: 3
Backup rows: 2
Backup labels: draft, review
Integrity: ok

Read the result

The backup contains the two rows committed before copying. The third row is visible only in the source. Reopening the backup proves that the verification reads its saved database rather than accidentally querying the original connection.

Apply it carefully

Use a new destination you intend to populate: the backup API replaces destination content. Commit or roll back pending writes on the source connection before this pattern. The pages argument controls each copying step; the Python call still waits for completion. This fixture does not test competing writers, storage failure, or retention policy. An integrity check verifies database structure, while the row assertions verify this small application fixture. Production backups also need protected storage and an actual restore rehearsal. Do not copy only a live database file with ordinary file-copy tools while ignoring its journal or WAL state.

The official reference documents the API behavior. These checks exercise the controlled fixture above.

Clean up

Remove the saved source, return to the starting directory, then remove the empty practice directory:

rm -- demonstrate-backup-api.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

If the directory is not empty, inspect its contents before removing anything else.

Acknowledgements

This post was created with assistance from AI.