-
Written By Pallavi
-
Updated on August 10th, 2023
The above-mentioned issue is a real-time query raised by users. The scenario can take place during the maintenance and management of their database records. Sometimes the database records are deleted by mistake, due to which database administration faces many issues. Hence, to get workflow normal, the query arises “How to retrieve or get back deleted records in SQL Server 2017, 2016, 2014, 2012, 2008, 2005 database” or recover deleted records in SQL server. So, we will try to address this issue in detail.
In this technical guide, we are going to discuss how to recover deleted records in SQL Server. Also, you will study some basic uses of LSN ( Log Sequence Number ) and transaction log.
You can recover deleted records if you know the time when the data got deleted. Microsoft said that each record in the SQL Server transaction log is uniquely identified by an LSN aka Log Sequence Number. So, you will use these LSNs to recover our deleted records in SQL Server.
To recover deleted records in SQL Server 2017, 2016, 2015, 2014, 2012, 2008 and 2005, you can follow the below steps given below.
Step 1. Check the number of records existing in the table from which records or rows were deleted.
SELECT * FROM Table_name
Step 2. Now 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
Step 3.Find the Transaction ID of deleted Records. It will help you to get information about deleted rows.
USE DatabaseName
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = ‘LOP_DELETE_ROWS’
Step 4. Now you can see the exact time at which the records (rows) were deleted. The Transaction ID will help you to find this information. These steps will also help you to find the ongoing LSN.
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, start the procedure to retrieve deleted data from records in SQL Server 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
Step 6. Finally, you need to check in the table whether deleted records are recovered or not.
USE DatabaseName_Copy GO Select * from Table_name
Limitations of the above technique
All the steps in the manual solution can only help when you have the most updated backup of your database. In any case, the above-mentioned steps get fail. This can happen due to many reasons
The best alternative solution is available for you to retrieve deleted records in SQL Server 2017, 2016, 2014, 2012, 2008, and 2005 databases is SQL Database Recovery Software. In addition to this, it is a commercial application that allows recovering Triggers, Rules, Functions, and Tables with ease.
This professional tool can resolve all kinds of issues that arises in SQL Server. Databasefilerecovery’s SQL Database Recovery Software is the best tool to recover deleted or corrupt database files. Above all, it performs perfect recovery in every circumstance and helps you to get your data back.
From the above explanation and steps, it becomes very clear how one can recover accidentally deleted records in SQL Server. But while using a manual method, you might have got an idea about LSNs, transaction logs, etc. A novice user may find it difficult, so there is an alternative solution for you which is SQL Database Recovery Software. Therefore, you can choose any of the approaches as per your convenience. Thanks for Reading.
About The Author:
Pallavi is a content writer in the field of data recovery and email migration. She has a passion for creating engaging and thought-provoking content. With several years of experience in the field, she has developed a deep understanding of various writing styles and formats,
Related Post
© Copyrights 2017-2023 Database File Recovery. All Rights Reserved.