Deleted rows in SQLite are not gone the moment you hit delete. The database marks the space free but leaves the bytes on disk. They survive until something overwrites them. That window is your opportunity. Act before new writes, a VACUUM, or secure_delete wipes them. This guide covers what keeps deleted data alive, what destroys it, and exactly how to run the recovery tools that can bring it back.

How SQLite Handles Deleted Rows Internally

SQLite marks deleted space free inside a page as a freeblock. When an entire page empties, it goes onto the freelist, a linked list of pages ready for reuse. The original row data stays readable from the database file until new data claims that space. The bytes linger. Two pragmas control what happens next.

PRAGMA secure_delete and PRAGMA auto_vacuum determine whether old bytes survive. The default behaviour leaves them intact. That is why recovery is possible at all.

What Destroys Deleted Data

Several actions wipe deleted rows permanently.

  • PRAGMA secure_delete: set to ON, SQLite overwrites deleted content with zeros immediately. Recovery is impossible if this pragma was active at deletion time.
  • VACUUM: rebuilds the database into a new file, discarding free pages and deleted content entirely. After a VACUUM the original rows are gone.
  • auto_vacuum mode: when enabled, SQLite can release freed pages and truncate the file automatically. Larger deletions accelerate the destruction.
  • New writes: any INSERT, UPDATE, or DELETE that needs space reuses freeblocks or freelist pages. Overwritten bytes mean no recovery.

Stop writing to the database immediately. Every new write risks the data you want back.

Using the Sqlite3 Shell to Recover Deleted Rows

The sqlite3 shell includes a .recover dot-command. It reads every page in the database file, freelist pages included, extracts all recognizable records, and writes them to a new database.

What .recover Can Return

Orphaned rows that cannot be assigned to an existing table land in a table called lost_and_found. This works when the schema is intact and no new data has overwritten the freed space. Rows still sitting on freelist pages get recovered here.

What .recover Cannot Return

If PRAGMA secure_delete was ON, the bytes are zeroed. Gone. If VACUUM or auto_vacuum already released and truncated those pages, the data does not exist in the file. The .recover dot-command targets corrupted databases, not deleted rows specifically, so it may miss some records. Repair of a corrupted database prioritizes live rows and does not guarantee deleted ones come back.

Step by Step Attempting Recovery Yourself

Work on a copy. Never touch the original file.

  1. Stop all writes. Close every application that might write to the database. Copy the file to a safe location.
  2. Open the copy in the sqlite3 shell. Run sqlite3 damaged.db where damaged.db is your copy.
  3. Run .recover. Type .recover recovered.db to extract all possible records into a new file.
  4. Check the output. Open recovered.db and inspect the tables. Examine any rows placed in lost_and_found.
  5. Compare with a backup. If you have one, check what the recovery restored against it.

This works best when deleted rows still sit on freelist pages and nothing has overwritten them. If the database was already corrupted, .recover may find live rows but rarely returns deleted ones.

When Recovery Tools Fail

Recovery is never guaranteed. Here are the reasons tools come back empty.

  • secure_delete was ON: the bytes were overwritten with zeros at deletion time.
  • VACUUM or auto_vacuum ran: free pages containing old data were discarded and the file truncated.
  • New data overwrote the space: any write after deletion can reuse freeblocks or freelist pages.
  • The database was corrupted: structural damage forces recovery tools to focus on live rows, not deleted ones. Repair prioritizes what is still reachable.

No tool can reliably recover rows in these cases. Regular backups are the only sure defence against data loss.

Online Options and Final Advice

If you prefer to avoid command-line tools, upload your database to a service like sqlite.repair. These services focus on repairing structure and extracting live rows. They are not built for recovering deleted records. The same limits apply: overwritten or vacuumed data is gone.

Act immediately for the best chance. Stop writes to the database, make a copy, and run the .recover dot-command or a similar tool. Success depends entirely on whether the freed space remains untouched.