SQBrite is a data recovery tool for SQLite databases
SQBrite requires Python 3. To install, simply run:
$ pip3 install --user sqbrite $ sqbrite --help
SQLite uses a paginated data model in which each database is a collection of same-size pages. There are several kinds of pages, of which one type (B-Tree Table Leaf pages) contains the starting point for actual data belonging to individual table rows.
When a row is deleted by means of a DELETE FROM table (...) statement, the space occupied by that row’s data (a record) within the relevant B-Tree Table Leaf page is marked as free and may subsequently be used to store new records or update existing records. However, it is common to see freed space within a page (a freeblock, in SQLite parlance) left alone after rows are deleted. In that case, it *may* be possible to retrieve deleted row data from within the freeblock.
The SQLite file format doesn’t keep track of where deleted records start and end within a leaf page’s freeblocks. This means that SQBrite needs a mechanism to find out where record headers start. This is achieved through the use of byte-wise regular expressions specific to tables in known databases. These regular expressions and the offset that separates matches from the first byte in a well-formed header are stored in a user-editable YAML file.
SQBrite aims to ship with heuristics for popular SQLite databases, so do send pull requests if you’ve got good results with your heuristics.
Export all records to CSV or reinject “undeleted” records into a copy of the database
Extensible heuristics - just add entries to ~/.local/share/sqbrite/sqbrite.yaml!
SQBrite can recover records from within active B-tree table leaf pages or from former table-leaf Freelist pages.
Heuristics for iOS and Firefox databases
SQBrite works better when ptrmap pages are present
The undelete subcommand may fail when re-inserting deleted rows into a table causes a constraint violation
SQBrite cannot recover records deleted with the SQLite secure_delete pragma enabled
Recovering data from overflow pages that have become Freelist leaf pages is not currently supported
Big thanks to @tobraha for contributing bugfixes in 2022.
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.