What PRAGMA integrity_check Does and Does Not Prove

SQLite integrity_check is useful, but it is one part of a restore drill, not a full disaster recovery plan.

The check people mention, but rarely explain

PRAGMA integrity_check is one of those SQLite commands that shows up in every serious backup conversation. It is worth using. It is also worth understanding what it can and cannot tell you.

In a restore drill, integrity_check is a strong signal that the restored database file is structurally readable. It is not a magic business-level audit, and it does not prove your whole recovery process is ready.

What it proves

PRAGMA integrity_check asks SQLite to inspect the database structure and report corruption. It checks the internal consistency of the database file: pages, b-trees, indexes, table relationships that SQLite can validate, and other low-level invariants.

A clean result is meaningful. It means SQLite could open the restored file and did not find structural corruption during the check. If your backup restore cannot pass that bar, you should treat it as a backup incident.

sqlite3 /tmp/restored.db 'PRAGMA integrity_check;'

What it does not prove

Integrity is not freshness. A perfectly valid database from last month can pass integrity_check while being useless for today's outage. You still need to know when the latest backup was taken.

Integrity is not application correctness either. The database can be structurally valid while missing a tenant, containing bad rows from a buggy migration, or failing because the app expects files outside SQLite. integrity_check sees the database file, not your product.

Use it after restore, not instead of restore

Running integrity_check on production is fine as a maintenance habit, but for backups the important place to run it is on the restored copy. That exercises storage, credentials, restore tooling, and the database file you would actually use during recovery.

Pair the check with simple table and row counts. You do not need to validate every business invariant in the first version. You do want to catch the obvious wrong restores, empty databases, and missing tables.

sqlite3 /tmp/restored.db 'PRAGMA integrity_check;'
sqlite3 /tmp/restored.db '.tables'
sqlite3 /tmp/restored.db 'SELECT count(*) FROM users;'

How Borela uses it

Borela restores the latest managed backup into an isolated temporary database, runs integrity_check, records restored size, table count, row count, and the drill result, then removes the temporary file.

The point is not that integrity_check is enough by itself. The point is that it is a crisp, automatable check inside a larger restore-proof loop.