Step By Step Process To Recover Deleted Records From SQL Server Table
The task of creating tables, storing data in records look quite easy to SQL Server users. But if the data is being deleted by mistake or because of some other hardware or software issues, then the situation becomes complex. Recovery of deleted data is not a child’s play. So, considering this issue we have come up with this write-up which will help you to know various methods to answer your query how to recover deleted data from SQL server table by transaction logs? Let’s begin with a detailed discussion on the same.
Techniques to Rely On For Recovering The Deleted Data From Server:
- Manual Method: – Using LSNs (Log Sequence Numbers), but it works only if the time of deletion is known to the user.
- Automated Solution: – Simple yet secure and reliable solution for recovering deleted data from the server by using SQL database recovery software.
Know-How to Recover Deleted Data From SQL Server Table by Transaction Logs
Deleted Records’ Recovery Using SQL Server LSN:– In SQL Server transaction logs, the LSN(Log Sequence Number) is nothing but unique identifiers assigned to each record. Here we can restore the deleted rows of SQL tables if the time when the record was deleted is known.
User has to be ensured that the Full Recovery Model or Logged Recovery Model were created when the data was actually deleted for starting the recovery process. This is the prerequisite for the successful recovery of the deleted records.
The steps are described below to recover the deleted data from SQL Server 2016, 2015, 2014, 2012, 2008 and 2005.
Step 1: Fire the following query to know the total number of records in a table where from the record is being deleted.
Select * From Table_Name
Step 2: Next, run the procedure to take log back using below-mentioned query:
USE NameOfTheDatabase
GO
BACKUP LOG (NameOfTheDatabase)
TO DISK = N’D:\ NameOfTheDatabase\RDDTrLog.trn’
WITH NOFORMAT, NOINIT,
NAME = N’NameOfTheDatabase-Transaction Log Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Step 3: Information has to be collected from the SQL Server table about the deleted records for data recovery. This query will retrieve Transaction ID of the deleted records.
USE NameOfTheDatabase
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = ‘LOP_DELETE_ROWS’
Step 4: Execute the query given below to know at what time exactly the records get deleted.
USE NameOfTheDatabase
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’
Ongoing LSN you will be able to find now query.
Step 5: Restore process has to be run to restore the deleted data from the SQL Server Table.
Recover Deleted R USE NameOfTheDatabase
GO
RESTORE DATABASE NameOfTheDatabase _COPY FROM
DISK = ‘D:\ NameOfTheDatabase \RDDFull.bak’
WITH
MOVE ‘NameOfTheDatabase’ TO ‘D:\RecoverDB\ NameOfTheDatabase.mdf’,
MOVE ‘NameOfTheDatabase _log’ TO ‘D:\RecoverDB\ NameOfTheDatabase_log.ldf’,
REPLACE, NORECOVERY;
GO
Step 6: now is the time to verify if deleted records are recovered or not.
USE NameOfTheDatabase_Copy GO Select * from Table_name
Efficient Way to Recover Deleted Records From SQL Server 2017 / 2016 / 2014
If you failed to recover deleted data from SQL server table by transaction logs then you can take the help SQL Database Recovery Program. This software provides you the option to recover deleted records from SQL server table. Also by using this software, the user can preview accidentally deleted SQL tables records in red color. The user can easily recover database objects such as tables, functions, stored procedure. Moreover, This application is compatible with SQL server 2017 and its below version.
Follow The Steps to Recover Deleted Records From SQL Server Table
- Download and Install the software on your machine.
- Click on Add file button and add the MDF file in the software.
- Now choose the Scan option and select the SQL server version.
- Check the option preview deleted SQL database records in red color.
- Preview the SQL server database items. The software will preview the deleted SQL table records in red color.
- And click on Export button to Export the SQL database.
- Now in database authentication choose the server name and the authentication mode.
- Now choose the destination database.
- Check the database items you want to export.
- Choose the option from with only schema and schema and data.
- Mark the option Export deleted records and click on Export button.
Final Words
In this article, we have discussed how to recover deleted data from SQL server table by transaction logs. The manual solution is quite lengthy and difficult to perform. It requires strong technical knowledge. So it is better to take the help of expert solution to recover deleted records easily.