How to Recover Deleted Records in SQL Server 2014, 2016 & 2012

In SQL server, there are numerous databases which are stored by the users. But sometimes, these records get deleted by mistake while managing the database. To maintain the normal workflow and functioning, they need to recover deleted records from SQL server. In this article, we’ll explore the methods using which we can recover deleted table in SQL server.

Many times, a user performs DELETE or UPDATE operation in an SQL Server database without applying the WHERE condition. It is a common reason for the loss of data from SQL Server tables. SQL Server database is one of the most popular DBMS. So, the data loss effects in a large form. So, users must know the methods to recover deleted data from SQL Server. Let’s have a look at the methods we can use to recover deleted record in SQL server.

Methods to recover deleted records in SQL server

We can recover deleted records in SQL server using a manual as well as an automated method. So, let’s explore one by one.

Recover deleted data and records in SQL server using LSN method

Every record in the SQL Server transaction log can be identified uniquely by a log sequence number (LSN). The LSN is a significant event of the log record and can be used to construct correct restore sequences. LSN can be used in the RESTORE sequence to track the point in the time at which data has been restored. Also, when a backup is restored, the data is automatically restored to the LSN corresponding to that point at which the backup was made.

So, to perform the recovery of deleted data in SQL server, go through the below steps:

Step 1: First of all, check the no. of rows present in the table from which the data has been deleted.

SELECT * FROM Table_name

Step 2: Next, use the below mentioned query to log back.

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: Now, gather the data and transaction ID of the records deleted from the SQL server.

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

Step 4: Execute the below query using transaction ID to find the specific time at which the records got deleted.

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’

Step 5: Now, after finding out the exact no. of records, recover them from the SQL Server Table by this process.

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: At last, check whether the deleted records have been restored to SQL server or not by performing this query.

USE Databasename_Copy GO Select * from Table_name

Some cons of LSN method

Although the LSN method is helpful in recovering the deleted database and records in SQL server, there are certain demerits of this method too.

• The tedious task for users who are not technically sound.
• High chances of loss of data while because of errors that may arise while executing the queries.
• The lengthy and time-consuming process to recover deleted data from SQL Server.

An automated method to recover deleted data from SQL server

As I have explained above, there are certain cons of using LSN method if you are not technically sound. Also, there are more chances of error which may lead to corruption of data. So, it is advisable to adopt a professional method of using an SQL database recovery software. It is the most effective and reliable solution to recover deleted records in SQL server. It recovers and restores the corrupted MDF as well as NDF files from the SQL server.

You can use the below steps to perform recovery of deleted data using this method.

• Click on the Open button on the taskbar. Further, Browse the corrupted MDF file for the recovery process.
• Now, choose the corrupted MDF file in the list.
• Choose the recovery mode in between Standard and Advanced.
• Click on the checkboxes accordingly and proceed by clicking on OK.
• Scanning is in process. Once it completes, click OK.
• Now, the MDF files will be shown in a tree-structure. You can save your file.
• Select the saving mode between SQL server database and SQL server compatible scripts.
• Don’t forget to test the database if choosing SQL server database saving mode.
• Within a few minutes, saving process will be completed.

Conclusion

In the blog, I have illustrated both manual as well as automated methods to Recover deleted records in SQL server. Although, the Log Sequence number (LSN) method can be used. But, it is very long and tedious process and should Be executed under an expert guidance. Instead, it is recommended to use the Automated method as it is more feasible.

Realted Post