Recovering data from SQLite .db File Data

dumprecoverysqlite

To give a bit of background, I was looking for the occurrence of specific text from within multiple SQLite DB files. I initially tried a simple grep of the text on the multiple files and grep found matches in one of the .db files.

# grep "mytext" *.db
Binary file sqlite.db matches

So I opened the sqlite.db file using a hex (note HEX) editor and searched for the same text. It was found in multiple spots and with the HEX editor, and as I can see text strings, I was able to actually see the text for which I was looking.

So I then opened the .db file using SQLite. The file had about three dozen tables. So I first tried (from within SQLite):

sqlite> .output sqlite.dbdump.sql
sqlite> .dump

…then…

# grep "mytext" sqlite.dbdump.sql

…no results were found…weird.

I went a step further and tried:

# echo ".tables" | sqlite3 sqlite.db | while read tablez
# do 
#  echo "select * from $tablez" | sqlite3 sqlite.db
# done | grep -i "mytext"

…no results were found…weird again.

Can anyone shed any light on where this matching text may be within the SQLite DB file? Are their hidden tables that my .dump and table selects may not be searching?

Since posting this, I believe those are deleted records. I now need to find the best way to attempt to recover instead of vacuuming.

Best Answer

Community wiki answer:

Is it possible that the text appears in deleted rows whose disk space has not yet been "recycled" or "garbage collected". If you vacuum the database and check again with grep, the text should be gone.

I now need to find the best way to attempt to recover instead of vacuuming.

To recover the data, instead of vacuuming, make a copy of the database as it is now, look for a hex editor, and see what you can salvage by hand.

See also Undelete accidentally deleted records in Sqlite3 on Stack Overflow.

Related Question