How to Recover Deleted Data From Table in SQL Server?

Author | September 27th, 2019 | SQL Server

If someone loses their data, this situation is very chaotic. In such a scenario, 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 2017, 2016, 2014, 2012, 2008 and other versions. So keep read on to learn the best technique of how to to recover deleted records in SQL Server using LSN and SQL Database Recovery tool.

Free Download

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 Retrieve Deleted Records in SQL Server?

There are two solution to recover deleted table in SQL Server 2014 and other edition i.e. Manual and Automated method. In the section below, first we will discuss the manual method step by step and after that we will move to the automated tool for quick solution.

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.

In order 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 section below, we will explain the complete steps to restore deleted records.

Steps to Recover Deleted Records in SQL Server

Follow these steps to learn how to recover deleted data from table in SQL Server 2017, 2016, 2014, 2012, 2008, and so on.

Step 1. Check the number of rows in the table from which records are deleted by following mentioned 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 Transaction ID. You can also get the ongoing LSN with the help of this query.

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. In the end, 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 Automated Solution

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 such as lack of good backup, enter new data in the lines, and the case of altered database lines are not defined. Now, use in order to learn how to recover deleted records in SQL Server, you need an expert solution i.e. FreeViewer SQL Database 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 file with all components like tables, rules, functions, triggers, etc.

To know how to retrieve deleted records in SQL Server, follow the below discussed steps.

Step 1. Launch the SQL Database Recovery Tool and open the MDF file.

Open MDF File

Step 2. Select Quick or Advance scanning mode to get solution of how to recover deleted data from table in SQL Server. After choose the SQL Server MDF file version and check the Preview of Deleted Records box.

Scan and Preview Deleted Records

Step 3. After scanning process, you can see the deleted records in red color.

Preview Deleted Records in Red Color

Step 4. Next, Under Export options, choose Export the recovered data to SQL Server Database and enter the required details.

Export Recovered Data

Step 5. Choose Destination Database from Create New Database and Export to an Existing Database.

Export Data to New Database

Step 6. Export the database with only schema or with schema & data both.

Export With Schema and Data

Step 7. Check the Export Deleted Records box and then click the Export button.

How to Recover Deleted Data From Table in SQL Server

Concluding Words

In the above discussed blog, we have explained the manual as well automated solution to learn how to recover deleted records in SQL Server 2014 and other versions. The process to recover deleted table in SQL Server using LSN is very complicated and required strong technical knowledge. It also required some prerequisites that must be follows to complete this task. Manual process does not have guarantee about recovery of deleted records. So, it is suggested to use SQL Recovery tool which is the best solution of how to recover deleted data from table in SQL Server without any file size and data modification issues.