How to Recover Deleted Records in SQL Server

The moment after a DELETE runs without a WHERE clause is a bad one, and what you do in the next few minutes decides how much comes back. SQL Server has no button that undoes a committed delete, but the rows are rarely gone the instant they vanish from a query. They sit in a backup, in the transaction log or still in the data file itself, and recovery is a matter of reaching whichever one still holds them before something else does.

We build SQL recovery tools at Univik and have pulled deleted rows back out of MDF files since 2013, often for people who had no backup and only realised the delete was wrong an hour later. The paths below run from least effort to most, your recovery model and backups decide which one is open to you, and one habit in the first sixty seconds matters more than any of them.

First, Stop Writing to the Table

Before any recovery method, stop the writes. This is the step people skip in a panic, and it is the one that quietly closes doors. When rows are deleted, SQL Server does not wipe them immediately. It marks their space as free, and that space stays readable until the next write reuses it. Every insert, update and even the checkpoint that follows can land on top of the rows you are trying to save.

Take the application offline, or set the database to single user so only you are connected. On a busy production system every second of continued traffic is another chance for the deleted rows to be overwritten, and no recovery method can bring back space that has already been reused.

What Decides Whether You can Get Them Back

Two things set your options before you try to recover deleted data, the recovery model and what backups exist. Check the recovery model first, because it decides whether the transaction log can help you at all.

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = N'YourDatabase';

In the full recovery model, the log holds every change until a log backup clears it, which is what makes a precise restore possible. In the simple recovery model, the log is reused after each checkpoint and log backups are not available, so the transaction log is not a route and you fall back on the last full or differential backup, or on reading the data file directly. Knowing which model you are in tells you which of the sections below applies.

Restore to Just Before the Delete

When the database is in the full recovery model and you have an unbroken chain of log backups, this is the cleanest recovery there is. You restore a copy of the database to the moment just before the delete using a point in time restore, then lift the rows out. Restore to a new database rather than over the top of production, so the live system stays untouched while you work.

-- Restore a full backup from before the delete, leaving room for log backups
RESTORE DATABASE YourDatabase_recovered
  FROM DISK = 'D:\Backups\YourDatabase_full.bak'
  WITH MOVE 'YourDatabase' TO 'D:\Data\YourDatabase_recovered.mdf',
       MOVE 'YourDatabase_log' TO 'D:\Data\YourDatabase_recovered.ldf',
       NORECOVERY;

-- Replay log backups, stopping just before the moment of the delete
RESTORE LOG YourDatabase_recovered
  FROM DISK = 'D:\Backups\YourDatabase_log.trn'
  WITH STOPAT = '2026-07-07 14:29:59', RECOVERY;

The STOPAT value is the last safe second before the delete ran. If several log backups sit between the full backup and the delete, restore them in order with NORECOVERY and apply STOPAT only on the one that holds your target time. If you know the exact log sequence number of the delete, STOPBEFOREMARK with that LSN stops even more precisely, right before that one transaction. Once the copy is online, copy the recovered rows back into the live table with an INSERT, and production never went offline. This side by side approach is the method Microsoft points to, since SQL Server has no supported way to undo a single committed transaction in place.

Read the Deleted Rows From the Transaction Log

When there is no clean restore point but the database is in the full recovery model, the deleted rows are still recorded in the transaction log, and they can be read back. A DELETE is fully logged, so each removed row leaves a log record marked as a delete operation. The undocumented function fn_dblog reads the active log, and fn_dump_dblog reads log backups, both letting you find the delete and the rows it removed.

-- Find delete operations in the active transaction log
SELECT [Current LSN], Operation, [Transaction Name], [Begin Time]
FROM fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS';

Be honest with yourself about the effort here. The log stores rows in a raw form spread across many columns, so reconstructing them by hand is slow and error prone, and it is realistic only for a small number of rows. For anything larger, a dedicated transaction log reader, or a point in time restore where a backup chain exists, is the practical route. One important limit, this works for a DELETE but not a TRUNCATE, because a truncate deallocates whole pages and does not log the individual rows.

The log does more than show the rows, it hands you the exact point to restore to. Take the transaction ID from the delete above, find where that transaction began, and read its log sequence number.

-- Find where the delete transaction began, to read its LSN
SELECT [Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction SID]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:000002f1'
  AND Operation = 'LOP_BEGIN_XACT';

That LSN comes back in the hexadecimal form fn_dblog uses, and it feeds straight into a restore that stops the instant before the delete, using the lsn:0x prefix.

-- Restore a copy to just before that transaction
RESTORE LOG YourDatabase_recovered
  FROM DISK = 'D:\Backups\YourDatabase_log.trn'
  WITH STOPBEFOREMARK = 'lsn:0x0000002f:000001d0:0001', RECOVERY;

The Transaction SID column in that same output points to the login behind the delete, so the log answers who as well as when. For a higher level view of who changed what, the Schema Changes History report in Management Studio lists recent schema level actions and the users who ran them.

Recover Deleted Records From the MDF File

The hardest case is the common one, a database in the simple recovery model with no recent backup, where neither a restore nor the log can help. Here the only place the rows still exist is the data file, in the space that was marked free but not yet overwritten. This is why stopping writes early matters so much, because that space is exactly what new activity reuses.

A file level recovery tool reads the MDF directly, page by page, and finds the deleted rows still physically present in it. The Univik SQL Database Recovery tool opens the MDF without attaching it to SQL Server, scans the pages, and shows recoverable deleted records so you can export them or write them back to a working database. It recovers dropped tables the same way, which the log cannot rebuild. Because it reads the file rather than replaying the log, it does not need the full recovery model or a backup chain, only rows that have not yet been overwritten. The free MDF Viewer lets you look inside the file first, and our guide on recovering a SQL database without a backup covers the wider no backup situation this belongs to.

Stopping Accidental Deletes From Happening

The recovery you never have to do is the cheapest one, and a few habits turn the next bad delete into a quick fix instead of a crisis. Wrap risky statements in a transaction so a wrong result rolls back before it commits, and get in the habit of writing the WHERE clause before the DELETE keyword.

BEGIN TRANSACTION;
DELETE FROM YourTable WHERE Id = 42;
-- Check the row count is what you expected, then:
COMMIT TRANSACTION;   -- or ROLLBACK TRANSACTION if it is wrong

Beyond that, put the database in the full recovery model and take regular log backups, since that alone turns most accidental deletes into a clean point in time restore. For tables that hold data you cannot afford to lose, temporal tables keep a full history of every row automatically, and change data capture records every modification, both letting you recover a deleted row without touching the log by hand. None of these help after the fact, which is the whole point of setting them up before.

Frequently Asked Questions

Can I Recover Deleted Records in SQL Server Without a Backup?

Sometimes, depending on the recovery model and how much has happened since. In the full recovery model, the transaction log may still hold the deleted rows. In the simple recovery model with no backup, a file level tool that reads the MDF directly, such as Univik SQL Database Recovery, can recover deleted rows still present in the data file, as long as new writes have not overwritten them. Acting quickly is what keeps that option open.

How do I Recover Rows After a DELETE Without a WHERE Clause?

Stop writing to the table immediately, then check your recovery model. With the full recovery model and log backups, restore a copy of the database to just before the delete using RESTORE LOG with STOPAT, then copy the rows back. Without a usable backup, read the transaction log or the MDF file to recover the rows. The faster you stop activity, the more you get back.

Does the Recovery Model Affect Deleted Data Recovery?

Yes, it is the single biggest factor. The full recovery model keeps every change in the log until it is backed up, which allows a point in time restore and log reading. The simple recovery model reuses the log after each checkpoint and allows no log backups, so recovery there depends on a full or differential backup, or on reading the deleted rows directly from the data file.

Can I Recover Rows Deleted by a TRUNCATE Statement?

A point in time restore recovers a truncate, but reading the transaction log does not. A DELETE logs each row, so the log holds the rows, while a TRUNCATE only deallocates whole pages and does not log the individual rows. For a truncate with no backup, a file level read of the MDF can still find rows in pages that have not been reused yet, since the data is not wiped by the truncate itself.

How Long do I have to Recover Deleted Rows?

There is no fixed window, only the amount of activity since the delete. The deleted rows stay recoverable until their space in the data file or their records in the log get reused by new writes. On a quiet database that can be a long time, on a busy production system it can be minutes. This is why taking the table or database out of use is the first thing to do.

What is the Best Way to Recover a Large Number of Deleted Rows?

A point in time restore is the most reliable for large recoveries, since it brings back every row as it was with no manual reconstruction. Reading the transaction log by hand suits only a handful of rows because the format is complex. When no backup chain exists, a file level recovery tool that reads the MDF handles volume far better than manual log reading, and it does not depend on the recovery model.

The Bottom Line

Recovering deleted records in SQL Server is a race against the next write. The rows are almost never gone at once, they linger in a backup, the transaction log or the data file, and the job is to reach one of those before new activity overwrites it. So the first move is always the same, stop writing to the table, then check your recovery model to see which path is open.

With the full recovery model and log backups, restore a copy to just before the delete and lift the rows out, which is clean and complete. Without that, read the log for a small delete, and when there is no backup and the log cannot help, read the MDF directly for the rows still sitting in its pages. And once the crisis passes, set up the transactions, log backups and history features that turn the next accidental delete from an emergency into an undo.