How to Recover Deleted Records in MS SQL Server Automatically

Applied to MS SQL Server 2017, 2016, 2014, 2012, 2008 and older versions

MS SQL Server Database is widely used in the corporate world to manage important and confidential data. However, in the SQL server, data loss happens from time to time because of variable issues. For example, if you have executed the UPDATE or DELETE command without implementing the WHERE clause or wrong WHERE clause, you will encounter SQL table, row, or column loss. When the tragedy happens, you can recover the deleted records from the backup if you have created any before. If not, you have to resort to some further methods to make things done. On this page, we will show you two ways to recover deleted records in SQL Server successfully.

Automated Way to Recover Deleted Records in MS SQL Server

If you are not a technical user, you'd better use a professional MS SQL server recovery tool to help you recover deleted records instantly.EaseUS MS SQL Recoveryis a user-friendly program that enables you to recover deleted rows or damaged database andrepair corrupt MDF fileeffectively.

Step 1.Download and run EaseUS MS SQL Recovery.

Step 2.Click the two dots (Browse button) to choose the target MDF file or click "Search" and select the desired file to repair from the list found below. Then, click "Repair" to start the repairing process.

Step 3.When the process completes, a Window will appear to confirm the successful analysis. All the deleted tables & records recovered items will be listed on the left pane in the name of the original tables.

Step 4.Click the "Export" button in the bottom right corner of the screen. You can save the recovered database to database & SQL scripts based on your need. Then, you need to enter Server/Instance Name and connect to the server. If you choose to "Export to database", you can create a new database or export to an existing database.

Manual Method to Recover Deleted Data in SQL Server Using LSNs

LSNs, short for Log Sequence Numbers, are unique identifiers that are given to every record in the SQL Server transaction logs. If you know the deletion time of records, you can use LSNs to recover deleted records in SQL server. The sooner you recover, the greater the chance of recovery.

Follow the step-by-step instructions below to use LSNs for deleted record recovery in SQL server 2017, 2016, 2015, 2014, 2012, 2008 and 2005.

Step 1.Execute the following query to know the number of rows contained in the table from which the records get deleted.

SELECT * FROM Table_name

Step 2.Now, use the query given below to create the backup of the transaction log of the SQL server.

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.Use the following query to get the information about the deleted records from the SQL Server table to recover data.

USE Databasename

GO

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

FROM

fn_dblog(NULL, NULL)

WHERE Operation = ‘LOP_DELETE_ROWS’

Through this query, you will obtain the Transaction ID (000:000001f4) of deleted records, which will be used in the further process.

Step 4.Use the Transaction ID just got to find specific the time at which the records got deleted.

USE Databasename

GO

SELECT

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

FROM

fn_dblog(NULL, NULL)

WHERE

[Transaction ID] = ‘000:000001f4′

AND

[Operation] = ‘LOP_BEGIN_XACT’

With the help of the above query, you will know the value of the current LSN.

Step 5.Now, recover the deleted data from the SQL Server Table by executing the following query.

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.Implement LSN value to restore deleted rows with the following command.

USE Databasename

GO

步骤7。Lastly, check whether the deleted records are recovered to the SQL Table database or not.

USE Databasename_Copy GO Select * from Table_name