How to Recover Deleted Records in SQL Server 2022 Tip


This tutorial is about recovering deleted records in SQL Server. We will do our best to make you understand this guide. I hope you enjoy this blog How to recover deleted records in SQL Server. If your answer is yes, please share after reading.

Check how deleted records are recovered in SQL Server

If you accidentally DROP or DELETE a table with an incorrect WHERE clause and important records are lost, you can use one of the following methods to recover them.

Recover deleted data in SQL Server with LSN:

LSNs (Log Sequence Numbers) are unique identifiers assigned to each record in SQL Server transaction logs. Therefore, rows deleted from SQL tables can be recovered if the time of their deletion is known.

In order to start the recovery process, several prerequisites must be met to recover deleted data from SQL Server table using LSN (Log Sequence Number). For a smooth recovery of deleted SQL Server database table rows, you must have a full recovery model or a recorded recovery model at the time the information was deleted. Use the below-given steps to recover deleted data from SQL Server 2016, 2015, 2014, 2012, 2008 and 2005.

  • Use the query mentioned below to check the number of rows present in the table from which data has been deleted.

SELECT * FROM tablename

  • Then consider logging in again using the query below:

USE database nameGOBACKUP LOG TO DISK = N’D:DatabasenameRDDTrLog.trn’WITH NOFORMAT, NOINIT,NAME = N’Databasename-Transaction Log Backup’,SKIP, NOREWIND, NOUNLOAD, STATS = 10GO

  • Collect information about deleted records from SQL Server table to recover data.

USE database name GOSelect LSN], Operation, Context, AllocUnitNameFROMfn_dblog(NULL, NULL)WHERE Operation = ‘LOP_DELETE_ROWS’

  • With the help of this query you get the transaction ID of the deleted data records.
  • You can run the query to use the transaction ID to find the exact time the records were deleted.

USE dbnameGOSELECTOperation, , , , FROM fn_dblog(NULL, NULL) WHERE = ‘000:000001f3’Y = ‘LOP_BEGIN_XACT’

  • Gets the current LSN using the previous query.
  • Now recover the deleted data from SQL Server table by starting the recovery process.

Recover deleted D USE database name GORESTORE DATABASE database name_COPY FROMDISK = ‘D:database nameRDDFull.bak’WITHMOVE ‘database name’ TO ‘D:RecoverDB database name.mdf’, MOVE ‘database name_log’ TO ‘D:RecoverDBdatabase name_log.ldf’ ,REPLACE, NOT REPAIR;GO

  • Finally, check if the deleted records have been recovered in the SQL table database.

USE Databasename_Copy GO SELECT * from Table_name

Final Words: How to Recover Deleted Records in SQL Server

I hope you understand this article How to recover deleted records in SQL Server, if your answer is no, you can ask anything in the contact forum section related to this article. And if your answer is yes, then please share this article with your family and friends.


Comments are closed.