Problem

Sometimes users may have numerous of databases in their SQL Server. However, while managing their database sometimes by mistake their records are deleted due to which they face a lot of issues. To maintain the workflow they need to recover deleted records from SQL Server 2014. This could be possible only with successful SQL LOG Recovery

How to Get Back Deleted Records in SQL Server: Solution

Mainly Transaction log is responsible for all database-logging activities. Similarly, in this discussion, we will utilize the usage of similar undocumented function "fn_dblog" to get any unauthorized deletion of data as well as to retrieve deleted data in SQL server.

If you are aware that when your data is removed then, you can easily solve your problem on how to recover deleted data in SQL server database. However, if you do not know the data as well as the time when the data is erased then, it is important to understand first who has deleted the data. In this segment, we will first find the particular LSN under which deleted statement is run after that we will rollback deleted records in SQL until that LSN.

Each record in MS SQL Server transaction log is distinctively recognized by LSN (Log Sequence Number). LSN of log record at which particular event happened can be beneficial for making precise restore sequences and to retrieve deleted data. LSNs are utilized internally at the time of RESTORE sequence to path the point in time at which the data has been reinstated. When data backup is restored, data is reinstated to LSN corresponding to point in time at which backup was taken. Differential, as well as log backups, advance restored the database to later time that corresponds to higher LSN.

Important: Do not do any modifications in production without testing it properly in lower-life cycle environments

Recover Deleted Records from SQL Server with LSN

Follow these steps to recover deleted records in SQL Server with successful SQL Log Recovery:

Step 1

Before moving further, we will make the database as well as the table on which one will run DELETE statement. Then, we will get the data deletion and then rollback deleted records in SQL. Implement the T-SQL code given below to generate database and table and retrieve deleted data.

Step 2

We have made a database with table 'Location' of three columns. Now, we will put few rows in table.

Step 3

We will go further to erase some rows and recover deleted records from SQL Server table by using LSNs from the SQL Server Transaction Log.

Step 4

Now, find the SQL Server Transaction Log and retrieve deleted records in SQL. Run the mentioned code below to get data information about all removed transactions with SQL Log recovery.

You can see the transaction IDs under which DELETE statement was executed. As we know that data is removed from Location table, we will focus on only those transaction IDs under which data is erased from location table to retrieve deleted data. We can get table name from "AllocUnitName" column. It states DELETE statement has been executed on HEAP table 'dbo.Location' under transaction ID 0000:0000055e. There is only one transaction ID under which we can view deleted records SQL Server, which means this action has been implemented in a single batch. Now, capture transaction ID from next command.

Step 5

We initiate that transaction ID from above script that we will utilize in below statement to find the LSN of LOP_BEGIN_XACT operation and recover deleted records from SQL Server. It means that we want an exact LSN when DELETE statement started its transaction to retrieve deleted data.

Step 6

We will utilize STOPBEFOREMARK operation to get back deleted records in SQL Server. We cannot utilize this hexadecimal value to implement the operation of STOPBEFOREMARK. Therefore, we want alteration this value into decimal format. Then put all these together as single number.

Step 7

Now implement transaction log backup on database where data was removed if transaction log backup has not competed since data deletion. Then, we will restore the database to other location or on a similar server with the variant name until above LSN. Then, we will import and recover deleted data in SQL Server from newly reinstated database to production database.

Step 8

Now, the database is restored, One can check whether you could recover and find deleted records SQL Server or not. Execute the below SELECT statement to preview the first ten rows of location table that was deleted and then retrieve deleted data.

Now you know how to retrieve deleted records in SQL Server with SQL Log Recovery. You can utilize this erased data as per your need. If you need to export this data to live database you can utilize the SQL Server Integration Services, i.e. import and export wizard.

SQL Log Recovery become Hard

Above steps can be useful in most of the cases. But several situation may occur when it becomes difficult to restore deleted data with SQL Log recovery. Lets take a look on these situation:

• Changes made after the DELETE, TRUNCATE or DROP statments
• Unable to take the SQL Server offline
• New Transactions performed after the data was lost
• No, recent Full Database backup available.

In such situation user can go with third party SQL Log Recovery to successfully restore deleted data records in SQL Server.

Conclusion

After understanding, the users problem how to recover deleted records from SQL Server 2014. In the above discussion, we have discussed the ways to retrieve deleted data from the SQL Server database by Successful SQL LOG Recovery that makes easy for users in restoring SQL Server deleted records in SQL version 2016 / 2014/ 2012 / 2008r2 / 2005 / 2002 and all below version.