SQL Server has no undo button for a deleted row but a SQL query to find deleted records in SQL Server works four ways. A history table or a full backup works only if it existed before the delete. The transaction log shows the delete only while its records are still in the log. Recovery software scans the data file and shows deleted records in red. Always test a query on a copy of the database first.
Write a SQL query to find deleted records in SQL Server only after you know what happens to the deleted rows when you execute a DELETE statement with a wrong WHERE clause. Three quick methods like temporal table query, Backup copy comparison, and transaction log query with fn_dblog can be used to find the deleted rows in the SQL Server.
To skip technical steps, you can also use a verified software to recover the deleted records in SQL.
Every step below has the exact T-SQL. You learn how to trace the lost rows and put them back in the table.
What Happens to Deleted Rows After a DELETE?
Deleted rows disappear from the table but the data survives in four places.
- Temporal History Table. A system versioned table copies every deleted row into its history table.
- Backup Files. A full backup stores the rows as they were when the backup finished.
- Transaction Log. The log records every delete while those log records exist.
- MDF File. Recovery software scans the data file and shows rows that the table lost.
SQL Server has no recycle bin for rows. Choose the place that fits your setup from the methods below.
What to Check Before a SQL Query to Find Deleted Records in SQL Server?
Before executing any query, you need to check these five facts:
- Note the date and the time of the delete.
- Note the database name and the table name.
- Find your last full backup and every log backup after it.
- Check the recovery model of the database with the query below.
- Check free disk space for a copy of the database.
SELECT name, recovery_model_desc
FROM sys.databases;
Microsoft states that point in time restore works in the full recovery model and that the log grows until you back it up. No log backups and no point in time restore exist in the simple model. SQL Server Express uses the simple model by default.
Never test a restore on the live database. Restore the backup under a new name so the live data remains safe.
Which Free Methods Trace Deleted Rows in a Table?
Three free methods give you a SQL query to find deleted records in SQL Server through SQL Server Management Studio (SSMS). Method one works only when the table was ready before the delete.
Temporal Table Query (Free, Needs SQL Server 2016 or Later)
System versioned temporal tables move a row into the history table when you delete it. Microsoft sets the ValidTo value of that history row to the start time of the delete transaction in UTC. Ordinary queries on the table do not return the row anymore.
- Click New Query in SSMS.
- Check that the table is a temporal table. Use the query below with your table name.
SELECT name, temporal_type_desc
FROM sys.tables
WHERE name = N'Orders';
- Look for the SYSTEM_VERSIONED_TEMPORAL_TABLE value. Microsoft lists a regular table as NON_TEMPORAL_TABLE and this method does not apply to it.
- List the rows that exist in the history but not in the table with the query below.
SELECT h.*
FROM dbo.Orders FOR SYSTEM_TIME ALL AS h
WHERE NOT EXISTS (
SELECT 1 FROM dbo.Orders AS o
WHERE o.OrderID = h.OrderID)
ORDER BY h.ValidTo DESC;
Replace OrderID with the primary key column of your table. Rows with several old versions appear several times so the highest ValidTo value shows the delete time in UTC.
- Copy one row back with an INSERT statement. Omit the ValidFrom and ValidTo columns from the column list and select the newest version with TOP and ORDER BY.
INSERT INTO dbo.Orders (OrderID, CustomerID, OrderDate, Amount)
SELECT TOP (1) h.OrderID, h.CustomerID, h.OrderDate, h.Amount
FROM dbo.Orders FOR SYSTEM_TIME ALL AS h
WHERE h.OrderID = 1042
ORDER BY h.ValidTo DESC;
Add SET IDENTITY_INSERT dbo.Orders ON before the INSERT when OrderID is an identity column. Switch it OFF after the INSERT.
Microsoft states that temporal tables apply to SQL Server 2016 and later. Versioning that you add after the delete has no history of that delete.
Backup Copy Comparison (Free, Needs a Full Backup)
Restore your backup under a new name and compare it with the live table. Any row that exists in the copy but not in the live table is a deleted record. Rows that you added and deleted after the backup do not exist in the backup.
- Find the logical file names inside the backup with the command below. Enter your backup path.
RESTORE FILELISTONLY
FROM DISK = N'D:\Backup\SalesDB_Full.bak';
- Restore the backup as a new database. Use the logical names from step 1 in the MOVE clauses.
RESTORE DATABASE [SalesDB_Copy]
FROM DISK = N'D:\Backup\SalesDB_Full.bak'
WITH MOVE N'SalesDB' TO N'D:\RecoverDB\SalesDB_Copy.mdf',
MOVE N'SalesDB_log' TO N'D:\RecoverDB\SalesDB_Copy_log.ldf',
RECOVERY;
- Paste the query below to list the rows that the live table lost.
SELECT c.*
FROM SalesDB_Copy.dbo.Orders AS c
WHERE NOT EXISTS (
SELECT 1 FROM SalesDB.dbo.Orders AS o
WHERE o.OrderID = c.OrderID);
- Copy the missing rows into the live table.
INSERT INTO SalesDB.dbo.Orders (OrderID, CustomerID, OrderDate, Amount)
SELECT c.OrderID, c.CustomerID, c.OrderDate, c.Amount
FROM SalesDB_Copy.dbo.Orders AS c
WHERE NOT EXISTS (
SELECT 1 FROM SalesDB.dbo.Orders AS o
WHERE o.OrderID = c.OrderID);
Microsoft states that REPLACE overrides safety checks so never add it to this restore. Our Restore SQL Database From BAK File guide explains the SSMS restore window.
Transaction Log Query With fn_dblog (Free, Needs sysadmin)
Use fn_dblog to list the records of the transaction log. Every delete creates a LOP_DELETE_ROWS record and the LOP_BEGIN_XACT record of the same transaction shows the start time. You then restore a copy that stops just before that time.
Microsoft does not document fn_dblog and the function needs the sysadmin role. Only the active part of the log appears in the output.
In the full recovery model a log backup clears the records it shows and in the simple model a CHECKPOINT clears them. Use a test server and query the log before you back it up.
- Click New Query in SSMS and select your database.
- List the delete records of one table with the query below.
USE SalesDB;
GO
SELECT [Current LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM fn_dblog(NULL, NULL)
WHERE Operation = N'LOP_DELETE_ROWS'
AND AllocUnitName LIKE N'dbo.Orders%';
- Note the Transaction ID of the delete that you want to find.
- Use the query below with that Transaction ID. Output shows the start time and the login of the delete.
SELECT [Current LSN], Operation, [Transaction ID], [Begin Time],
[Transaction Name], SUSER_SNAME([Transaction SID]) AS LoginName
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:000001f3'
AND Operation = N'LOP_BEGIN_XACT';
- Back up the log after both queries so the delete records exist in a file.
BACKUP LOG [SalesDB]
TO DISK = N'D:\Backup\SalesDB_Log2.trn';
- Restore the full backup as a copy with NORECOVERY. Restore every log backup after it in creation order with NORECOVERY and STOPAT. Set STOPAT to the Begin Time from step 4 or a few seconds before it.
RESTORE DATABASE [SalesDB_Copy]
FROM DISK = N'D:\Backup\SalesDB_Full.bak'
WITH MOVE N'SalesDB' TO N'D:\RecoverDB\SalesDB_Copy.mdf',
MOVE N'SalesDB_log' TO N'D:\RecoverDB\SalesDB_Copy_log.ldf',
NORECOVERY;
RESTORE LOG [SalesDB_Copy]
FROM DISK = N'D:\Backup\SalesDB_Log2.trn'
WITH NORECOVERY, STOPAT = 'Oct 20, 2026 2:30 PM';
RESTORE DATABASE [SalesDB_Copy] WITH RECOVERY;
- Use the comparison query from the backup copy method to list the deleted rows in the copy and insert them into the live table.
Microsoft states that the recovery point is the last transaction that committed at or before the STOPAT time. You cannot stop inside a log backup that has bulk logged operations.
LoginName in the second query names the login that deleted the rows. Lost an entire table or a stored procedure? Follow Recover Deleted Objects of SQL Database for those steps.
Older guides back up the log before they query it. That order can clear the records the query needs so query the log first.
How to Recover Deleted Rows in SQL Server With EmailDoctor Software?
EmailDoctor SQL Database Recovery scans databases from SQL Server 2000 through 2019 and works with the MDF file and its NDF files. Software needs only an MDF file and no backup.
Free demo shows every recovered object. Demo export is limited to 25 records per table and 5 other objects.
Five features matter for deleted data.
- Advanced Scan. Select it for a database where Quick Scan does not show every object.
- Recover Deleted Objects. Tick the option in the scan window to include deleted tables and other objects.
- Red Preview. Deleted objects and deleted records appear in red.
- Export Deleted Records. Tick the option in the export window to send deleted records with the table data.
- Export Formats. Save results in a live SQL Server database or a SQL script or a CSV file.
Confirm the system needs before you install.
- Windows Versions. Desktop editions 10, 8 and 7 or server editions 2016, 2012 R2 and 2008.
- Free Space. 100 MB on the disk.
- RAM. 4 GB is recommended.
- .NET Framework. Install version 4.5 or newer first.
Preview Deleted Records of an MDF File on Windows
- Find the software in the Start menu and click it.
- Click Open in the toolbar and browse to your MDF file.
- Choose Advanced Scan and tick the Recover Deleted Objects box. Tick Auto detect so the software finds the version of the file.
- Untick Auto detect and select the version from the list when you know it. Open the NDF Options tab to add NDF files and click OK.
- Wait while the scan finishes.
- Click Yes to save the scan and save it as an STR file.
- Open the tables, views and stored procedures in the tree. Deleted objects and records appear in red.
- Click Export and select SQL Server Database.
- Choose a SQL Server name from the drop down list. Select Windows Authentication or enter your SQL login and password under SQL Server Authentication.
- Select Create New Database or Export To Existing Database.
- Type the name of the new database.
- Accept the default collation or choose another one from the list.
- Tick the tables and other objects that you want to export.
- Select With Schema & Data and tick Export Deleted Records. Tick Export Deleted Objects to export deleted tables and procedures too. Click Export.
- Click OK when Export Completed appears and close the status window.
- Open SSMS and expand Databases to see the new database. Use the comparison query from the backup copy method on it.
Export deleted records only into a new database first. Compare the rows with your live table before you copy them back.
Which SQL Query to Find Deleted Records in SQL Server Suits You?
All four methods trace deleted rows but they differ in setup and output.
| Feature | Temporal Table Query | Backup Copy Comparison | fn_dblog | Recovery Software |
|---|---|---|---|---|
| Needs setup before the delete | Yes | No | No | No |
| Needs a backup | No | Yes | Yes for the restore | No |
| Shows delete time | Yes | No | Yes | No |
| Shows the login | No | No | Yes | No |
| Needs sysadmin | No | No | Yes | No |
| Price | Free | Free | Free | Free demo (25 records per table) + Paid License |
Start with the temporal query when the table is system versioned. Use the backup copy when you only need the rows and use fn_dblog when you need the time and the login. Recovery software suits a database without a usable backup or log.
What Problems Appear During the Search?
You may come across six problems during the search:
- fn_dblog Returns No Rows. A checkpoint or a log backup cleared the records. Query the log backup file with fn_dump_dblog instead.
- Permission Error 9010. fn_dblog needs the sysadmin role.
- Restore Says the Database Exists. Give the copy a new name and new file paths.
- Log Restore Fails. A missing log backup breaks the chain. Restore every log backup after the full backup in creation order.
- INSERT Fails on Identity. Add SET IDENTITY_INSERT ON before the INSERT and OFF after it.
- INSERT Fails on a Temporal Table. Remove ValidFrom and ValidTo from the column list.
A damaged data file needs a repair first. Our Repair Corrupt SQL MDF File guide explains the steps.
Frequently Asked Questions
Can a SQL Query to Find Deleted Records in SQL Server Work Without a Backup?
Yes. Temporal history tables store the rows when versioning was on and fn_dblog shows delete records that still exist in the log. Recovery software checks the MDF file.
Does SQL Server Have a Recycle Bin for Deleted Rows?
No. Rows vanish from the table after a DELETE. Only a backup or a temporal history table or the transaction log still shows the data.
What Does LOP_DELETE_ROWS Mean?
LOP_DELETE_ROWS is the operation name of a delete record in fn_dblog output. Use the Transaction ID of that record to find the start time of the delete.
Can fn_dblog Show Who Deleted a Record?
Yes. LOP_BEGIN_XACT records have a Transaction SID and the SUSER_SNAME function converts it into a login name.
Does fn_dblog Work in the Simple Recovery Model?
Yes but only until a CHECKPOINT clears the records. Check the recovery model before you search.
Do I Need sysadmin to Use fn_dblog?
Yes. fn_dblog needs the sysadmin role. Other users get permission error 9010 when they call the function. Ask your database administrator to execute the query for you or to assign the role.
Does Microsoft Document fn_dblog?
No. Microsoft has no documentation for the function so use it on a test server and not on a live server.
Does the Temporal Table Method Work in SQL Server 2014?
No. Microsoft lists temporal tables for SQL Server 2016 and later.
Can I Restore Only the Deleted Rows?
Yes. Restore a copy of the database and insert only the rows that the live table lost.
Does the Software Need a Backup?
No. Software opens the MDF file directly and shows deleted records in red after an Advanced Scan.
Final Words
Deleted rows disappear from the table but the data survives in temporal history tables and in backups and in the transaction log. Your setup before the delete decides the right SQL query to find deleted records in SQL Server.
Restore a backup under a new name and test every query on that copy. Query the log with fn_dblog first and back it up after.
EmailDoctor software scans the MDF file and exports deleted records into a new database when no backup or log exists.
- SQL Server has no recycle bin so deleted records exist only in a history table or a backup or the log or the MDF file.
- Temporal queries need system versioning from before the delete and backup comparisons need a full backup.
- Queries with fn_dblog show the time and login of a delete but need the sysadmin role and an uncleared log.
- Recovery software exports deleted records after an MDF file scan.