.backup, Online Backup API, and VACUUM INTO

Learning outcomes

A live backup should be created through SQLite rather than by guessing which bytes are stable. SQLite exposes two main snapshot mechanisms: the online backup API, surfaced by many language drivers and the CLI .backup command, and VACUUM INTO, which constructs a compact consistent copy.

The online backup API creates a database snapshot

The core API uses sqlite3_backup_init(), one or more sqlite3_backup_step() calls, and sqlite3_backup_finish(). It copies database pages into a destination database. Incremental stepping lets SQLite release source read locks between chunks so other users can continue, although heavy concurrent writes can force backup work to restart.

sqlite3_backup *b = sqlite3_backup_init(dest, "main", source, "main");if (b != NULL) {    do {        rc = sqlite3_backup_step(b, 64);   /* copy up to 64 pages */        if (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED)            sqlite3_sleep(50);    } while (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED);    rc_finish = sqlite3_backup_finish(b);}

Production code needs complete error handling and retry policy; this compact fragment exists only to reveal the lifecycle. The destination connection must not be used for unrelated work while backup is active.

The CLI exposes the same operational idea

Current SQLite CLI help documents .backup ?DB? FILE, .save as a backup alias, and .restore ?DB? FILE. These are dot-commands interpreted by the shell, not SQL statements that can be prepared through an application driver.

sqlite3 fieldnotes.sqlitesqlite> .backup main fieldnotes-20260812T101700Z.sqlitesqlite> .quit# Verify the backup as a separate database before relying on it.sqlite3 fieldnotes-20260812T101700Z.sqlite   "PRAGMA integrity_check; PRAGMA foreign_key_check;"# Restore drill into a disposable target, not blindly over production.sqlite3 restore-drill.sqlitesqlite> .restore main fieldnotes-20260812T101700Z.sqlitesqlite> PRAGMA integrity_check;sqlite> .quit

Python exposes the online backup directly

Python’s standard sqlite3.Connection.backup() is a practical way to see the application form of the same engine feature. The destination is another SQLite connection.

from pathlib import Pathimport os, sqlite3source_path = Path("fieldnotes.sqlite")tmp_path = Path("fieldnotes-20260812T101700Z.sqlite.tmp")final_path = Path("fieldnotes-20260812T101700Z.sqlite")with sqlite3.connect(source_path) as source:    dest = sqlite3.connect(tmp_path)    try:        source.backup(dest, pages=32)    finally:        dest.close()check = sqlite3.connect(tmp_path)try:    assert check.execute("PRAGMA integrity_check").fetchone()[0] == "ok"    assert list(check.execute("PRAGMA foreign_key_check")) == []    assert check.execute("PRAGMA application_id").fetchone()[0] == 1179533646    assert check.execute("PRAGMA user_version").fetchone()[0] == 16    assert check.execute("SELECT count(*) FROM maintenance_note").fetchone()[0] == 1200finally:    check.close()# Atomic rename on the same filesystem publishes the already-verified file.os.replace(tmp_path, final_path)

The temporary name prevents other automation from mistaking an unfinished file for a completed backup. The final rename does not make the backup magically durable on every storage stack; it simply gives consumers a clear publication boundary. Your backup system still needs storage-specific durability and retention controls.

VACUUM INTO: compact copy rather than page-for-page backup

VACUUM INTO, introduced in SQLite 3.27.0, creates a new database containing the same logical content while rebuilding it into a compact form. The source database is unchanged. The destination must not already contain data: the file must be absent or empty.

-- Run outside an active transaction on this connection.SELECT sqlite_version();PRAGMA synchronous;VACUUM INTO 'fieldnotes-compact-20260812T101700Z.sqlite';

Current documentation states that the result is a consistent snapshot. It also states that interruption while the command is still running can leave an incomplete/corrupt output. With source synchronous=NORMAL or FULL, current SQLite synchronizes the completed output database to storage before returning, assuming normal OS/filesystem/hardware behavior.

Measured compact-copy experiment

The course test created churn data, deleted most of it, then compared the live source file with a VACUUM INTO copy. One run on SQLite 3.46.1 produced the following sizes:

source before VACUUM INTO : 2,179,072 bytescompact destination       :   331,776 bytesintegrity_check           : okmaintenance_note rows     : 1200

The important observation is the relationship, not the exact byte count: reusable/free space existed in the source, while the rebuilt destination omitted it. Page size, payloads, indexes, filesystem allocation, and SQLite version can change the numbers.

Compare the physical-copy choices

Verification is part of backup creation

A backup file existing on disk proves only that bytes were written. A useful backup should be opened independently and checked against what the application actually needs.

PRAGMA integrity_check;       -- structural / index / constraint checksPRAGMA foreign_key_check;     -- referential violations are separatePRAGMA application_id;        -- is this the expected application file?PRAGMA user_version;          -- what application schema version is it?SELECT count(*) FROM device;SELECT count(*) FROM maintenance_note;SELECT count(*) FROM device WHERE status='active';

For a larger system, add invariant queries, expected critical objects, checksums/hashes of stable logical subsets, and an actual application read test. Store the verification result next to backup-job metadata.

Timestamp and naming policy

Checkpoint

Bridge to logical exports

Physical backups preserve an SQLite database as a database. Lesson 3 changes the artifact completely: .dump emits SQL text that recreates schema and data. That can be excellent for inspection or migration, but it has different size, speed, portability, and extension behavior.

Authoritative references

• SQLite Online Backup API
• SQLite Online Backup C API
• SQLite VACUUM / VACUUM INTO
• SQLite CLI dot-commands
• SQLite 3.27.0 release notes