DIY Methods to Restore Deleted Records in SQL Server without Data Loss

by Posted on

SQL Server Database is mainly used to store data on the server and keep a record of files and many corporate companies also use SQL server for maintaining attendance sheets and keeping salary records. But sometimes accidentally the user deletes files from the SQL Server which results in a huge loss. Therefore in this article, I will share some techniques through which you can easily restore deleted records in SQL Server and access them without any hassle.


I’ll share some manual techniques which might also work in getting back the deleted records and if by chance they fail then you have to spend some bucks and try professional recovery software. It is really difficult to recover deleted or lost records from the database but we can give a try by following the manual methods and see if the manual methods can fix this problem or not.

Steps to Recover Lost Data in SQL Server 2012 and other Versions

First try the manual procedure and if it fails, then move to the Professional method.

Try to Recover Data via LSN

LSN means Log Sequence numbers which identify the time of SQL Log Transaction when it has occurs. So you can get back deleted data with the help of LSN. But the prerequisite for this method is that you should remember the time and date when you have deleted the data. You can follow the steps given below to get back erased data in SQL Database:-

  • Check the number of rows present in the table by using the query given below.
SELECT * FROM Table_name
  • Use the following query and then create a backup of the SQL Log transaction.
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
  • Run the following query to retrieve information of the deleted records.
USE Databasename

GO

Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName

FROM

fn_dblog(NULL, NULL)

WHERE Operation = ‘LOP_DELETE_ROWS’

Now, you’ll get the transaction of all the deleted record and if you have got the transaction I.D. of the lost record then go to the next step.

  • In the following query enter the transaction I.D. of your deleted data and then you’ll know the time and date of deletion.
USE Databasename

GO

SELECT

[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name],
[Transaction SID]

FROM

fn_dblog(NULL, NULL)

WHERE

[Transaction ID] = ‘Transaction of your deleted data′

AND

[Operation] = ‘LOP_BEGIN_XACT’

You’ll get the LSN value of your deleted record and now, it is the time to restore deleted records in SQL Server.

  • Execute the following query to initiate the recovering 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
  • Now, put the LSN value that you have got in the following command.
USE Databasename
GO
 RESTORE LOG Databasename_COPY FROM DISK=N’D:\Databasename\RDOTrLog.trn’ WITH STOPBEFOREMARK=‘lsn:0x00000020:000001d0:0002′
  • Finally, Run the following query and see whether you have succeeded in restoring the data back.
USE
Databasename_Copy GO Select * from Table_name

You May Also Read- SQL Server Login Error 18456

I hope this manual method will help you out to resolve your issue but there are some drawbacks of using the manual methods.

Disadvantages of the Manual Method

  • It is a very time-consuming method and you also have to run all the queries will full concentration.
  • Any kind of negligence during the whole process may result in full data loss.
  • The manual method is not recommended for novice users because it requires a lot of technical expertise.

SQL Database Recovery Software

The professional tool is the only solution for getting back the deleted data in SQL Database and it also defeats all the disadvantages of the manual methods and neither is it time taking nor there is any chance of data loss. The user can easily recover all the data either it is deleted or has gone corrupted.

Conclusion

The ball is in your court and you have to decide which method will be the best option for recovering records and if you are having good technical knowledge then you can try the manual method otherwise you should go with the Software because it is quick and there are no chances of data loss during the recovery process. So, make your choice and restore deleted records in SQL Server.

Published by Yousuf Hasan

Leave a Reply