Recovering a corrupt SQLite database — and knowing when not to
A media manager began returning HTTP 500. The database was malformed, the most recent backups were also corrupt, and the automatic repair tool produced a file that passed integrity checks but silently destroyed data.
Context
An application returned HTTP 500 on its main listing endpoint. The underlying SQLite database reported corruption with truncated overflow chains in a specific table. Four backups existed, spanning three weeks.
Constraints
- The service was in daily use and needed to come back up.
- Restoring a three-week-old backup risked silently losing recent changes.
- The obvious repair tool is not data-preserving, and does not announce that.
Decisions
Validate every backup before choosing one
All four backups were integrity-checked rather than assuming the newest was good. Two were also corrupt — damage predated the most recent snapshots, so "restore last night" would have restored the fault.
Treat the repair output as suspect until diffed
The recovery tool produced a database that passed integrity checks and had matching row counts. A column-level comparison revealed it had emptied two JSON columns and destroyed a path column in the corrupted cells. Integrity passing did not mean correct.
Restore the older clean copy, having proved it was complete
Comparison showed the older backup and the live data were identical in row counts and paths for every table that mattered, so the "older" copy was not stale — it was simply the last clean one.
Outcome
- Service restored, integrity check clean, listing endpoint returning 200.
- Verified the restore introduced no regression by comparing all three database versions for identical item and file counts.
- Documented that two of the four backups must never be restored, so a future reader does not repeat the mistake.
What it taught me
A passing integrity check proves structural consistency, not data fidelity. Backups that are never validated are not backups.
Addresses, hostnames and identifiers in this write-up are abstracted or generalised. If you would like the specific detail, ask — I am happy to walk through it directly.