Summary: This blog discusses the solution to recover deleted records in SQL server. The manual and the automated solution are explained here in a simple way.
Commonly, in the SQL server, the data can be lost because of using wrong commands. There are the chances you have executed the UPDATE or DELETE command without implementing the WHERE clause or wrong WHERE clause. As a result, the records of the table in the SQL server gets deleted. Unluckily, you don’t have the backup of the deleted records in the SQL server 2016. But, you want to recover deleted records in SQL server. Undoubtedly, you can recover the deleted records.
Therefore by implementing the methods which I am going to discuss in the below section will be useful for the recovery of deleted table records. So, let’s move to the different solution.
How to Recover Deleted Records in SQL Server 2016, 2014, 2012
The manual solution is discussed here can be used if the certain conditions are known. These conditions I will tell you further. The second method discussed here will be the automated method. Let’s start with the manual method.
Utilizing LSNs to Recover Deleted Records in SQL Server
Here, the LSNs stands for Log Sequence Numbers. If the deletion time of records is known then by using LSNs you can recover deleted records in SQL server. Actually, each record of SQL server transaction log is recognized by unique increment value that is LSN. So, you have to perform the quick recovery before the transaction log gets lost. The blog also describes how to rollback deleted data in SQL Server 2008 r2.
Therefore, follow the below instructions to use LSNs for deleted record recovery in SQL server.
Step 1: First, to know the number of rows contained in the table from which the records are deleted, execute the following SQL query to find deleted records in SQL Server.
SELECT * FROM Table_name
Step 2: Now, create the backup of the transaction log of the SQL server by using the given query.
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: To get the required information for the recovery of deleted records in SQL server, run the following query.
USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = ‘LOP_DELETE_ROWS’
From the above, you will get the transaction of the deleted records of the row. Let’s suppose “000:000001f2” is the transaction ID. This ID will be used in the further process.
Step 4: By using “000:000001f2” transaction ID we can determine deletion time of the rows. Run the following query.
USE Databasename
GO
SELECT
[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘000:000001f2′
AND
[Operation] = ‘LOP_BEGIN_XACT’
Now, the value of current LSN will be known to you let’s suppose “00000020:000001d0:0002”.
Step 5: Start the recovery process by executing the following command.
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: Then, implement LSN “00000020:000001d0:0002” to restore deleted rows by the following command.
USE Databasename
GO
RESTORE LOG Databasename_COPY FROM DISK=N’D:\Databasename\RDOTrLog.trn’ WITH STOPBEFOREMARK=‘lsn:0x00000020:000001d0:0002′
Note: The LSN value is in hexadecimal to it is required to convert it in decimal form. For doing the same, the 0x prefix is used in LSN value.
Step 7: At last, you can check whether the deleted records are restored or not with the help of the following query.
USE Databasename_Copy GO Select * from Table_name
The Disadvantage of Using LSNs for Deleted Record Recovery in SQL
- It is the time-consuming process to recover deleted record in SQL.
- The entire process requires the technical knowledge for implementing the process.
- Commencing of single mistake will result in failure of complete process.
Alternate Solution to Recover Deleted Records in SQL Server
As discussed above the manual method had some disadvantages. So, I will suggest the alternate solution SQL Database Recovery Tool. This tool can recover the deleted records from the SQL database. The recovery process completes within the seconds using this tool. You can use this tool without having any technical knowledge.
Conclusion
I have discussed the two methods, the manual method needs technical knowledge. If you have technical knowledge you can easily use it. Otherwise, the automated solution will be the better option to recover deleted records in SQL server 2016, 2014, and 2012.
