There are three files associated with database which may contain deleted records.

1. The database file - <database name> (https://www.sqlite.org/fileformat2.html#section_1)
2. The rollback journal - <database name>-journal (https://www.sqlite.org/fileformat2.html#section_3)
   
3. The write-ahead log - <database name>-wal (https://www.sqlite.org/fileformat2.html#section_4)

How the recovery works

Basic recovery method 

The basic approach is described in many documents. Sqlite B-Tree is parsed and Freeblocks and Unallocated blocks are detected. 
We know which table blocks belong to, so we know the data types of item columns that should be recovered. Data in each block (Freeblocks and Unallocated blocks) is read sequentially.
Each potential item found in the database, has header where there are data types and lengths of incoming data, so we read the whole block of data as if it could be considered a header. If it fits the table data types it is probably a deleted item.

Example of getting deleted record

Source database: orca2.db 

1. Find  number of first freelist trunk page and number of freelist pages in header (https://www.sqlite.org/fileformat2.html#section_1_2)
   
   
   
   
2. Get address of freelist trunk page ( (pageNumber - 1) * pageSize) and parse header (https://www.sqlite.org/fileformat2.html#section_1_4)
   
   
   
   
   
3. Process all freelist pages (get addresses and  parse headers) (https://www.sqlite.org/fileformat2.html#section_1_5)
   
   
   
   
   
4. Parse record header to get values (https://www.sqlite.org/fileformat2.html#section_2_1)
   All values are in Varint(Variable-Length Integer) format (https://sqlite.org/src4/doc/trunk/www/varint.wiki)
1. 87h 3Ch - Record size (956)
2. 81h 03h - Row ID (131)
3. 06h - Header size
4. 55h - Text, size = (85 - 13) / 2 = 36
5. 2Bh - Text, size = (43 - 13) / 2 = 15
6. 05h - BigInt (48-bit)
7. 8Eh 06h - Blob, size = (1798 - 12) / 2 = 893
   
   
5. 
   
   

Recovered records may be corrupted, incomplete or duplicate of existing record.

Recovery filtering

We are trying to filter all duplicate or incomplete records. It does not currently have a success rate of 100% but we are always looking at ways to improve features. So expect an increased success rate in future updates.

How it works???

• For each processed table in database is defined set of columns.
• Each recovered record is compared (according to set of columns) with all valid records and all previous recovered records.
• Depending on result of comparison the record is processed (duplicates are thrown away).

Deleted files

Deleted files can be extracted from physical dump (Android only).