Mahedee Hasan

Mahedee Hasan

I am a Microsoft MVP (9th times), programmer, software developer, software architect, consultant, author, researcher, technical blogger, technical speaker, trainer and learner. I have more than 15 years experience in the field of software design and development.

Top 2 Methods To Recover Deleted Records From SQL Table

4 minute read

When it comes to data management task, SQL Server is used by most of IT organizations. Sometimes, while working with table users perform UPDATE operation or DELETE operation accidentally without specifying the WHERE condition. This results in the deletion of the SQL table. Now, it becomes difficult for an organization to handle the data loss problem. Fortunately, it is possible to recover deleted records from SQL table. Thus, in this blog, we are going to introduce different ways to recover deleted table record in SQL Server.

How To Recover Deleted Records From SQL Table?
Each record present in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). The user can recover deleted rows if the time of their deletion is known. This can be done only through the use of Log Sequence Number (LSNs). Basically, the LSN of a log record at which a significant event occurred can be useful for constructing correct restore sequences.

Recover Deleted Records From SQL Table – Step-By-Step Process
In this section, we are going to explain the process to recover deleted records from SQL table with the help of Transaction log and LSNs.

Step - 1. First of all, you have to check the number of rows present in the table from which the data has been mistakenly deleted via using the below-mentioned query:

SELECT * FROM Table_name

Step - 2. Now, you need to take the transaction log backup of the database by implementing the query given below:

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. After that, to recover deleted records from SQL Server Table, gather some information related to deleted rows. For this, you need to execute the query given below:

USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'

After executing all the above-mentioned queries, the user will obtain a Transaction ID (assuming ID is 000:000001f3) of the deleted row. Now, the user can easily determine the time when these rows were deleted via using this ID.

Step-4. To find the particular time at which rows were deleted you need to use the transaction ID 000:000001f3. You have to follow the query given below:

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'

After executing this query, you will get the value of current Log Sequence Number (LSN). Assuming the value is 00000020:000001d0:0001

Step - 5. Next, begin the restore process to recover deleted records from SQL table:

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. Now, apply the transaction log to restore deleted rows by using LSN 00000020:000001d0:0001

USE Databasename

GO
RESTORE LOG Databasename_COPY FROM DISK = N'D:\Databasename\RDOTrLog.trn' WITH STOPBEFOREMARK = ‘lsn:0x00000020:000001d0:0001' Note: Since LSN values are in HexadecimaUSE Databasename
GO

RESTORE LOG Databasename_COPY FROM DISK = N'D:\Databasename\RDOTrLog.trn' WITH STOPBEFOREMARK = ‘lsn:0x00000020:000001d0:0001' 
Note: Since LSN values are in Hexadecimal form and for restoring tables using this LSN, we need to convert it into decimal form. For this purpose, we add 0x just before the LSN as shown above form and for restoring tables using this LSN, we need to convert it into decimal form. For this purpose, we add 0x just before the LSN as shown above

Step - 7. Finally, the process to recover the deleted table from SQL Server will begin. After the completion of the process, you can easily check for the SQL table in the database

USE Databasename_Copy GO Select * from Table_name

Recover Deleted Records From SQL Table
However, it is quite difficult for the user to implement the steps correctly. As the method is highly time-consuming and requires adequate technical knowledge. Additionally, if the user is not able to perform the steps correctly then it can also lead to permanent data loss issues. Thus, to avoid all the catastrophic situations, the user can make use of an automated solution that is SysTools SQL Log Viewer. With SQL Log Analyzer, the user can easily recover deleted records from SQL Server if the database is in simple recovery mode. Moreover, the utility can also read and analyze all the transaction like INSERT, DELETE, UPDATE etc. The software is capable to preview .ldf activities such as transaction, time, name, table name, query etc.

Final Words
Every organization or businesses own important and confidential data. Thus, no one can afford the loss of their vital data. To manage tons of data, SQL Server database is used by numerous companies and users. Sometimes, the situation arises in which the user by mistake deletes SQL table. Now, recovering deleted SQL table is quite a complex task for the user without knowing the proper approach. Thus, in the above section, we have introduced a complete procedure to recover deleted records from SQL table.

Author: Andrew Jackson

You May Also Enjoy