“One of my colleagues accidentally ran DELETE command on a Table having most important data with a wrong WHERE clause. We use SQL Server 2014. We don’t have any backup of the data. How to recover deleted records in SQL Server 2014?”
The above-discussed query is a real-time issue raised by a user. There are times when users accidentally perform UPDATE operation or DELETE operation in SQL server 2005, 2008, 2012, 2014, 2016 database without adding WHERE clause or with wrong WHERE condition. This is one of the common scenarios that can occur during the management and maintenance of records where they face disastrous database situations. So in order to go back to the normal workflow, it is necessary to retrieve or get back deleted record in SQL Server 2016, 2014, 2012, 2008, 2005 database.
How to Recover Deleted Records in SQL Server Manually?
While considering manual solutions to restore deleted rows in the SQL Server database, the availability of Backup of your data is very important. It is sure that, if you have maintained a proper backup of your SQL Server Database time to time, it will help you to get back your deleted rows from SQL Server database.
Now there are prerequisites on your SQL Backup also. Because a differential backup does not help you always. To perform some available manual solutions, you need the full backup of your SQL Server database. Follow the steps if you are having the backup.
Are your Systems is in Full Recovery Mode or Logged Recovery Mode?
This is a direct question. The chance of recovering your deleted rows or records depends upon these recovery modes. In the case of retrieving deleted records in SQL Server manually using LSN (Log Sequence Number), the prerequisite is to have Full Recovery or Bulk-Logged Recovery Mode enabled in the Server.
How to Restore Deleted Record in SQL Server Using LSN?
The LSN (Log Sequence Numbers) are unique identifiers and each record in a table will be having individual LSN in a SQL Server Transaction Log file. So, a deleted record in a SQL table can be easily recovered if the time of their deletion is known correctly.
To recover deleted records in SQL Server using LSN, there are few prerequisites that are to be fulfilled as discussed above. For a fine recovery of deleted rows (records) from the table in SQL Server database, you need to have Full Recovery Model or Logged Recovery Model at the time when the data deletion took place.
Steps to Retrieve Deleted Records from Table in SQL Server
To recover deleted rows in SQL Server 2016, 2015, 2014, 2012, 2008 and 2005, follow the below steps without any mistakes.
Check the number of rows existing in the table from which records or rows are deleted.
SELECT * FROM Table_name
Now take a log back.
BACKUP LOG [DatabaseName]
TO DISK = N’D:\DatabaseName\RDDTrLog.trn’
WITH NOFORMAT, NOINIT,
NAME = N’DatabaseName-Transaction Log Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
Get the Transaction ID of deleted Records. It will help you to get information about deleted rows.
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
WHERE Operation = ‘LOP_DELETE_ROWS’
Now find specific time at which the rows (records) were deleted. The Transaction ID will help you to find this information. This steps will also help you to find the ongoing LSN.
[Current LSN], Operation, [Transaction_ID], [Begin Time], [Transaction_Name], [Transaction SID]
[Transaction ID] = ‘000:000001f3′
[Operation] = ‘LOP_BEGIN_XACT’
Now start to restore process to recover deleted data from records in SQL Server Table.
Recover Deleted D USE DatabaseName
RESTORE DATABASE DatabaseName_COPY FROM
DISK = ‘D:\DatabaseName\RDDFull.bak’
MOVE ‘DatabaseName’ TO ‘D:\RecoverDB\DatabaseName.mdf’,
MOVE ‘DatabaseName_log’ TO ‘D:\RecoverDB\DatabaseName_log.ldf’,
Finally, check the table whether deleted rows are recovered or not.
USE DatabaseName_Copy GO Select * from Table_name
All the above-discussed solutions can only help when you have the most updated backup of your database. In many scenarios, the above mentions steps fail. Because
Deficiency of good backup
Entry of new data in the rows
The case of altered database rows are not defined
Alternative Solution to Recover Deleted Records in SQL Server
The best alternative solution is available to retrieve rows from SQL Server 2016, 2014, 2012, 2008, 2005 etc. The alternative solution that we discuss here can resolve all kind issues that arise in the SQL Server. It can fix SQL errors and can repair SQL data files like MDF and NDF. The SysTools SQL Database Recovery is a commercial application which allows recovering Triggers, Rules, Functions, Tables, Stored Procedures easily without any data loss. The important part is, the application provides many advanced options while recovering and repairing SQL database files. Download the free demo edition to understand the proper function of the application.
After downloading the Software, launch the application, and open the MDF file.
Select Advance Scan option to recover deleted records in SQL Server.
After scanning process completes, select Export as SQL Server Database.
You need to provide the Database authentication details in the upcoming section.
Check ‘Create New Database’ option and provide a Database Name.
The software provides the option to export data ‘With only Schema’ or ‘With Schema & data’.
Now Export the Database ‘With schema & data’ so as to recover data in the deleted rows in the SQL Server.
Now save the report and open the newly created database which contains Recovered records.
To see proper step by step working of the software, watch the video given below. It clearly shows how to get back deleted records in SQL Server 2008, 2012, 2014, 2016 without any data loss.
It is sure that you would have got the solution for your query. Because the solutions discussed in the blog will definitely help you to recover deleted records in SQL Server. The manual, as well as automated application discussed in this blog, will help you in resolving the issue. If you couldn’t retrieve deleted rows in SQL Server 2008, 2012, 2014, 2016 version using LSN method, try the third party software to restore records in database files. It will help you, resolving the issue in very simple steps.
About The Author
Being a Chief Technical Operator, I am aware of the technicalities faced by the user while working with multiple technologies. So, through my blogs and articles, I love to help all the users who face various challenges while dealing with technology.