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.
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