CODEWITHSUNDEEP
linuxdatabasebashsqlite
confetti
confetti

Reputation: 1100

How to retrieve ALL data from an sqlite database?

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.)

Upvotes: 3

Views: 2222

Answers (1)

CL.
CL.

Reputation: 180080

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.

Upvotes: 2

Related Questions