SQLite3 Data rescue on Error: Database disk image is malformed

Question

Background

I have a database thats been corrpted, and want to save so much of the data possible.

I have tried sql dump the data with numerous of tools, without success. Always same error message:

Error: database disk image is malformed

I'm pretty sure this did happen due to a power failure.

Approach?

Now the the database is in fact a file. And I'm thinking if its possible to treat it so and try to save so much data as possible.

I guessing when the db is opened by a tool or program it first check its headers. In my case I get the error message right away. I'm assuming that the headers are corrupt, or miss matching. And due to that no tool will try to read the payload.

In the documents http://www.sqlite.org/fileformat2.html there are explanations for the header offsets.

Questions: Is this is an reasonable approach? And if it possible to repair, modify or exchange headers on the corrupted db. And how do I do it?

Answer 1

Despite several replies in multiple threads on SO to the contrary, SQLite databases can be recovered from corruption!

I have requested an update from the SQLite team in their FAQ (http://www.sqlite.org/faq.html#q20), but in the meantime, here are a couple of options.

The FAQs state:

20. "...If SQLITE_SECURE_DELETE is not used and VACUUM has not been run, then some of the deleted content might still be in the database file, in areas marked for reuse. But, again, there exist no procedures or tools that we know of to help you recover that data."

and:

21. "...Depending how badly your database is corrupted, you may be able to recover some of the data by using the CLI to dump the schema and contents to a file and then recreate. Unfortunately, once humpty-dumpty falls off the wall, it is generally not possible to put him back together again."

There are in fact at least two excellent tools to do data recovery for whole SQLite databases and individual records, and they can help in cases of hardware failure, software errors or human problems. It will not be 100% pristine, but the situation is not hopeless

PhotoRec is open source and multi-platform. While historically, it was used for images and pdfs, it now supports SQLite recovery (http://www.cgsecurity.org/wiki/File_Formats_Recovered_By_PhotoRec), along with 220+ binary file types. If a database (or entire directory) is deleted, PhotoRec can often restore the db file to a sane-enough state to be opened and exported. There are pre-compiled versions of the app freely available for Windows, Mac and Linux.

In addition, the commercial product Epilog by CCL Forensics can do very advanced record recovery, including retrieving data from the write-ahead log (WAL) transaction files. It is a few hundred dollars, but it can do fairly amazing forensic reconstruction on SQLite data (both native binary db files as well as raw disk images).

Both the above have saved my hide several times, so passing this along for others who may have lost hope in deleted/corrupted SQLite databases (as well as genuine forensics for popular use cases, like mobile phones, browsers, address books, etc.).

Once you've regenerated/exported data, it's always a good idea to verify your backup schemes and definitely run a pragma integrity_check periodically, along with vacuuming.

I have requested that the official FAQ be updated to at least mention that one can google "sqlite recovery" or something if it's verboten to mention other projects/products by name.

Cheers.