SQLite Database Recovery (Deleted Rows, Disk Image Scan)

About the SQLite Database Recovery (Deleted Rows, Disk Image Scan)

A SQLite database is a single file — often with one or two more beside it: name-wal, its write-ahead log, and name-journal, its rollback journal. Web browsers such as Firefox, Chrome and Safari, and countless other programs, keep their data in such files (SQLite). When a row is deleted, SQLite does not normally erase it: it marks the space as available for reuse and moves on (SQLite’s VACUUM notes), so the row stays in the file until new data happens to land on it.

This page reads the database page by page with its own reader of SQLite’s file format, so a damaged page costs only the rows on it. It replays the write-ahead log as SQLite would, runs SQLite’s own integrity check — SQLite itself, compiled to WebAssembly, in your browser — and looks for deleted rows wherever the format leaves them: in the unused space inside each table page, on the pages the database has set aside as unused, in older copies of pages in the log, and in the rollback journal. Every row found is checked against its table’s columns and its other rows, and marked sure, likely or partial; a row that turns out to be an older version of one still in the table is marked as one, not as deleted. Everything goes into a new database — the live rows in their own tables, the rows found beside them in tables of their own — and into CSV files. For a database that was deleted, or one on a card or drive that was formatted, choose a disk image: the page searches it for databases by their header. Nothing is uploaded.

Common uses

• Rows deleted by mistake from an app’s database: a note, a contact, an entry, a message.
• A database that will not open, with “database disk image is malformed” or “file is not a database”.
• A database left with its ‑wal or ‑journal file after a crash or a power cut.
• A database that was deleted, or was on a card or drive that was formatted: search a disk image of it.
• Before sharing a database: see which deleted rows it still holds, and remove them with VACUUM.

Where deleted rows stay in a SQLite file

A database file is a series of pages of one size, and each table is a tree of them (SQLite’s file format). Deleted content stays in four places:

• Inside a table page. A deleted row’s space becomes a freeblock: its first four bytes are overwritten with the size of the space and a link to the next one, and the rest of the row stays as it was. Rows can also be left whole in the unused space between a page’s list of rows and the rows themselves.
• On freelist pages. Pages a table no longer needs go on a list of unused pages for later reuse. SQLite does not read or write the leaf pages of that list, so a whole page of deleted rows keeps them until the page is used again.
• In the write-ahead log. Each frame of the log is a new copy of one page; a page changed several times has several copies, and the older ones hold rows as they were.
• In the rollback journal. It keeps the pages a transaction changed as they were before it, so rows deleted in that transaction are in it.

What overwrites them: new rows reusing the space, PRAGMA secure_delete, which writes zeros over deleted content, and VACUUM, which rebuilds the whole file (SQLite’s pragmas).

How each row found is checked

Deleted space is full of fragments, so a row is kept only when it fits its table:

• its record must decode within the space it was found in — a whole row to exactly the length it states — with as many values as the table has columns (or a few fewer, for columns added later);
• each value must suit its column: a TEXT column never stores a number, a NOT NULL column never stores NULL, and an INTEGER PRIMARY KEY is stored as NULL because it is the rowid;
• its text must decode cleanly;
• it must look like the table’s other rows: a record with NULL, or another kind of value, in two or more columns where none of the table’s rows has one is a fragment that happens to decode, and is left out.

Then it is marked sure (a whole row with its rowid), likely (a row found in a freeblock: its values are whole, but its rowid was among the four bytes overwritten), or partial (part of a row: its overflow pages are gone, or it holds only one value). A row that matches one still in the table is left out.

Not every old row was deleted: when a row is changed, SQLite writes it anew and its old version is left behind like a deleted one. A row found with the rowid of a row still in the table, or one that shares with such a row a value no other row has (a timestamp, an id) and at least one more value, is marked as an older version of it. Rows on pages that no table links to any more — after damage to the page that pointed to them — are kept apart as cut off from their table: they may well still be current, so they are never called deleted.

The write-ahead log and the rollback journal

In WAL mode SQLite appends changes to the ‑wal file and copies them into the database only at a checkpoint (SQLite) — by default when the log reaches 1,000 pages, and when the last connection closes. Until then, the newest rows are only in the log. The page checks each frame’s salts and checksums, replays the committed frames over the database as SQLite does when it opens it, and reads the older copies of pages and any frames that were never committed for rows that are no longer there.

A rollback journal holds the original pages of the last transaction. In PERSIST mode SQLite zeroes the journal’s header instead of deleting the file (SQLite); the page then reads its pages by the database’s page size.

Databases inside a disk image

Every SQLite database starts with the same 16 bytes, “SQLite format 3” and a zero byte, followed by the rest of a 100-byte header. File systems store a file’s data in whole clusters, so the page looks for that header at every 512-byte boundary of the image, a few megabytes at a time, and lists each database it finds with its page size and the tables named on its first page.

The header gives the database’s size in pages, and SQLite’s format says when that count can be trusted: when it is not zero and the change counter at offset 24 matches the number at offset 92. Then exactly those pages are read; otherwise the database is taken to run to the next database found or the end of the image, up to 1 GB. A database the file system stored in pieces is read only as far as its first piece, and the recovery reports the pages it could not read.

What the recovery is measured against

The page’s automated tests use databases written by SQLite itself — through sql.js in the tests and Python’s sqlite3 for the log and journal files — with rows deleted, a page overwritten, the header destroyed, the first page destroyed, rows in a write-ahead log, deleted rows only in a PERSIST journal, and databases inside a disk image. Every row the page calls deleted must be one that really was deleted: a recovery that invents a row fails. The rebuilt database must open in SQLite with every live row in it.

SQLite’s own documentation is frank about salvage: a recovered database is usually defective, and recovered values may be altered (SQLite). Treat what comes back as evidence to check, not as a database to put straight back into use. No success rate is published for other people’s databases: what comes back depends on what has been overwritten since.

Limitations

• Rows that new data, secure_delete or VACUUM overwrote cannot be recovered. With secure_delete set to FAST, traces may remain on freelist pages only.
• The row at the start of a freeblock has lost its first four bytes to the freeblock’s header: its rowid is gone, and when more of it was overwritten it is not found at all.
• Encrypted databases (SQLCipher and others) cannot be read without their key, and this page never tries to guess one.
• Databases of up to 1 GB are read. Above 400 MB SQLite’s own check is not run and no new database is written; every table is still saved as CSV.
• Deleted rows are looked for in ordinary tables; tables declared WITHOUT ROWID give their live rows only.
• A row found without its rowid that was changed rather than deleted is recognised as an older version only when it still shares a value that is unique in its table (a timestamp, an id) with the row it became; otherwise it is listed as deleted.
• Without the schema (when the first page is destroyed) rows are grouped by their number of columns, and their tables’ names are lost.
• Disk images must be raw images; compressed or encrypted ones (E01, sparse VMDK or VHDX files, BitLocker or FileVault volumes) must be converted or unlocked first. A database stored in pieces is read only as far as its first piece.
• What a row means — which message, which contact — depends on the app that wrote it; the page shows the values as they are stored.

Privacy

The database, its log and journal, and any disk image are read by your browser on your device; nothing from them is sent anywhere, and the page works offline once it has loaded. Use it on databases you own or may examine.