Incremental Backups with the SQLite Backup API

There are two very different things an engineer can mean by “incremental backup,” and conflating them is the single biggest source of disappointment with the SQLite Backup API. One meaning is incremental stepping: copying a large database a few pages at a time, spread across many short windows, so no single operation holds a read lock long enough to disturb writers. The other meaning is delta or differential backup: capturing only the blocks that changed since the last backup, so a nightly run moves kilobytes instead of gigabytes. The Backup API gives you the first and explicitly does not give you the second — if the source is written during the copy, SQLite restarts the copy from the beginning, which is the opposite of a delta scheme. This page sets that expectation precisely and shows how to exploit what the API does offer: driving sqlite3_backup_step() with a bounded page budget per tick so a multi-gigabyte database is copied incrementally without ever taking a long lock. It is a companion to Online Backup API & Hot Copies within the Backup, Recovery & Data Integrity guide, and it assumes you have already read how pages, sleep, and the progress callback behave there.

Diagnosis

Reach for incremental stepping when a single-shot backup(dst, pages=-1) is unacceptable for one of two concrete reasons, and confirm which applies before writing the loop.

The first reason is lock-hold time. Copying a large database in one step reads every page while briefly holding the source lock per internal step; on a latency-sensitive service you want to guarantee that each tick of work is short and that the scheduler regains control between ticks. Measure how long a full copy takes and whether that duration ever coincides with a write you cannot delay. If a whole-copy step would run for seconds on your medium, budget the work into sub-second ticks.

The second reason is that the source is written often enough that a naive copy keeps restarting and never finishes. This is where expectation-setting matters. Query how large the database is and estimate copy time against your medium’s throughput, then compare to the interval between writes:

PRAGMA page_count;   -- total pages to copy
PRAGMA page_size;    -- bytes per page; page_count × page_size = bytes to move
PRAGMA journal_mode; -- expect 'wal'; a reader (the backup) then never blocks the writer

If page_count × page_size divided by your disk throughput exceeds the typical gap between commits, a single continuous copy will restart repeatedly. The incremental loop does not make restarts disappear — a mid-copy write still restarts the copy — but it lets you schedule ticks into quiet gaps and detect restarts cheaply, so you spend windows productively instead of thrashing.

Incremental stepping of the Backup API across short windows, with restart on a mid-copy write A loop of three states. Each tick copies a fixed page budget then yields; a scheduler waits for the next quiet window and steps again, advancing the copy. If a writer commits to the source during a tick, the machine restarts the copy from page zero rather than producing a delta. When the last page is copied the machine reaches Done and integrity_check verifies the target. Tick step(page_budget) Wait window yield until next gap Done remaining == 0 Restart source written mid-copy budget copied next window all pages a write commits copy from page 0

Solution

Drive the API yourself with a fixed page budget per tick and a scheduler that decides when the next tick may run. Passing a positive pages value copies that many pages and yields; you call it repeatedly. Python’s Connection.backup() with a positive pages will loop internally to completion, so to get true per-tick control you observe progress through the callback and pace the whole operation, treating a rising remaining as the restart signal to log and (optionally) back off on.

import sqlite3
import logging

logger = logging.getLogger("sqlite_incremental_backup")


def incremental_backup(src_path: str, dst_path: str, *, page_budget: int = 200,
                       tick_sleep: float = 0.20) -> None:
    """Copy a large DB in bounded ticks. page_budget pages per step; tick_sleep yields
    the CPU/lock to writers between steps so no single tick holds a long read lock."""
    src = sqlite3.connect(src_path, timeout=30.0)   # SECONDS: a busy step waits, does not fail
    dst = sqlite3.connect(dst_path, timeout=30.0)
    state = {"last_remaining": None, "restarts": 0}
    try:
        def _tick(status, remaining, total):
            # Restart detection: remaining should fall monotonically. A jump upward means
            # SQLite restarted the copy because the source was written mid-backup.
            prev = state["last_remaining"]
            if prev is not None and remaining > prev:
                state["restarts"] += 1
                logger.warning("backup restarted (%d so far) at %d/%d pages",
                               state["restarts"], total - remaining, total)
            state["last_remaining"] = remaining

        # page_budget pages per internal step + tick_sleep between steps = incremental
        # stepping: the read lock is held only for one budget at a time, then released.
        src.backup(dst, pages=page_budget, progress=_tick, sleep=tick_sleep)

        if state["restarts"]:
            logger.info("copy completed after %d restart(s)", state["restarts"])

        # Verify the finished target is a well-formed, consistent database.
        integrity = dst.execute("PRAGMA integrity_check;").fetchone()[0]
        if integrity != "ok":
            raise RuntimeError(f"incremental backup integrity_check failed: {integrity!r}")

        # Read back a structural invariant to confirm the copy is queryable.
        pages_copied = dst.execute("PRAGMA page_count;").fetchone()[0]
        if pages_copied <= 0:
            raise RuntimeError(f"empty backup: page_count={pages_copied}")
        logger.info("incremental backup verified: %s (%d pages)", dst_path, pages_copied)
    except sqlite3.OperationalError as e:
        logger.error("incremental backup failed (busy/full): %s", e)   # SQLITE_BUSY / SQLITE_FULL
        raise
    except sqlite3.Error as e:
        logger.error("incremental backup failed: %s", e)
        raise
    finally:
        dst.close()
        src.close()

Two parameters govern the trade-off. page_budget (here 200 pages, roughly 800 KiB at a 4 KiB page) bounds the read-lock hold per step: smaller is politer to writers but stretches wall-clock time and widens the restart window; larger finishes sooner but each step disturbs the write path more. tick_sleep is the gap handed back to writers between steps — raise it on a busy source so commits land in the gaps and the copy restarts less often. Restarts are counted, not hidden: if state["restarts"] climbs without the copy ever completing, the source is too hot for a continuous run and you must either widen the quiet windows or checkpoint and quiesce writes briefly.

Reconcile the copy with checkpointing so it stays fast. Because the backup reads the source’s current content, a bloated WAL means more work per copy and a longer restart window; run a PRAGMA wal_checkpoint(TRUNCATE) before a long incremental run so the copy starts from a compact database. The cadence that keeps the WAL small for continuous writers is set in Optimizing wal_autocheckpoint for Continuous Logging.

Verification

Confirm the completed copy is sound and that the run’s restart accounting was reasonable.

Assert the target is well-formed and non-empty before promoting it to “the backup”:

incremental_backup("/var/lib/app/main.db", "/backups/main-incremental.db")
v = sqlite3.connect("/backups/main-incremental.db")
assert v.execute("PRAGMA integrity_check;").fetchone()[0] == "ok"
assert v.execute("PRAGMA page_count;").fetchone()[0] > 0
v.close()

Confirm the copy did not silently loop forever by bounding restarts operationally — a run that logs many restarts and still finishes was working against too-frequent writes and should move to a quieter window:

# In a scheduled job, treat a high restart count as an alertable condition even on success.
# (state["restarts"] is logged above; surface it to your metrics pipeline.)

Finally, if the source is truly always-writing, verify your expectation rather than the mechanism: a clean incremental copy is only guaranteed when a full pass completes without a mid-pass write. When that is impossible, brief write quiescence during the final pass — or a scheduled maintenance window — is the correct answer, not a longer retry loop.

Failure Modes & Gotchas

“Incremental” is stepping, not delta — the copy restarts on any source write. The most damaging misconception is expecting the API to resume where it left off after a source change, capturing only the delta. It does not: a commit to the source discards in-progress copy state and restarts from page zero. If your recovery objective genuinely needs delta backups (moving only changed bytes nightly), the Backup API is the wrong tool — you want application-level change tracking or a full copy on a schedule you can complete uninterrupted. Use incremental stepping to bound lock-hold time, not to shrink the bytes moved.

A budget too small on a hot source guarantees perpetual restarts. Shrinking page_budget to be maximally polite lengthens the total copy, which widens the window in which a write can restart it — so on a continuously written database, an over-small budget can mean the copy literally never finishes. The fix is counterintuitive: on a hot source, either copy faster (larger budget) inside a genuine quiet window, or quiesce writes for the final pass. Diagnose it by watching the restart counter; if it climbs without remaining ever reaching zero, you are thrashing.

Skipping the pre-run checkpoint makes every pass do extra work. If the WAL is large when the incremental run starts, each copy pass must reconcile more frames and the restart window is longer. Always checkpoint to TRUNCATE before a long incremental copy, and keep the autocheckpoint cadence tuned per Checkpoint Frequency Tuning; pair the finished copy with the same integrity_check verification you would run on any hot copy from the parent Online Backup API & Hot Copies topic.

For the exact restart-on-write contract, see the official SQLite Backup API documentation and the Python sqlite3 backup() reference.