Someday some our application on customer site stopped working. The app’s log contained an error relates to SQLite (Our .net4 based application uses SQLite as an embedded DB):

"System.Data.SQLite.SQLiteException: The database disk image is malformed database disk image is malformed"

From log it was obvious that there’s a reboot right before the moment. Application successfully run after the reboot and SQLite executed SELECTs without errors. But on update/insert it threw the mentioned exception.
It’s the first time when I’ve encountered any issue with SQLite. My experience before was only positive. Obviously a very bad thing has happened — a corruption of data without any hardware failure. I understand that an embedded DB can’t offer the same level of durability as “normal” DBMS like MSSQL/Oracle. But nonrecoverable corruption of database file just because of power failure is very bad.

I got the sqlite database file from our customer and removed password (we use encryption with password). It can be done in VS “Server Explorer”: just create a new connection and then choose “Change password” and enter blank one.

Then I checked that there’s really problems — execute “integrity check”:

PRAGMA integrity_check;

Usually this command should return “ok” but not is this case. It returned:

*** in database main ***
On page 9 at right child: invalid page number 8678
Page 7881 is never used
..
Page 7980 is never usedSo, something’s broken.

I executed a query like “select * from {table}” for each table in the database and all of them was executed successfully except one. This one caused the exactly same error I saw before -”database disk image is malformed”. But in spite of error I got data from this table.

So if I can read data then I can recreate my database. I started searching for a way how to dump data from the database into a SQL-script with INSERTs.

I downloaded SQLite shell. This tool allows to export data in many forms including INSERTs.I run sqlite3.exe storage.data where “storage.data” is my corrupted database file.

The following script exports all content (as INSERTs) with schema into “dump_all.sql” file:

sqlite> .mode insert
sqlite> .output dump_all.sql
sqlite> .dump

Then exit (Ctrl-C, Ctrl-Z or “.exit”) and run again:sqlite3.exe storage.fixed.data,where “storage.fixed.data” is name of non-existing file for my resurrected database.

The following script reads commands from “dump_all.sql” script file and recreates all database.

sqlite> .read dump_all.sql

After this procedure I got a new database file with all content from the corrupted file.