How to recover a corrupt SQLite3 database?

Solution 1:

UPDATE: There is now an automatic method that is built into SQLite: .recover

Sometimes, the corruption is only or mostly in indexes, in which case it would be possible to get some or most records by trying to dump the entire database with .dump, and use those commands to create a new database:

$ sqlite3 mydata.db ".dump" | sqlite3 new.db

However, this is not always possible.

The easiest and most reliable way is to restore the database file from the backup.

Solution 2:

With Sqlite 3.29.0 a new .recover command has been introduced to the CLI:

Add the ".recover" command which tries to recover as much content as possible from a corrupt database file.

sqlite3 broken.db ".recover" | sqlite3 new.db

Solution 3:

I had an sqlite file that was corrupt that would show a symptom like this.

select count(*) from corruptTable;
return:38000;

But when I would try to load the records with

select * from corruptTable;

It would only return 7 records.

I tried several things, but these steps were the most successful.

On a mac, open terminal and run these commands on your corrupt database. (these are sqlite3 commands, so you should be able to use other sqlite3 editors or similar commands in other systems).

1 sqlite3 dbWithCorruptTable.sqlite (Obviously replace "dbWithCorruptTable" to your sqlite3 file that has the corrupt table)
2 .mode insert
3 .output dump_all.sql
4 .dump
5 .exit
6 Manually edit the dump_all.sql file in a text editor and remove the transaction statements. Usually there is a "BEGIN TRANSACTION" statement on the 2nd line of the file and a "ROLLBACK" statement on the last line. Remove these and save the file

These steps were taken from this website: http://www.dosomethinghere.com/2013/02/20/fixing-the-sqlite-error-the-database-disk-image-is-malformed/

Solution 4:

If the database is seriously corrupt, the .dump will contain errors, and some data may be lost.

For more complex data schemas, this will mean orphaned and/or partial records which may confuse the application.

It may be preferable to .dump to a file, then use a text editor to remove problematic rows. Search for ERROR within the dump file.

Solution 5:

My method is similar, prevents a error rollback script:

sqlite3 database.db ".dump" | sed -e 's|^ROLLBACK;\( -- due to errors\)*$|COMMIT;|g' | sqlite3 database.new