Table of Contents
This tip is about the how to Recover Deleted Records in SQL Server. So read this free guide, How to Recover Deleted Records in SQL Server step by step. If you have query related to same article you may contact us.
How to Recover Deleted Records in SQL Server – Guide
If you accidentally ran a DROP or DELETE command on a table with an incorrect WHERE clause and important records were lost, you can recover them using any of the following methods.
Recover deleted data in SQL Server using LSN:
LSNs (Log Sequence Numbers) are unique identifiers assigned to each record in the SQL Server transaction logs. Therefore, rows deleted from SQL tables are recoverable if the time of their deletion is known.
To start the recovery process there are several prerequisites to be met to recover deleted data from SQL Server Table using LSN (Log Sequence Number). For smooth recovery of deleted rows from SQL Server database table, it must have Full Recovery Model or Logged Recovery Model at the time the data was deleted. Use below mentioned steps to recover deleted data from SQL Server 2016, 2015, 2014, 2012, 2008 and 2005.
SELECT * FROM Table_name
USE DatabasenameGOBACKUP LOG [Databasename]TO DISK = N’D:DatabasenameRDDTrLog.trn’WITH NOFORMAT, NOINIT,NAME = N’Databasename-Transaction Log Backup’,SKIP, NOREWIND, NOUNLOAD, STATS = 10GO
USE DatabasenameGOSelect [Current LSN] LSN], [Transaction ID]Operation, Context, AllocUnitNameFROMfn_dblog(NULL, NULL)WHERE Operation = ‘LOP_DELETE_ROWS’
USE Database NameGOSELECT[Current LSN]Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]FROMfn_dblog(NULL, NULL)WHERE[Transaction ID] = ‘000:000001f3′AND[Operation] = ‘LOP_BEGIN_XACT’
Recover Deleted D USE DatabasenameGORESTORE DATABASE Databasename_COPY FROMDISK = ‘D:DatabasenameRDDFull.bak’WITHMOVE ‘Databasename’ TO ‘D:RecoverDBDatabasename.mdf’,MOVE ‘Databasename_log’ TO ‘D:RecoverDBDatabasename_log.ldf’ ,REPLACE, NORECOVERY;GO
USE Databasename_Copy GO Select * from Table_name
Final note
I hope you like the guide How to Recover Deleted Records in SQL Server. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.