If someone loses their data, this situation is very chaotic. Sometimes, Microsoft SQL Server database table records are deleted by mistake during the management and maintenance of records that confront users with many problems. To solve this issue, this blog will show you how to recover deleted data from table in SQL Server 2019, 2017, 2016, 2014, 2012, 2008 and other versions. Keep reading to learn the best technique of how to recover deleted records in SQL Server using LSN and SQL Database Recovery tool.
Database tables are objects that store all data in a database. In a table, the data is logically organized in a row and column format. Each row represents a unique record and each column represents a field in the record.
How to Recover Deleted Data From Table in SQL Server Using LSN?
The LSN (Log Sequence Numbers) are unique identifiers that are assigned to each record in the SQL Server transaction logs. Deleted rows of SQL tables can therefore be restored if the time of deletion is known.
To recover deleted table in SQL Server by using Log Sequence Numbers, some prerequisites must be met. For a smooth recovery of deleted data from the SQL Server database table, the Full Recovery Model or the Logged Recovery Model should exist at the time of data wipe. In the next section, we will explain the complete steps to recover deleted data from table in SQL Server 2017, 2016, 2014, 2012, 2008, etc.
Steps to Recover Deleted Records in SQL Server are as follows;
Step 1. Check the number of rows in the table from which records are deleted by executing the below query.
SELECT * FROM Table_name
Step 2. Now take a log back with the below-mentioned query.
BACKUP LOG [Databasename]
TO DISK = N’D:\Databasename\RDDTrLog.trn’
WITH NOFORMAT, NOINIT,
NAME = N’Databasename-Transaction Log Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
Step 3. Get the transaction ID of the deleted records. This will give you information about deleted rows.
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
WHERE Operation = ‘LOP_DELETE_ROWS’
Step 4. Find the specific time at which the records get deleted with the help of a Transaction ID. You can also get the ongoing LSN with the help of this query.
[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
[Transaction ID] = ‘000:000001f3′
[Operation] = ‘LOP_BEGIN_XACT’
Step 5. Now retrieve deleted records in SQL Server by starting the restore process.
Recover Deleted D USE Databasename
RESTORE DATABASE Databasename_COPY FROM
DISK = ‘D:\Databasename\RDDFull.bak’
MOVE ‘Databasename’ TO ‘D:\RecoverDB\Databasename.mdf’,
MOVE ‘Databasename_log’ TO ‘D:\RecoverDB\Databasename_log.ldf’,
Step 6. Verify that deleted records are recovered to the SQL table database or not.
USE Databasename_Copy GO Select * from Table_name
How to Recover Deleted Data From Table in SQL Server Using SQL Repair Tool
All solutions described above can only be helpful if you have the latest backup of your database. In many scenarios, the above steps will fail like lack of good backup, enter new data in the lines, and the case of altered database lines are not defined. To recover deleted records in SQL Server, use an expert solution SQL Recovery software. This will help you to recover deleted table in SQL Server without altering any kind of data. It is capable to fix all SQL errors and can repair corrupt MDF and NDF database files with all components like tables, rules, functions, triggers, etc.
Also, the tool is capable to repair and restore deleted database objects and table records. Once the database is recovered users can export the data to live SQL Server database, SQL Server Compatible Script, or CSV file format in a few simple steps.
To know how to retrieve deleted records in SQL Server, follow the below discussed steps.
Step 1. Launch the SQL Recovery Tool and click on the Open to load MDF file.
Step 2. Select Advance scanning mode to recover deleted data from tables in SQL Server. After that choose the SQL Server MDF file version and check the Recover Deleted Objects box.
Step 3. After the scanning process, you can see the deleted data in red color.
Step 4. Next, Under Export options, choose Export the recovered data to SQL Server Database and enter the required details.
Step 5. Choose Destination Database from Create New Database and Export to an Existing Database.
Step 6. Export the database with only schema or with schema & data both. Check the Export Deleted Records box and click the Export button.
We explained the manual as well as automated solution to recover deleted records in SQL Server 2014 and other versions. The task to recover deleted table in SQL Server using LSN required in-depth technical knowledge and some prerequisites that must be followed to complete this task. The manual process does not have a guarantee about the recovery of deleted records. To overcome these issues, it is recommended to use the SQL Recovery tool to recover deleted data from tables in SQL Server smoothly.
Frequently Asked Questions
You can find out who deleted database records using SSMS:
Step 1. Open SSMS and connect to SQL Instance
Step 2. Right click in the instance and select Reports >> Standard Reports >> Schema Changes Report
Step 3. Now, a pop-up window with the “Schema Changes” history appears, showing the name of the user who deleted records in SQL Server.
SQL Repair tool offers the option to recover and preview deleted table records and other database objects in red color.