Backup, Recovery & Data Integrity #

A production SQLite database is only as valuable as your ability to prove it is intact and to bring it back after the storage beneath it lies to you. The engine’s crash-safety model — WAL frame checksums, atomic commit markers, deterministic replay — protects the database against an interrupted transaction, but it makes no promise about an operator who copies the file at the wrong instant, a flash controller that acknowledges an fsync() it never performed, or a cron job that fills the partition mid-checkpoint. Those are the failures that turn a healthy database into SQLITE_CORRUPT (“database disk image is malformed”), and no amount of PRAGMA tuning recovers data you never captured a consistent copy of. This guide is the entry point for the discipline that sits underneath every other hardening decision: producing a consistent hot copy while the database is live, verifying that copy and the live database with integrity checks, reclaiming space without introducing new corruption windows, and routing deterministically around the family of failure codes that signal a damaged image. It hands off to three deeper topics — the online backup API and hot copies, integrity checking and corruption recovery, and VACUUM and space reclamation — where each concern is worked through in full. The goal throughout is a database you can restore on demand and verify on a schedule, not one you merely hope is fine.

Core Mechanics & Crash-Safety Architecture #

The single most common backup mistake is treating a live SQLite database as one file. It is not. In WAL mode a logically consistent database is the combination of the main file, the -wal file holding committed frames not yet checkpointed, and the -shm shared-memory index that tells a reader which frames supersede which pages in the main file. A naive cp, rsync, or filesystem snapshot copies those files independently and without coordination, so it captures them at three slightly different moments. If a checkpoint or a commit runs between copying main and copying -wal, the resulting pair is internally inconsistent: the WAL references a database generation the main file no longer matches, or the main file has advanced past frames the copied WAL still claims are pending. Open that copy and SQLite reports SQLITE_CORRUPT or, if the header itself was caught mid-write, SQLITE_NOTADB (“file is not a database”). Even in rollback-journal mode the same hazard applies to the -journal file, and copying the main file alone while the journal describes an in-flight rollback yields a torn image.

The reason a live byte copy is unsafe is that SQLite’s atomicity guarantees are defined inside the engine, across the coordinated set of files, under the page-level locks it holds. A copy tool holds none of those locks and sees none of that coordination. Quiescing every writer and running PRAGMA wal_checkpoint(TRUNCATE) to fold all frames back into the main file and empty the WAL produces a moment where the main file alone is a complete database — but “quiesce every writer” is exactly the guarantee a busy production system cannot offer, and any writer that reconnects mid-copy reopens the window. That is why the correct mechanism is not a smarter file copy but a page-aware copy performed through the engine itself.

The online backup API is that mechanism. In Python’s sqlite3 it is exposed as Connection.backup(); underneath, both bind to the C sqlite3_backup_init, sqlite3_backup_step, and sqlite3_backup_finish machinery. The API opens a read transaction against the source, then copies a bounded number of pages per step call directly into the destination database, page by page, holding the appropriate locks so each page it reads is internally consistent. Crucially, it is restart-aware: if a concurrent writer modifies a source page the backup has already copied, SQLite detects the write and transparently restarts the copy from the beginning so the destination can never contain a mix of pre- and post-write pages. The result is a destination file that is a valid, self-consistent database as of a single logical point — the definition of a consistent hot copy — without ever freezing the source. Because the copy proceeds in increments, you can throttle it (a sleep between step batches) to bound its I/O impact on a live edge device, at the cost of more frequent restarts on a hot database.

Corruption, when it appears, almost never originates in SQLite’s own logic. It enters from beneath the VFS layer. The dominant sources are power loss during a write when the underlying fsync() was not honored, aging or counterfeit flash that returns bad blocks (surfacing as SQLITE_IOERR with a subcode), a memory-mapped page whose backing storage disappears or faults mid-read (delivering a SIGBUS to the process rather than a clean error), and filesystems or controllers that reorder writes across the sync barrier or lie about flush completion. The engine assumes the storage stack tells the truth; when it does not, a “successful” commit can be silently torn, and the damage stays invisible until a query happens to read the affected b-tree node. This is why verification must be scheduled, not reactive — corruption is latent, and a backup you never verified may be a faithful copy of an already-malformed image.

Configuration & PRAGMA Baselines #

Backup and integrity work is governed less by a large PRAGMA stack than by a few settings that decide how reclaimable space is tracked and how aggressively pages return to the OS. Apply these against the durability baseline from the PRAGMA optimization guide; the ones below are the settings that specifically shape backup, integrity, and space behaviour.

PRAGMA auto_vacuum = INCREMENTAL;   -- track free pages in a pointer map so incremental_vacuum can reclaim them; must be set before first table is created (or before a VACUUM)
PRAGMA page_size = 4096;            -- 4 KiB pages align with flash erase/write granularity; set before the DB is populated, it is fixed thereafter
PRAGMA wal_autocheckpoint = 1000;   -- fold ~1000 WAL pages (~4 MB) back before they accumulate; bounds the -wal a hot copy must reconcile
PRAGMA synchronous = NORMAL;        -- safe in WAL: fsync deferred to checkpoint; step up to FULL where an acknowledged commit must survive a brownout
PRAGMA cache_spill = ON;            -- allow dirty pages to spill to WAL under memory pressure so a large VACUUM/backup does not exhaust RAM

auto_vacuum is the load-bearing choice. Its default of NONE means deleted rows leave their pages on an internal freelist — the file never shrinks, it only grows to its high-water mark, and space is reused only by future inserts. FULL moves free pages to the end of the file and truncates on every commit, which reclaims eagerly but adds write amplification (bad for flash) to the commit path. INCREMENTAL records free pages in a pointer map without moving them automatically, letting you drain them on your own schedule with PRAGMA incremental_vacuum — the right balance for continuous writers, and the subject of VACUUM and space reclamation. The critical constraint: auto_vacuum and page_size can only be changed on an empty database or by performing a full VACUUM, which rewrites the entire file. You cannot flip these on a populated production database without paying for a full rebuild.

Two derived quantities drive maintenance decisions. PRAGMA freelist_count reports how many free pages are sitting unused inside the file; multiply by page_size for the reclaimable bytes. PRAGMA page_count times page_size is the total file size. When freelist_count / page_count climbs past roughly 15–25%, the file is carrying significant dead space and an incremental vacuum (or a maintenance-window VACUUM) is warranted. Reading these two counters costs nothing and belongs in your health telemetry alongside WAL size, which is watched more closely in checkpoint frequency tuning.

Failure Mode Documentation #

Integrity and backup failures announce themselves through a specific set of return codes. Each has a distinct root cause and a distinct correct response; conflating them (for example, retrying SQLITE_CORRUPT as if it were transient) turns a recoverable incident into data loss.

Two patterns warrant emphasis. Latent corruption is corruption that already exists but has not yet been read: a bad b-tree node sits untouched until a query traverses it, so a database can pass casual use for weeks and then fail suddenly. The only defense is a scheduled integrity_check (or the faster quick_check) that deliberately walks the structure, covered in integrity checking and corruption recovery. Backing up a corrupt source is the failure that quietly defeats an otherwise disciplined backup rotation: the backup API faithfully copies whatever the source contains, malformed pages included, so verification must run against the restored copy, not merely confirm that the copy operation returned success.

Implementation Patterns #

The following sqlite3 routine performs a consistent hot copy with the online backup API, then verifies the resulting file by running PRAGMA integrity_check against it and by reading back the durability-relevant PRAGMAs. Verification is not optional: a backup whose integrity you never confirmed is indistinguishable from no backup at all.

import sqlite3
import time

SOURCE_DB = "/var/lib/app/telemetry.db"
BACKUP_DB = "/var/backups/app/telemetry.backup.db"

# Backup/integrity-relevant settings to confirm on the SOURCE before copying.
EXPECTED = {
    "journal_mode": ("wal",),
    "auto_vacuum": (2,),          # 2 == INCREMENTAL
    "synchronous": (1, 2),        # NORMAL or FULL both acceptable
}


def _progress(status, remaining, total):
    # Called after each batch of pages; lets us log/throttle a long copy.
    copied = total - remaining
    print(f"backup: {copied}/{total} pages")


def hot_copy(source_path: str = SOURCE_DB, dest_path: str = BACKUP_DB) -> None:
    src = sqlite3.connect(source_path, timeout=5.0)
    dst = sqlite3.connect(dest_path)
    try:
        # Confirm the source is configured as we expect before trusting the copy.
        for pragma, allowed in EXPECTED.items():
            got = src.execute(f"PRAGMA {pragma};").fetchone()[0]
            got = got.lower() if isinstance(got, str) else got
            if got not in allowed:
                raise RuntimeError(
                    f"source PRAGMA {pragma}={got!r} not in {allowed}; refusing to back up"
                )

        # Page-aware, restart-safe copy through the engine. pages=64 copies in
        # bounded batches; sleep=0.05 throttles I/O impact on a live device.
        src.backup(dst, pages=64, progress=_progress, sleep=0.05)
    except sqlite3.Error:
        # SQLITE_BUSY/IOERR during copy: leave no half-written backup in place.
        raise
    finally:
        dst.close()
        src.close()

    verify_backup(dest_path)


def verify_backup(dest_path: str = BACKUP_DB) -> None:
    """Open the finished copy and prove it is a well-formed database."""
    conn = sqlite3.connect(dest_path)
    try:
        # integrity_check returns a single row 'ok' on a clean database.
        rows = conn.execute("PRAGMA integrity_check;").fetchall()
        result = [r[0] for r in rows]
        if result != ["ok"]:
            raise RuntimeError(f"backup failed integrity_check: {result[:5]}")

        # Cross-check the freelist so we retain space telemetry with the backup.
        free = conn.execute("PRAGMA freelist_count;").fetchone()[0]
        pages = conn.execute("PRAGMA page_count;").fetchone()[0]
        assert free <= pages, "freelist_count exceeds page_count: header damaged"
        print(f"verified backup: {pages} pages, {free} free")
    except sqlite3.DatabaseError as exc:
        # 'malformed' == SQLITE_CORRUPT, 'not a database' == SQLITE_NOTADB.
        msg = str(exc).lower()
        if "malformed" in msg or "not a database" in msg:
            raise RuntimeError(f"backup image is unusable: {exc}") from exc
        raise
    finally:
        conn.close()


if __name__ == "__main__":
    started = time.monotonic()
    hot_copy()
    print(f"backup + verify completed in {time.monotonic() - started:.1f}s")

The pattern encodes three non-obvious rules. First, backup() copies through the engine with pages and sleep arguments, so it is both consistent and throttleable — never substitute a shell cp for it. Second, verification runs PRAGMA integrity_check against the destination file after the copy finishes, catching a source that was already corrupt as well as any I/O error during the copy. Third, the source’s configuration is asserted before the copy begins, because a database in the wrong auto_vacuum mode or an unexpected journal_mode may not be the artifact you intended to protect. For streaming the copy off a constrained device rather than to a local path, the same API drives streaming hot backups from edge devices, and re-copying only changed pages is the province of incremental backups with the backup API.

Filesystem & Security Boundaries #

Because SQLite has no server process, backup and recovery inherit the host operating system’s security model whole, and the boundaries that protect the live database protect the backups too — a copy is just another file that grants whoever can read it full access to the data. Three boundaries govern this domain.

Ownership, mode, and the backup destination. The process must own not only the source database and its directory (so it can create the -wal and -shm files the backup read transaction relies on) but also the directory it writes backups into. Create backups with umask 077 so they land at 0600, and keep the backup directory at 0700 under strict UID/GID ownership; a database snapshot in a world-readable path is a trivial data-exfiltration vector regardless of how well the live file is locked down. The exact chown/chmod matrices are enumerated in file system permissions & ownership. A common SQLITE_CANTOPEN during recovery traces directly to restoring a database into a directory whose permissions do not let the process create the companion files.

Free space and the reclamation window. A full VACUUM rebuilds the database into a temporary file before swapping it in, so it transiently requires free space of up to roughly twice the current database size; a VACUUM that runs a partition out of room aborts with SQLITE_FULL and can leave a large temporary artifact behind. Verify available space before any full rebuild, and prefer PRAGMA incremental_vacuum on space-constrained media, where it reclaims freelist pages in bounded batches without the double-size spike — the pattern detailed in scheduling incremental VACUUM for continuous writers.

fsync honesty and network storage. Every durability and recovery guarantee rests on the storage stack honoring fsync() and not reordering writes across the barrier. A controller that acknowledges an unperformed flush produces SQLITE_CORRUPT after the next power cut no matter how conservative your PRAGMAs are, and it corrupts backups taken from that media too. WAL mode additionally requires real shared memory and so does not work correctly over most network filesystems; edge nodes that sync to a central store must back up a checkpointed, backup-API copy rather than the live file over the network, and should verify the copy on the far side before trusting it. Where regulation demands it, encrypt backup snapshots (for example AES-256-GCM) before offloading them to cold storage.

Production Deployment Checklist #

• Never back up a live database with cp, rsync, or a filesystem snapshot; use Connection.backup() / the sqlite3_backup_* API so the copy is page-consistent.
• Run PRAGMA integrity_check against every completed backup image, not just against the live database, and reject any result other than a single ok.
• Schedule a periodic integrity_check (or quick_check on large databases) to surface latent corruption before a query hits it in production.
• Track freelist_count and page_count in health telemetry; trigger reclamation when free pages exceed ~15–25% of the file.
• Choose auto_vacuum=INCREMENTAL and drain freelist pages with PRAGMA incremental_vacuum on flash; reserve full VACUUM for maintenance windows with ≥ 2x free space.
• Checkpoint (PRAGMA wal_checkpoint(TRUNCATE)) before any operation that assumes the main file alone is complete.
• Create backups with umask 077; keep the backup directory at 0700 under the correct UID, and encrypt snapshots destined for cold storage.
• Validate fsync() honesty and disable volatile write caching on SD/eMMC media, since a lying flush corrupts live and backup images alike.
• Rehearse restore from a verified backup against simulated power loss, and confirm the restored process can create -wal/-shm in the target directory.
• On SQLITE_CORRUPT or SQLITE_NOTADB, halt writes and restore rather than retry; treat these codes as non-transient.

Backup, verification, and reclamation are not periodic chores bolted onto a working system — they are the properties that make SQLite trustworthy on hardware that fails. A database you can copy consistently while it runs, verify on a schedule, keep compact without corruption, and restore under rehearsal is a database that earns the crash-safety reputation SQLite claims. The pages below take each half of that promise — capture and recovery — down to the exact API calls, PRAGMAs, and thresholds.

Related Pages #

• Online Backup API & Hot Copies — copying a live database consistently with sqlite3_backup_* and Connection.backup().
• Integrity Checking & Corruption Recovery — integrity_check vs quick_check, where corruption comes from, and how to recover a malformed image.
• VACUUM & Space Reclamation — VACUUM, auto_vacuum, incremental_vacuum, and reading freelist_count for reclaimable space.
• Checkpoint Frequency Tuning — bounding WAL growth so a hot copy has less to reconcile.
• File System Permissions & Ownership — the ownership and mode rules that protect both the live database and its backups.