Verify SQLite Database Integrity Before and After Backup

Run integrity_check on the source, take an online backup with the backup API instead of copying the file, and verify the copy row-for-row.

~20 mindatabasesqlitebackup

Task

Prove both source and backup are structurally sound and logically identical using integrity_check, backup API, and row counts.

When to use this

A nightly job copies the .db file with cp while writes are in flight. The copy restores with database disk image is malformed and nobody knows which side is corrupt.

Prerequisites

SQLite 3.31 or newer with sqlite3 CLI
A database under live writes for the online-backup demonstration
Disk space for one full copy plus the verification dump

Steps

1. Check the source database structural integrity first.

sqlite3 app.db "PRAGMA integrity_check;"

Expected output: A healthy database prints ok; anything else, such as a page chain error, means fix the source before any backup is worth taking.

2. Check logical consistency of foreign keys and indexes.

sqlite3 app.db "PRAGMA foreign_key_check;" && sqlite3 app.db "PRAGMA quick_check;"

Expected output: foreign_key_check prints nothing when consistent, and quick_check returns ok, covering row-level corruption faster than the full check.

3. Back up online with the backup API, not a file copy.

sqlite3 app.db ".backup backup.db" && sqlite3 backup.db "PRAGMA integrity_check;"

Expected output: backup.db passes integrity_check even though writes continued during the backup, because the API copies pages transactionally.

4. Compare logical content between source and backup.

sqlite3 app.db ".dump" | sha256sum > /tmp/src.sha; sqlite3 backup.db ".dump" | sha256sum > /tmp/bak.sha; diff /tmp/src.sha /tmp/bak.sha && echo "logical content identical"

Expected output: The dump hashes match, proving every table, row, and schema object is logically identical between the two files.

5. Prove why the naive cp copy fails, to close the argument.

(sqlite3 app.db "INSERT INTO events DEFAULT VALUES;" & cp app.db naive-copy.db; wait) ; sqlite3 naive-copy.db "PRAGMA integrity_check;"

Expected output: The naive copy frequently reports corruption or a stale journal state, demonstrating why online copies need the backup API or a checkpoint first.

Troubleshooting

Problem

integrity_check reports errors on the source

Solution

Stop backing up and recover first: use .recover to extract readable rows into a new database, then rebuild from the logical dump instead of propagating corruption.

Problem

Dump hashes differ after backup

Solution

Live writes continued between the two dumps; quiesce writes or run both dumps inside one read transaction for a stable comparison.

Problem

Backup fails with database is locked

Solution

A long-running reader blocks the backup; retry with sqlite3_backup_step semantics via the CLI .backup, or schedule the copy when writers are idle.

Limitations

integrity_check verifies structure, not business correctness; pair it with foreign_key_check and application-level invariants.
The dump-hash comparison is a point-in-time proof; under continuous writes, compare inside one transaction or accept eventual consistency.

A copied file is not a backup until it is verified

Copying a live SQLite file with cp captures pages mid-write and a journal that may never match the copy. The online backup API copies pages inside repeated transactions, so the result is a consistent snapshot. Verifying both sides with integrity_check and a dump hash turns hope into evidence.

# 1) structural health of the source
sqlite3 app.db "PRAGMA integrity_check;"     # -> ok
sqlite3 app.db "PRAGMA foreign_key_check;"   # -> (empty = consistent)

# 2) online backup, safe under writes
sqlite3 app.db ".backup backup.db"

# 3) logical equality, not just file equality
sqlite3 app.db    ".dump" | sha256sum   # a1b2...
sqlite3 backup.db ".dump" | sha256sum   # a1b2...  identical

# 4) the anti-pattern, for the record
# cp app.db copy.db   # pages mid-write -> malformed copy

Fix the source before copying the problem

If integrity_check fails on the source, a backup just archives corruption. Use .recover to salvage readable rows into a fresh database file, reload from the logical dump, and only then resume a backup schedule. Copying a corrupt file faster helps nobody.

Compare dumps, not file bytes

Two byte-identical files are impossible under live writes, and two files with different free-page layouts can be logically identical. The .dump output normalizes the logical content, so matching SHA-256 hashes are the right proof of backup correctness.