Database Repair

Top Two Methods to Recover Deleted Record From Table in SQL Server 2016/ 2014

Last updated on June 18th, 2019 at 05:32 am

Regular Database management is must to keep it up to the pace of entries added and queries executed. Database Administrators should find measures to improve the performance of database and keep all the data in an organized manner. Weekly backup, table structure, updating, deletion of unnecessary file are some of the procedures to maintain database in normal state. Sometimes it is observed that while using the delete operation important records gets deleted in SQL server and this causes Database corruption. DBA’s must restore accidentally deleted records as soon as possible. But the question comes ‘How’. Here we will know the steps on how to recover deleted records in SQL server manually and automatically.

Database administrator must be aware of the manual methods of SQL database recovery, so that quick action can be taken without disturbing the normal workflow. In case of recovery of deleted rows from SQL server, we can use the LSN expanded as Log Sequence Number. LSN helps out for recovery of accidental delete records in SQL server if the time when they are deleted is known.

What is LSN (Log Sequence Number)?

Every record that is made in the SQL Server transaction logs is assigned with unique identifiers. It acts as an ID to each record and if it is known then they can be retrieved easily. Hence, It is necessary to have LSN number and Full or Logged Recovery Model before recovery of deleted records in SQL Server 2016, 2014, 2012, 2008 and 2005

Retrieve Accidentally Deleted Records from SQL with LSN

Login to SQL Server and go to database from where records are deleted

In the console type the command SELECT * FROM Table_name. Replace Table_name with name of the table which had records

Log back database by following query

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

Now, check the transaction ID of deleted records and other information from the table by

USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation = ‘LOP_DELETE_ROWS’

When the transaction ID is obtained get the details and time at which records are deleted

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’

Now we have LSN, Transaction ID of records, use RESTORE command to recover deleted records from SQL Server

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

The command will restore all the deleted records back to table in SQL server. Verify and check the records are recovered via

USE Databasename_Copy GO Select * from Table_name

This will list the records available in table and you can check them.

Use Kernel for SQL Database Recovery Tool

If you are a beginner in a Database Admin Management then it is advised to avoid the manual method, this is because a single keyword or spelling mistake may result in execution of incorrect query which will corrupt the SQL database rather than restoring deleted records. So use the professional Kernel for SQL recovery tool and recover SQL table records.

Download Kernel for SQL Recovery

Kernel SQL Database recovery software is known for its high-quality recovery of files without any loss, it maintains the table structure, attributes, key constraints of the entries. Check steps for recovery via SQL database tool

  • Open the Kernel SQL Database Recovery Software in your computer
  • Now click on Open in the toolbar and a new window will open
  • Click on browse to add the SQL MDF or NDF File
  • Select Standard type and check-mark option to automatically detect SQL version, click Recover
  • When the tool completes scan, it shows deleted records which can be recovered
  • Select all or some of the records and click on Save option in toolbar
  • Select SQL server and then enter login details to save in Live database
  • There is choice to save deleted records as batch-file, select this option and save it in computer
  • Finally, click on OK

In these simple steps, DBA’s can get deleted data from table in SQL server in few minutes. So, it is recommended to go for Kernel Database Recover for SQL tool as it is easy to use and restore files without any error.

Leave a Reply

Your email address will not be published. Required fields are marked *