Regular Database management is a must to keep up to the pace of entries added and queries executed. Database Administrators should find measures to improve the performance of the database and keep all the data in an organized manner. Weekly backup, table structure, updating, deletion of unnecessary files are some of the procedures to maintain the database in a normal state. Sometimes it is observed that while using the delete operation important records get deleted in the 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.
The 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 the recovery of accidental delete records in the 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 it 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 the 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 the table in the 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 the table and you can check them.
Use Kernel for SQL Database Recovery Tool
If you are a beginner in Database Admin Management then it is advised to avoid the manual method, this is because a single keyword or spelling mistake may result in the execution of an 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.