How to retrieve deleted records in SQL Server 2017, 2016, 2014, 2012, 2008, 2005 database?

The above-mentioned issue is a real-time query raised by users. The scenario can take place during the maintenance and management of their database records. Sometimes the database records are deleted by mistake, due to which database administration faces many issues. Hence, to get workflow normal, the query arises “How to retrieve or get back deleted record in SQL Server2017, 2016, 2014, 2012, 2008, 2005 database” or recover deleted records in SQL server. So, we will try to address this issue in detail.

In this technical guide, we are going to discuss how to recover deleted records in SQL Server. Also, you will study some basic uses of LSN ( Log Sequence Number ) and transaction log.

Methods to Retrieve deleted records in SQL Server 2017, 2016, 2014, 2012, 2008, 2005 database

You can recover deleted records if you know the time when data got deleted. Microsoft said that each record in the SQL Server transaction log is uniquely identified by an LSN aka Log Sequence Number. So, you will use these LSNs to recover our deleted records in SQL Server.

Manual Approach

To recover deleted records in SQL Server 2017, 2016, 2015, 2014, 2012, 2008 and 2005, you can follow the below steps given below.

Step 1. Check the number of records existing in the table from which records or rows were deleted.

SELECT * FROM Table_name

Step 2. Now take a log backup.

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.Find the Transaction ID of deleted Records. It will help you to get 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. Now you can see the exact time at which the records (rows) were deleted. The Transaction ID will help you to find this information. These steps will also help you to find the ongoing LSN.

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, start the procedure to retrieve deleted data from records in SQL Server Table.

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. Finally, you need to check in the table whether deleted records are recovered or not.

USE DatabaseName_Copy GO Select * from Table_name

Limitations of the above technique

All the steps in the manual solution can only help when you have the most updated backup of your database. In any case, the above-mentioned steps get fail. This can happen due to many reasons

• Unavailability of good backup.
• The entry of new data in the rows.
• Undefined case of altered database rows.

Professional Approach

The best alternative solution is available for you to retrieve deleted records in SQL Server 2017, 2016, 2014, 2012, 2008, 2005 database is SQL Database Recovery Software. In addition to this, it is a commercial application that allows recovering Triggers, Rules, Functions, Tables with ease.

This professional tool can resolve all kinds of issues arises in SQL Server. Databasefilerecovery’s SQL Database Recovery Software is the best tool to recover deleted or corrupt database files. Above all, it performs perfect recovery in every circumstance and helps you to get your data back.

Final Words 

Here from the above explanation and steps, it is very clear that how you can recover deleted records in SQL server if these get accidentally deleted. But while using a manual method, you might have got an idea about LSNs, transaction logs, etc. A novice user may find it difficult, so there is an alternative solution for you which is SQL Database Recovery Software. Therefore, you can choose any of the approaches as per your convenience. Thanks for Reading.

Related Post