How I Recovered Deleted SQL Server Rows Without Ever Enabling CDC or Audit

Derek Cai — Tue, 29 Sep 2026 05:57:28 +0000

Most data-recovery tools assume you turned on Change Data Capture, Change Tracking, or Audit before the incident happened. In the real world — small and mid-sized companies running their own SQL Server — almost nobody does that. By the time someone notices bad data, it's already too late to turn those features on retroactively.

SQL Server's transaction log already records every change. The problem is fn_dblog, the function that reads it, is almost entirely undocumented — the row image byte layout isn't published anywhere. The only way to figure it out was to run real experiments against a live database and reverse-engineer the format from actual output, byte by byte.

That's what became LogCarver: a tool that reads fn_dblog directly and reconstructs a table's full INSERT / UPDATE (before & after values) / DELETE history, with reviewable Undo and Replay SQL — no prior setup required.

Two bugs that only showed up on real data

After shipping it, I tested it against a perfectly ordinary table — one with no clustered index (a Heap table, which is also what you get from a PRIMARY KEY NONCLUSTERED). Result: 0 events found. Worse, the tool's own error message sounded reasonable — it said the VLF had probably been recycled and the data was likely gone.

That explanation was wrong.

Digging into fn_dblog's raw output, the real cause was much simpler and much worse: row operations on Heap tables are tagged with LCX_HEAP in their Context field, but my filter only recognized LCX_CLUSTERED and LCX_MARK_AS_GHOST. Every single Heap-table record had been silently excluded from the start — nothing to do with VLF recycling at all. If I hadn't gone back to the raw fn_dblog output, I would have believed my own tool's plausible-sounding, wrong explanation and moved on.

Fixing that surfaced a second bug: any table with any extra index — a NONCLUSTERED PRIMARY KEY on a Heap table, or just an ordinary index someone added for query performance — broke the table-matching logic, which guessed at the right AllocUnitName string pattern from the table name. An index's own internal maintenance records use a similar naming pattern, so they got matched too, mixing two completely unrelated pieces of internal SQL Server bookkeeping together.

The fix: stop guessing at string patterns. Query sys.indexes for the table's actual storage structure (index_id 0 = Heap, 1 = clustered index), build the exact AllocUnitName from that, and match exactly instead of loosely. The same missing index_id filter existed in the schema-reading query too — once fixed, extra indexes stopped polluting the column layout.

The rule that held throughout: every assumption gets checked against a real SQL Server instance, never just inferred from docs or intuition — because for fn_dblog, there mostly aren't any docs. None of these three bugs were found by reading documentation; all three were found by connecting to a real database and looking at what fn_dblog actually returned. I reran the same test matrix against SQL Server 2016, 2019, and 2022 the same day to confirm the fix held across versions before calling any of them "validated."

Current limitations: only int, datetime2, char/nchar, varchar/nvarchar are decoded so far — decimal/money are still on the list. TRUNCATE TABLE is currently (over-cautiously) treated like a schema change, which hides events before it; known, not yet fixed.

LogCarver is MIT-licensed: github.com/caiderek/LogCarver

DEV Community: Derek Cai

How I Recovered Deleted SQL Server Rows Without Ever Enabling CDC or Audit