How to Recover Deleted Records in SQL Server?
Read time 6 minutes
There are times when users face disastrous database situations in their SQL Server databases. The scenarios could take place during the management and maintenance of their database records. Sometimes the database records are deleted by mistake, due to which database administrators face many issues. Hence, to get back to the normal workflow, the records need to be recovered back to the SQL Server database.
Here are some suggested methods that you can use to recover deleted records in SQL Server database.
Methods to Recover Deleted Records in SQL Server
In this section, we will discuss manual as well as an automated method to recover deleted data from SQL Server.
Try Kernel SQL Database Recovery software to recover deleted records from SQL server database MDF/NDF files.
LSNs (Log Sequence Numbers) are unique identifiers that are assigned to every record in the SQL Server transaction logs. Hence, deleted rows of SQL tables are recoverable if the time of their deletion is known.
To start the recovery process there are several prerequisites to be fulfilled to recover deleted data from the SQL Server Table using LSN (Log Sequence Number). For smooth recovery of deleted rows from SQL Server database table, it should have Full Recovery Model or Logged Recovery Model at the time the data deletion took place.
Use the below-mentioned steps for recovery of deleted data from SQL Server 2016, 2015, 2014, 2012, 2008 and 2005.
Step 1: Use the below-mentioned query to check the number of rows present in the table from which the data got deleted.
Step 2: Next, consider taking log back with the help of the mentioned query below:
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: Gather information about the deleted records from the SQL Server table to recover data.
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = ‘LOP_DELETE_ROWS’
With the help of this query, you will obtain Transaction ID of deleted records.
Step 4: You can execute to the query to find specific time at which the records got deleted using the Transaction ID.
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’
You get the ongoing LSN with the help of above query.
Step 5: Now, recover the deleted data from the SQL Server Table by starting the restore process.
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: Lastly, check whether deleted records are recovered back to the SQL Table database.
Using Automated Solution:
The third-party automated software Kernel for SQL Database Recovery is the most trusted and recommended solution for efficiently recovering deleted records of SQL Server database files. The SQL server recovery software allows to recover data from healthy as well as corrupt MDF/NDF files of SQL Database. The tool restores all database objects like tables, triggers, functions, deleted records, rules, etc.
Follow the mentioned steps for flawless recovery using this software.
Step 1: Launch the software and click Open to load MDF/NDF database file.
Step 2: Click Browse to select MDF/NDF database files and select the SQL version if known or set it to autodetect and click Recover button.
Step 3: The tool scans and shows the full Preview of recovered database records which were deleted.
Step 4: You can save the data to Live SQL Server database by simply typing the login credentials.
Step 5: If you do not want to save to the current Live SQL Server, select Batch-File and select a location on your system to save the recovered data. This file can be accessed anytime whenever desired.
Step 6: The data is saved successfully to the desired location as shown.
Conclusion
We suggest you perform the manual method of recovery under expert guidance as the method is very tedious and takes a long time in the processing. Also, there are much chances that the data will not recover as the process does not guarantee about data recovery. Kernel SQL Data recovery is the best tool to recover corrupt or deleted database files. It performs the perfect recovery in every situation and brings your data back.
It is a nice tool to recover deleted records in SQL server, it helped me a lot.
Thank for sharing such a wonderful tool, It worked very well for me and I recovered deleted records.