SQLite disk I/O error fix: restore or repair

Quick answer: Run sqlite3 broken.db .recover | sqlite3 fixed.db to recover what you can, then restore from your latest backup if that fails.

What's really going on

SQLite's disk I/O error (SQLITE_IOERR) is the database equivalent of a hard crash. It happens when SQLite tries to read or write a page and the underlying file system returns an error — could be a bad sector on the drive, a corrupted journal file, or the database was left in an inconsistent state after a power loss. Last month I had a client whose Android app kept crashing — turned out the SQLite database on the SD card got corrupted because the user pulled the card out while the app was writing.

The error message itself doesn't tell you which specific page is bad, just that I/O failed. That's why the recovery process is a bit of a shotgun approach: you try the easiest fix first (backup restore), then the surgical fix (recover), then the nuclear option (dump and rebuild).

Fix steps (in order of likelihood to work)

1. Restore from your latest backup

If you have a backup, stop right here. Copy the backup file over the corrupted one, then run PRAGMA integrity_check; to confirm it's clean. If you don't have a backup, skip to step 2 — and go set up automatic backups after you fix this.

2. Run sqlite3 .recover

Open a terminal (or Command Prompt on Windows) and run:

sqlite3 corrupted.db .recover | sqlite3 fixed.db

This scans the entire database file, reads every page it can, and writes the recoverable data to a new database. It'll skip pages that are completely unreadable. You'll lose any data that was on those bad pages, but you'll get back 95-99% of your data in most cases.

After it finishes, rename fixed.db to corrupted.db and run PRAGMA integrity_check; again.

3. Dump and rebuild with .dump (fallback)

If .recover fails with an error, try the older .dump command:

sqlite3 corrupted.db .dump > dump.sql
sqlite3 fixed.db < dump.sql

This is slower and might fail completely if the first few pages are gone. But sometimes it works when .recover doesn't (especially on very old SQLite versions).

4. Manual hex dump recovery (advanced, last resort)

If you're desperate and the data is critical, open the file in a hex editor. SQLite stores strings in plain text inside the database file. You can manually extract table names, column names, and some data values. It's tedious — I did this once for a client's inventory database and it took 6 hours — but it's better than nothing.

Alternative fixes if the main one fails

If .recover and .dump both fail, check whether the file system itself is corrupted. On Windows, run chkdsk /f C: (replace C: with the drive letter). On macOS/Linux, run fsck on the drive. I've seen cases where the database was perfectly fine but the file system had errors that made SQLite think the file was bad.

Another possibility: the database file is stored on a network share or cloud sync folder (Dropbox, Google Drive). Those services can lock the file or partially sync it. Copy the file to a local drive before opening it in SQLite.

How to prevent this from happening again

The number one cause of SQLite disk I/O errors is unsafe shutdowns. If your app or OS crashes while writing, the database can end up with a torn page. Enable WAL mode (Write-Ahead Logging) — it's much more crash-safe than the default rollback journal mode:

PRAGMA journal_mode=WAL;

Also set a reasonable PRAGMA busy_timeout (like 5000ms) so concurrent writers don't trip over each other and cause corruption.

And for the love of all that is holy, back up your database daily. Use a cron job or scheduled task that runs .backup to a separate drive. That one habit has saved more clients of mine than any repair tool ever will.