Summary: This blog explains how to recover deleted table records in SQL Server. It demonstrates an example to retrieve deleted records from a SQL table using a Log Sequence Number (LSN). It also explores Stellar Repair for MS SQL software to recover deleted records quickly without any data loss.
If you’ve accidentally executed a DROP or DELETE command against a table with a wrong WHERE clause and lost important records, you can recover them by using any of the following methods.
Note: The truncate operations cannot be recovered. Only the delete operations can be recovered.
Note: This method won’t work if the transaction log backup is corrupted or missing. In that case, skip to the next method.
Each SQL Server transaction log record is uniquely identified by a log sequence number (LSN). You can construct restore sequences by using the LSN of a log record at which a significant event occurred.
Let’s take an example to understand how you can use the log sequence number method to recover deleted table records. In this example – we’ll create a database and a table, insert rows into the table, delete some rows, get information about deleted data, and recover the data.
In this section, we will create a test database and a table on which we will run a DELETE operation. Then, we will find deleted data and recover it using LSN.
Execute the following query to create a database named ‘RecoverDeletedRecords’ and a table named ‘Employee’:
CREATE DATABASE RecoverDeletedRecords;
CREATE TABLE [Employee] (
[Sr.No] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT ‘City1’);
Figure 1 – Create Database in SSMS
We have created a table named ‘RecoverDeletedRecords’ and a table ‘Employee’ with three columns. Now, we’ll insert rows into the table by running the following query:
INSERT INTO Employee DEFAULT VALUES;
Figure 2 – Insert Records into New Table
Now let’s delete some rows by executing the following:
WHERE [Sr.No] < 10
Select * from Employee
Figure 3 – Delete Table Rows
As shown in the image above, all the rows having Sr.No less than 10 have been deleted from the table ‘Employee’.
Next, we will get information about the deleted rows by searching the transaction log:
Operation = ‘LOP_DELETE_ROWS’
Figure 4 – Transaction ID of Deleted Rows
After getting the Transaction IDs of the deleted rows, we’ll have to find the time when the rows were deleted.
To find the exact time when the rows were deleted, we must use the transaction ID to get the LSN of the LOP_BEGIN_XACT log record of a transaction:
[Transaction ID] = ‘0000:0000020e’
[Operation] = ‘LOP_BEGIN_XACT’
Figure 5 – Current LSN of Transaction Log
In the above screenshot, we can see the current LSN of the transaction along with details like the time ‘2021/03/15 19:36:59:337’ when a DELETE statement was executed with LSN ‘00000014:0000001a:0001’ under transaction ID ‘0000:0000020e’. To recover the deleted SQL database table data, proceed with the next step.
To recover the deleted SQL table records, we will need to convert LSN values from hexadecimal to decimal form. To do so, add ‘0x’ before the log sequence number as shown in the code below:
–Restoring Full backup with norecovery.
RESTORE DATABASE RecoverDeletedRecords_COPY
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak’
MOVE ‘RecoverDeletedRecords’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.mdf’,
MOVE ‘RecoverDeletedRecords_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.ldf’,
–Restore Log backup with STOPBEFOREMARK option to recover exact LSN.
RESTORE LOG RecoverDeletedRecords_COPY
DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords_tlogbackup.trn’
STOPBEFOREMARK = ‘lsn:0x00000014:0000001a:0001’
Figure 6 – Recover Deleted Records
Now that your data is restored, check whether the deleted records have been recovered. For this, run the following query to see the first ten rows of the table that were deleted (in Step 3 above).
SELECT * from Employee
If the deleted table records have not been recovered, try using a SQL recovery tool such as Stellar Repair for MS SQL to get back the deleted data.
Note: In the code above, replace the path ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak’ with the path where your backup file is located. Also, replace the path of .mdf and .ldf files with the path where you have saved the database files.
The SQL recovery software from Stellar® can help you recover all the deleted records in a SQL Server database in a few simple steps. Besides the deleted data, the software can also recover all other objects like views, triggers, stored procedures, etc. while maintaining data integrity.
Before using the software to restore the records, make sure your system meets the following prerequisites:
Download the demo version of the SQL recovery software to recover deleted rows or damaged databases:
Figure 7 – Stellar Repair for MS SQL Main Interface
Figure 8 – Select Database MDF File in Software
Note: If you do not know where your database is located, you can find it in SSMS in database properties, in the data file page (refer to the image below).
Figure 9 – Check database Location in SSMS
Figure 10 – Include Deleted Records Option
Figure 11 – Select Scan Mode
Figure 12 – Repair Complete Message Box
Figure 13 – Log Report
Figure 14 – Save Database
Figure 15 – Saving Options
Figure 16 – Choose Authentication Type
The recovered table records and other selected data will be saved in the repaired MDF file.
Watch the complete working process of the SQL Recovery software:
In this article, we learned how to recover deleted records in SQL Server. These solutions can work in any SQL Server version in Windows. If you’ve the Full database backup available, you can try recovering deleted records using the transaction log with Log Sequence Number (LSN). If the backup is corrupted, use Stellar Repair for MS SQL to recover the deleted data in SQL Server or in other formats like Comma Separated Value (CSV) files, HTML, or XLS.
The SQL database repair software not only helps in recovering deleted rows but also restores the SQL database damaged by virus attack, hardware failure, or other reasons.
Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.
Stellar Repair for MS SQL is an enterpriRead More
3-in-1 software package, recommended byRead More
Stellar Converter for Database is an effRead More
Powerful tool, widely trusted by users &Read More