Online Backup API & Hot Copies #

Copying a busy SQLite database while it is being written is the operation most teams get wrong first. The instinct — cp app.db backup.db, or shutil.copy2, or a filesystem snapshot — produces a file that opens without complaint and passes casual inspection, yet is silently torn: the copy captures the main database file at one instant and misses the committed frames still living in the -wal file, or captures a page mid-write. On restore you get SQLITE_CORRUPT or, worse, a database that reads back plausible-but-wrong rows. The online Backup API exists precisely to avoid this. It walks the source database page by page under the source’s own locking rules, so the target is a transactionally consistent image even though writers never stopped. This topic sits inside the Backup, Recovery & Data Integrity guide and assumes the source is already running in Write-Ahead Logging mode with a hardened busy_timeout in place. Here we cover exactly how to drive sqlite3.Connection.backup(), how to throttle it so it never starves the write path, and how to prove the copy is sound before you trust it.

Figure — A filesystem copy of a WAL-mode database captures the main file but misses the -wal frames and can tear a mid-write page; the Backup API reads a consistent page-by-page image while writers continue.

Core Mechanism & Crash-Safety Defaults #

The Backup API is a small state machine exposed in Python as sqlite3.Connection.backup(target, *, pages=-1, progress=None, name="main", sleep=0.250). It opens a backup handle between a source connection and a target connection, then copies database pages from source to target in steps. Each call to the underlying sqlite3_backup_step() copies up to pages pages; passing pages=-1 copies everything in a single step, while a positive value copies that many pages and returns, letting you loop. Between steps the source database is unlocked, so writers can commit. This is the entire reason the API is safe to run hot: it never holds a long read lock, and it tracks writes that land on already-copied pages so the final image is consistent as of the moment the copy completes.

The crash-safety guarantee is subtle and worth stating precisely. The Backup API does not snapshot the source at the instant you start. Instead, if any process writes to the source database while a backup is in progress, SQLite detects that the source page-count or content changed under it and automatically restarts the copy from the beginning on the next step. The target you finally get is consistent as of the last restart, not as of when you called backup(). On a continuously written database this restart behaviour is the dominant performance concern — a backup that keeps restarting never finishes — and it is the reason throttling and window selection matter so much. A backup interrupted by a crash simply leaves a partial, unusable target file; the source is never endangered, because the API only ever reads the source.

Two defaults must be respected regardless of how you tune the copy. The source connection’s PRAGMA synchronous and PRAGMA journal_mode are irrelevant to the source’s safety during backup (it is read-only from the API’s perspective), but the target connection controls how durably the copy lands on disk. Leave the target at synchronous=NORMAL or FULL so the backup file is itself crash-safe once written; setting the target to OFF to speed the copy defeats the purpose of making a backup at all. And because the API copies raw pages, the target inherits the source’s page_size exactly — you cannot change page size through a backup.

Step-by-Step Implementation #

1. Verify WAL Mode & Checkpoint State Prerequisites #

Before copying anything, confirm the source is in a state where a hot copy is meaningful. A hot backup is only worth doing on a database with concurrent writers, which in practice means WAL mode; in rollback-journal mode a reader already blocks writers, so a plain copy under a read transaction would do. Confirm the mode and inspect how much uncheckpointed data is sitting in the WAL, because a very large WAL lengthens the copy and widens the restart window:

PRAGMA journal_mode = WAL;         -- required: lets writers proceed while the backup reads
PRAGMA busy_timeout = 5000;        -- ms; the backup handle waits instead of failing on a busy source
PRAGMA wal_checkpoint(PASSIVE);    -- returns (busy, log_pages, checkpointed_pages); shrinks the copy footprint
PRAGMA page_size;                  -- pages copied are this many bytes each; needed to estimate copy time
PRAGMA page_count;                 -- total pages to copy; multiply by page_size for the target's size

Treat any journal_mode other than wal as a signal to reconsider whether you need the online API at all. If wal_checkpoint(PASSIVE) shows a large log_pages value that will not shrink, a reader is pinning the WAL — resolve that first, because that same reader will keep the backup restarting. The checkpoint discipline that keeps this footprint small is covered in Checkpoint Frequency Tuning.

2. Choose Page Batch Size & Sleep Interval #

The two knobs that decide whether your backup finishes and how much it disturbs writers are pages (how many pages each step copies before yielding the source lock) and sleep (how long the driver waits between steps when the source is busy). A large pages value copies faster but holds the source read lock longer per step, injecting latency into the write path; a small pages value yields often and stays polite but stretches total wall-clock time and widens the window in which a write can trigger a restart. Pick a starting pair from the decision table below, then measure restart frequency and writer latency under real load.

The rule of thumb: pages × page_size is the I/O burst per step (e.g. 256 × 4096 ≈ 1 MB), and sleep is the recovery gap given to writers. On a database under sustained write pressure, favour small pages and larger sleep so the copy is a background trickle rather than a competing bulk reader — the same contention logic behind busy_timeout sizing.

3. Apply backup() With Error Handling & Verification #

A production backup routine copies in bounded steps, reports progress, and — non-negotiably — verifies the finished target with PRAGMA integrity_check before anything downstream treats it as a good copy. The routine below wires all three together and handles the error codes you will actually see: a busy source, a full target disk, and a restart in progress.

import sqlite3
import logging

logger = logging.getLogger("sqlite_hot_backup")


def hot_backup(src_path: str, dst_path: str, *, pages: int = 128,
               sleep_s: float = 0.10) -> None:
    src = sqlite3.connect(src_path, timeout=30.0)   # timeout in SECONDS: waits out a busy source
    dst = sqlite3.connect(dst_path, timeout=30.0)
    try:
        # A progress callback lets us log throughput and observe restarts. It receives
        # (status, remaining, total) after each copied batch; remaining rising back toward
        # total signals SQLite restarted the copy because the source was written.
        def _progress(status: int, remaining: int, total: int) -> None:
            done = total - remaining
            logger.info("backup %d/%d pages (%d%%)", done, total,
                        (done * 100 // total) if total else 100)

        # pages>0 + sleep throttles the copy: each step copies `pages` pages, then if the
        # source is locked the driver sleeps `sleep_s` before retrying, yielding to writers.
        src.backup(dst, pages=pages, progress=_progress, sleep=sleep_s)

        # The target must itself be durable; confirm it is a well-formed database.
        result = dst.execute("PRAGMA integrity_check;").fetchone()[0]
        if result != "ok":
            raise RuntimeError(f"backup integrity_check failed: {result!r}")

        # Read back a cheap invariant to prove the copy is queryable, not just well-formed.
        page_count = dst.execute("PRAGMA page_count;").fetchone()[0]
        if page_count <= 0:
            raise RuntimeError(f"backup produced an empty database: page_count={page_count}")

        logger.info("hot backup verified: %s (%d pages)", dst_path, page_count)
    except sqlite3.OperationalError as e:
        # SQLITE_BUSY (source locked past the timeout) and SQLITE_FULL (target disk full)
        # both surface here; neither leaves the source touched.
        logger.error("backup failed (busy/full): %s", e)
        raise
    except sqlite3.Error as e:
        logger.error("backup failed: %s", e)
        raise
    finally:
        dst.close()
        src.close()

The progress callback is doing double duty: it is your throughput log and your restart detector. If remaining stops falling monotonically and jumps back up toward total, SQLite restarted the copy because a writer changed the source — the signature you will diagnose in the failure section below.

Workload Profiles & Threshold Reference #

The correct copy cadence and target location depend on the medium and the write load. These bands are field-tested starting points; measure restart count and writer latency, then adjust.

Where you run the backup matters as much as how. Run it from a dedicated connection (never one that is also serving writes), write the target to storage that does not share an I/O queue with the source, and schedule it against the same low-traffic windows you use for checkpointing. The narrow case of streaming the copy off a constrained device over a slow link is developed in Streaming Hot Backups from Edge Devices, and driving the copy across many short windows in Incremental Backups with the SQLite Backup API.

Failure Documentation & Edge Cases #

Backup Restarted by Concurrent Writes #

Trigger: A process commits to the source while the backup is mid-copy. SQLite detects the change and silently restarts the page copy from zero on the next step. Under sustained writes the copy can restart indefinitely and never complete.

Diagnosis: Watch the progress callback. Monotonically falling remaining is healthy; remaining jumping back toward total is a restart:

# In the callback, flag a restart when remaining grows instead of shrinking.
if remaining > _progress.last:            # attribute seeded to total before the run
    logger.warning("backup restarted at %d/%d pages", total - remaining, total)
_progress.last = remaining

Fallback: Reduce pages and raise sleep so writers finish between steps, or move the backup to a genuine low-traffic window. On an always-writing source, drive the copy incrementally across short quiet windows as described in Incremental Backups with the SQLite Backup API, and shorten reader lifetimes per Checkpoint Frequency Tuning.

Source Busy or Locked #

Trigger: Another connection holds a write lock the backup step needs, and the wait exceeds the source connection’s timeout, raising SQLITE_BUSY.

Diagnosis: The exception surfaces as sqlite3.OperationalError: database is locked. Confirm the source timeout and that a sleep is set so the driver yields between retries:

timeout = src.execute("PRAGMA busy_timeout;").fetchone()[0]  # ms the step waits before raising
assert timeout >= 5000, f"busy_timeout too low for a hot backup: {timeout} ms"

Fallback: Raise busy_timeout on the source connection and set a non-zero sleep on the backup() call so busy steps retry politely. The timeout-sizing method is in Busy Timeout Configuration.

Target Disk Full (SQLITE_FULL) #

Trigger: The target volume runs out of space mid-copy. The backup step raises SQLITE_FULL and leaves a partial, unusable target file.

Diagnosis: The error arrives as sqlite3.OperationalError with database or disk is full. Pre-flight the target free space against the source size:

import os, shutil
needed = os.path.getsize(src_path)                 # target will be about the source size
free = shutil.disk_usage(os.path.dirname(dst_path) or ".").free
assert free > needed * 1.2, f"insufficient space: need ~{needed} B, have {free} B"

Fallback: Write to a volume with headroom, delete the partial target on failure, and alert before the volume fills. Never write the backup to the same nearly-full partition as the source.

Reader Pinning the WAL #

Trigger: A long-lived read transaction on the source pins an old WAL snapshot, so checkpoints cannot truncate and the WAL — and therefore the copy footprint and restart window — keeps growing.

Diagnosis: A PASSIVE checkpoint reports a large log that never shrinks:

PRAGMA wal_checkpoint(PASSIVE);  -- checkpointed_pages stuck far below log_pages => a reader is pinning the WAL

Fallback: Enforce bounded read-transaction lifetimes so checkpoints can truncate before you back up, then reclaim with wal_checkpoint(TRUNCATE). The reader-lifecycle discipline is detailed in Checkpoint Frequency Tuning.

Production Hardening Checklist #

• Confirm the source is in wal mode before choosing the online API over a copy-under-read-transaction.
• Run the backup from a dedicated connection that is not also serving writes.
• Set pages and sleep from the workload table; never use pages=-1 on a busy source.
• Attach a progress callback and treat a rising remaining as a restart warning.
• Pre-flight target free space at ~1.2× the source size to avoid SQLITE_FULL.
• Raise busy_timeout on the source so busy steps wait instead of raising.
• Run PRAGMA integrity_check on the finished target and require ok before trusting it.
• Write the target to storage that does not share an I/O queue or flash region with the source.
• Delete partial target files on any failure so a torn copy is never mistaken for a good one.

Related Pages #

• Streaming Hot Backups from Edge Devices — throttled, resumable copies off a constrained device over a slow link.
• Incremental Backups with the SQLite Backup API — stepping the copy across many short windows without a long read lock.
• Integrity Checking & Corruption Recovery — verifying a copy and recovering when a source is already malformed.
• Checkpoint Frequency Tuning — keeping the WAL small so a hot copy stays fast and restart-free.
• Busy Timeout Configuration — sizing the wait so a busy source never fails the backup outright.

For authoritative detail on the copy semantics and restart behaviour, see the official SQLite Backup API documentation and the Python sqlite3 backup() reference.