Solutions to Recover Deleted Records in MS SQL Server

Have you also repeated the same mistake? Trying to use UPDATE or DELETE operations in your SQL Server and forgot to use WHERE clause with it or had used an incorrect WHERE clause. No wonder some records got deleted after that. But for the sake of managing big data stored in SQL Server and avoiding large losses, there should not be any delay in finding the solutions for the lost data recovery. We are here to help you acknowledge some effective solutions to recover deleted records in SQL Server.

The first question I would like to ask the user is if he has the updated backup for the SQL Server database. If the answer is yes, then we can proceed to this standard solution including recovery with backup.

Check out these solutions to recover deleted records in MS SQL Server

Solution 1 – When Updated Backup is Available

This is the primary and standard recovery solution for any data lost in SQL Server. The condition must be the administrator has the latest database backup. Otherwise, there are great chances that the lost records are not recovered with the old backup.

Perform these steps for deleted records recovery with the latest full SQL database backup. You must have Database owner rights for restoring the database to SQL Server.

1. Go to the SQL Server Management Studio and restore the database from the .bak file.
2. Save this database with a separate name as to keep both databases separately.
3. Now, find the deleted records from the SQL data recovered from backup.
4. Finally, to update the altered records (altered rows must be defined); use UPDATE operation on that data.

You can also take help of certain queries in SQL Server to get your deleted data back. Find it in the next solution.

Solution 2 – With Log Sequence Number (LSN)

Very less administrators consider the regular backups a serious action to perform and the result is they require searching for an alternative recovery solution. Don’t worry, for users devoid of the latest backup, we have another recovery solution for you. You can recover deleted records through Log Sequence Number (LSN). These are assigned unique identifiers to each record in Server transaction logs. LSN helps to build restore sequences which can track the point in the time when data was restored. So, if the administrator recognizes the time of deletion of the SQL Server records, then use of LSNs for deleted records recovery will be helpful.

SQL Server administrators must have the Full Recovery Mode or Bulk-Logged Recovery mode enabled in the server to use Log Sequence Number for recovery of deleted SQL Server records.

Let us perform the steps to understand the recovery process through LSN.

1. Run this query in SQL Server to know the number of rows in the table from where records got deleted.
   SELECT * FROM Table_name
2. Run this next query for take a log backup.
   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
3. To get the information about the deleted records like Transaction ID, run this query.
   USE Databasename
   GO
   Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
   FROM
   fn_dblog(NULL, NULL)
   WHERE Operation = ‘LOP_DELETE_ROWS’
4. To get the LSN for knowing the timing when the records got deleted using the above got Transaction ID, run this 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:000001f3′
   AND
   [Operation] = ‘LOP_BEGIN_XACT’
5. Now, restore the data by running this final query and recover the deleted records from the SQL Server database table.
   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

You can confirm that your deleted records are recovered in database table by running this query.

I think the SQL experts may find these queries more relatable but common or new SQL Server users will get confused or make errors while performing these queries including chances of data loss with issues in queries execution in the application. No doubt this method is complex and quite lengthy to perform. A minor mistake in the command could result in complete failure of the process and user needs to start it again. However, you can try the above methods and if finding any difficulty or restrictions, you have another alternative for deleted records recovery to combat the manual method’s challenges.

Most Recommended Third Party Solution

It is the professional SQL Recovery software that deals with all the database-related issues like corruption, data loss, errors, etc. and recovers the data as it was before. The interface is built with minimum icons and interactive features to perform the data recovery in minimum steps. Give the software a try by using its completely free trial version. The highlight of this software is that it works with all SQL Server versions.

Download Now

Final Words

We discussed about the issue of data got deleted from SQL Server tables and the remedies for it. Some manual solutions like use of updated backup and Log Sequence Number for data recovery are explained to the users. In case, these methods do not work well for them, a reliable tool is recommended for the deleted records recovery. We hope the users get some help from it. Stay connected!