A couple days ago I’ve been working with an sqlite3 database and noticed that after deleting a lot of data (an entire table (
DROP) to be exact) the file size did not change (the table used 5MB) at all. Not the database I’m asking about, but maybe this is relevant to my question.
I’m having a bunch of databases that I was looking through using
grep -a for a specific message, I successfully found the database in question having 50MB in size, seems perfect! However, opening the database in an sqlite3 database viewer (or the
sqlite3 command line tool) shows only a little over 800 entries in the
messages table. The current auto increment index is above 18.000, which is the amount of entries the table should have. Going through the .db file with
grep, everything I want seems to be there, but I’m guessing it’s somehow “hidden”(?).
How can I retrieve ALL the data from an sqlite database? Note that
.dump <table> does also not include the messages I’m looking for. For a quick explanation on this and why data doesn’t actually get deleted/filesize won’t shrink when I delete a 5MB table I’d be thankful too.
sqlite> .dbinfo database page size: 4096 write format: 1 read format: 1 reserved bytes: 0 file change counter: 366 database page count: 11405 freelist page count: 10372 schema cookie: 2 schema format: 4 default cache size: 0 autovacuum top root: 0 incremental vacuum: 0 text encoding: 1 (utf8) user version: 7 application id: 0 software version: 3008005 number of tables: 3 number of indexes: 1 number of triggers: 0 number of views: 0 schema size: 737
(Note that I did not create nor ever edited this database, it’s Instagram’s direct messages database.)
Deleted data is not immediately removed from the database file; see change sqlite file size after “DELETE FROM table”.
As shown by the freelist page count, that file is mostly empty, and running VACUUM is likely to throw away the remains of the data you’re searching for.
There is no easy way to recover deleted data (because some important management information is missing). If you are interested in only a small amount of data, try a hex editor.