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

Описание и применение

В статье описывается, как восстановить удалённые строки в SQL Server без включения CDC или Audit, используя инструмент LogCarver, который читает fn_dblog и восстанавливает историю изменений таблицы.

Задача

Recover deleted rows from SQL Server when CDC, Change Tracking, or Audit were never enabled.

Результат

Using LogCarver, deleted rows were recovered without any prior change‑capture configuration, demonstrating that the transaction log can be leveraged for data recovery even when CDC or Audit are disabled.

Рабочий процесс

• 1. Connect to the SQL Server instance and run LogCarver against the target database.
• 2. LogCarver queries fn_dblog to read the transaction log entries for the target table.
• 3. LogCarver parses the raw fn_dblog output, reconstructing before/after row images for INSERT, UPDATE, and DELETE operations.
• 4. The tool generates reviewable Undo and Replay SQL scripts that can restore the deleted data.
• 5. Execute the generated scripts to recover the lost rows.

Доказательства

SQL Server's transaction log already records every change.

Источник

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.

Источник

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.

Источник

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.

Источник