SQL Server

How to Recover Deleted Data From Table in SQL Server? Rollback Rows & Records

MS SQL Server database table records get deleted by mistake during the management and maintenance of records oftentimes. This is why users need to recover deleted data from table including rows, records, etc. Log Sequence Number (LSN) method & automated solutions both can fix the problem.

Users can recover deleted SQL records if they have lost the data because of DROP or DELETE commands with. It mostly happens due to false WRONG stipulations. However, the TRUNCATE command can not be rollback with the above methods.

The LSN is a proven solution because the log file records the time of each & every transaction executed in SQL Server. The “fn_dblog()” can help users to identify the deleted data like table, rows, stored procedures, & all other objects.

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. Therefore, learning how to recover deleted rows in SQL Server is easy if the time of data 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 for learning how to recover deleted data from table in SQL Server 2017, 2016, 2014, 2012, 2008, etc.

Also Read: Repair SQL Server Page Level Corruption Accurately

Recover Deleted Records in SQL Server Step by Step

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.

USE Databasename

GO

BACKUP LOG [Databasename]

TO DISK = N’D:\Databasename\RDDTrLog.trn’

WITH NOFORMAT, NOINIT,

NAME = N’Databasename-Transaction Log Backup’,

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Step 3. Get the transaction ID of the deleted records. This will give you information about deleted rows.

USE Databasename

GO

Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName

FROM

fn_dblog(NULL, NULL)

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. This way learning  how to rollback deleted data in SQL becomes easier.

USE Databasename

GO

SELECT

[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]

FROM

fn_dblog(NULL, NULL)

WHERE

[Transaction ID] = ‘000:000001f3′

AND

[Operation] = ‘LOP_BEGIN_XACT’

Step 5. Now retrieve deleted records in SQL Server by starting the restore process.

Recover Deleted D USE Databasename

GO

RESTORE DATABASE Databasename_COPY FROM

DISK = ‘D:\Databasename\RDDFull.bak’

WITH

MOVE ‘Databasename’ TO ‘D:\RecoverDB\Databasename.mdf’,

MOVE ‘Databasename_log’ TO ‘D:\RecoverDB\Databasename_log.ldf’,

REPLACE, NORECOVERY;

GO

Step 6. Verify that deleted records are back in the SQL table database or not.

USE Databasename_Copy GO Select * from Table_name

Also Read: SQL Server Disaster Recovery Solutions from Experts

How to Recover Deleted Data From Table in SQL Server Automatically

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. Therefore, users mustlearn how to recover deleted rows in SQL Server automatically. The automated tool even solves the SQL database recovery pending issue as well.

To recover deleted records in SQL Server, use an expert solution SQL MDF Recovery Tool. This will help you to recover deleted table in SQL Server without altering any kind of data. Also, 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.

Download Now Purchase Now

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 rollback deleted data in SQL, 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 . Finally, click the Export button.

Also Read: Extract Data From MDF File in SQL Server Safely

Concluding Words

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 users must keep in mind to complete this task.

Moreover, the manual process does not guarantee the recovery of deleted records. Hence, to overcome these issues, we advise users to use the SQL Recovery tool to recover deleted data from table in SQL Server smoothly.

Frequently Asked Questions

Q-1. How to rollback deleted data in SQL automatically?

Ans: Below are the quick steps for users to how to recover deleted rows in SQL Server along with all database objects.

1. Download tool & launch on computer.
2. Hit the open button to add MDF files.
3. Select scan mode as quick or advanced.
4. Now, set the destination path for files.
5. Enter the Export/Save button to finish.