Fix corrupted SQLite3 database
I tackled a very strange support request last few days, which escalated to the point that customer extracted the .sqlite database from the iPhone and sent it to me. I’m a SQLite3 rookie, but this worked for this particular case and I need to write for future reference. :)
When trying to open the file, Base would complain that file is either encrypted or damaged. When I placed it on my test device and started the app, it would immediatelly crash with this message in the console:
disk image is malformed
This usually happen if database file is accessed while still in use, but it can also happen if app is terminated during the save. In any case, I now needed to fix the database file and here’s how to do that.
Fixing damaged SQLite3 file
Copy it to some folder and open that folder in Terminal. Then type sqlite3 DBNAME.sqlite and Return to enter into sqlite3 shell, then do this:
.mode insert
.output s.sql
.dump
.exit
Now, open this s.sql file in any editor and remove all BEGIN TRANSACTION and ROLLBACK statements you find, as well as any lines where you see ERRORs mentioned.
Then re-build the fixed database:
sqlite3 FIXED.db < s.sql
You will most likely lose some records, but will probably mean you now have a working database.
One additional tidbit: at the sqlite shell, this is the command to check integrity:
PRAGMA integrity_check;
If it results in anything except ok, database is corrupted.