SQL Query to Find Deleted Records in SQL Server

Sometimes clients face shocking database circumstances in their SQL Server databases. The situations could occur during the maintenance process of their database records. Some of the time the database records are erased unintentionally, because of which database admins face numerous issues. Henceforth, to return to the ordinary work process, the records should be recovered back to the SQL Server database.

Here are some proposed strategies that you can use to recover deleted records in SQL Server database. Before Proceeding further Let us have a look at users query,

“Hi, I am rookie at SQL Server, and I have accidentally deleted some records from the SQL table, unfortunately, I don’t have the recent backup available. Does anyone know about any SQL Query to find deleted records in SQL Server or If any particular solution is there to get my deleted records back? Thanks!”

Download Purchase Now

Know The SQL Query to Find Deleted Records in SQL Server

In this segment, we will talk about the manual technique to recover deleted records in SQL server.

Recover Deleted Data in SQL Server Using LSN:

LSNs (Log Sequence Numbers) are special identifiers that are allocated to each record in the SQL Server exchange logs. Subsequently, erased columns of SQL tables are recoverable if the time of their deletion is known.

To begin the recovery procedure there are a few essentials to be satisfied to recover erased data from the SQL Server Table with the help of LSN (Log Sequence Number). For easy and smooth recovery of deleted rows from the SQL Server database table, it ought to have a Full Recovery Model or Logged Recovery Model at the time the deletion of data occurred.

Utilize the beneath referenced steps to find the deleted records in SQL Server 2016, 2015, 2014, 2012, 2008 and 2005.

Step 1: Use the beneath referenced command to check the number of rows present in the table from which the data got erased.

SELECT * FROM Table_name

Step 2: Next, take log back by using the below-mentioned command:

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: Collect all the data about the erased 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’

By using this command, you will acquire a Transaction ID of deleted records.

Step 4: You can execute to the command to find explicit time at which the records got deleted with the help of the Transaction ID.

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’

You get the ongoing LSN by using of the above command.

Step 5: Now, recover the deleted data from the SQL Server Table by executing the restore procedure.

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, check whether erased records are recovered back to the SQL Table database.

USE Databasename_Copy GO Select * from Table_name

Automated Solution to Retrieve Deleted Records in SQL Server

Emaildoctor SQL Recovery is the most trusted and suggested way to effectively recover deleted records in SQL Server. The software permits to recover data from damaged or corrupted MDF/NDF files of SQL Database.

This is an Enterprise level software recommended by most SQL Server experts and professionals to retrieve deleted records in SQL Server. The best thing about this software is that it shows the preview of deleted SQL database items in red color. Also, the user can recover deleted SQL database objects such as Tables, Views, Functions, Store Procedures and so on. The application support to export recovered table records into SQL Server present in the same machine or over a server network. More so, you can also save the data as SQL compatible script or CSV Format. This utility supports SQL Server 2019 / 2017 / 2016 / 2014 and its prior version.

Follow the Steps to Get Back Your Deleted Records

1. Install and Run the Application on your system and click on Open.

2. Select the MDF file from your machine and choose the Advanced Scan mode, also select the SQL Server version. And then check the Preview deleted objects checkbox option.

3. Software will start the Scanning process of MDF files.

4. Preview the SQL database components like Table, Stored procedure, Functions, Views etc. and hit export button ( Software shows the deleted records in Red color.)

5. Now, choose the appropriate export options as per requirements.

6. If you have choose the SQL Server Database option then select the Authentication Mode.

7. The user can select the destination database by creating the new database or also the user can export to existing database.

8. Select the database objects and check the export deleted records option. Finally click on Export button to start the process.

Important Note: The user can also read this post to know how to repair corrupt MDF file of SQL database.

Conclusion

In this blog, we have discussed the SQL Query to find deleted records in SQL server. But, use a manual strategy for recovery under the expert’s direction as the technique is exceptionally monotonous and takes quite a while in the processing. Likewise, there are a lot of chances that the data won’t recover as the procedure doesn’t ensure data recovery. Therefore, SQL database Recovery is the best solution to retrieve deleted records in SQL Server. It plays out the successful recovery in each circumstance and brings your deleted data back.