Recover Deleted Records in SQL Server 2008 / 12 / 14 / 16 / 17 / 19 / 22
“One of my colleagues accidentally ran DELETE command on a Table having most important data with a wrong WHERE clause. We use Microsoft 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, 2017, 2019 database without adding WHERE clause or with wrong WHERE condition.
This is one of the common scenarios that 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 recover deleted records in SQL server 2019, 2017, 2016, 2014, 2012, 2008, 2005 database.
In some cases, many people intentionally deleted the records from the SQL database. To analyze who deleted data from a Table in SQL Server and wants to recover deleted data from log file in SQL Server then, take the help of the SQL Log Analyzer Software. It is an efficient utility to read data of a .ldf file and recover in the designated format to understand the process of SQL Server recover deleted rows, stored procedures, views, etc, it can be of god use for all.
Table of Content
How to Recover Deleted Records in SQL Server Manually?
While considering manual solutions to restore deleted rows from the SQL Server database, the availability of .bak file 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. In other words, it’ll help you learn how to rollback deleted data in SQL easily. Also, it can work for records, stored procedures, SQL database functions, & etc objects.
Now there are prerequisites on your solution also. Because a differential backup does not help you always. Now, to perform some available manual solutions, you need the full backup of your SQL Server database. Follow the steps if you are having the files required.
- Restore .bak file of SQL database
- Keep both Databases individually
- Find removed data from .bak file
- Perform UPDATE operation on altered records
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. users must not skip the prerequisites in any case for SQL Server restore deleted records operation.
Also, the users who are quite proficient in SQL technicalities can easily tackle all the issues whereas the new users might face some trouble at first. Therefore, experts do not recommend this solution to everyone.
How to Restore Deleted Record in SQL Server Using LSN?
The LSN are unique in nature. Each record in a table will be having LSN in a unique SQL Server LDF file. Therefore, learning how to recover deleted rows in SQL Server can be easy 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. To learn how to rollback deleted data in SQL & specially deleted rows (records) from the table, 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 2019, 2017, 2016, 2015, 2014, 2012, 2008 and 2005, follow the below steps without any mistakes.
Recover Deleted Records in SQL Server with these Simple Steps
Step 1. Check the number of rows existing in the table with deleted records.
SELECT * FROM Table_name
Step 2. Now take a log backup.
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
Step 3. Get the Transaction ID of deleted Records.
This helps users to rollback deleted records in SQL with deleted rows information easily.
USE DatabaseName GO Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName FROM fn_dblog(NULL, NULL) WHERE Operation = ‘LOP_DELETE_ROWS’
Step 4. Now find specific time at which the rows (records) were deleted in order to get back SQL Server recover deleted rows, stored procedures, views, etc.
The Transaction ID will help you to find this information. This steps will also help you to find the ongoing LSN.
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’
Step 5. Begin to recover deleted data from records in SQL Server Table.
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
Step 6. 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 .bak files of your database. In many scenarios, the above mentioned steps gets fail. the reasons for the same are:
- Corruption in the .bak files.
- Entry of new data in the rows
- 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 2019 / 2017 / 2016 / 2014 / 2012 / 2008 / 2005. The alternative solution that we discuss here can resolve all kind issues that arises in the SQL Server. In addition, it can fix SQL errors and fix the database files like the primary MDF and secondary NDF files. SysTools SQL Database Recovery is a commercial application that allows recovering deleted SQL Database Functions, Triggers, Rules, Tables, etc.
In addition, it also restore Stored Procedures in SQL Server easily without any data loss. The important part is, the application provides many advanced options while repairing corrupted SQL Server database files. Also, this application shows the preview of deleted SQL table records in red color. This software is compatible with Microsoft Outlook 2019 and all its below versions.
Download the free demo edition to understand the proper function of the application for SQL Server restore deleted records.
Steps to Follow to Recover Deleted SQL Records in Red Color
1. After downloading the Software, Launch the application, and Open the MDF file.
2. Select Advance Scan option, Detect the SQL Server version to recover deleted records in SQL Server.
3. Solution will Start the Scanning process of the primary database file ( MDF)
4. Preview the SQL Database Components, tool shows the deleted SQL table records in red color. After that click on Export button.
5. You need to provide the Database authentication details in the upcoming section.
6. Check ‘Create New Database’ option and provide a Database Name.
7. Export the Database ‘With schema & data’ so as to recover data in the deleted rows in the SQL Server. ( Also check the checkbox Export deleted records)
8. Now save the report and open the newly created database which contains Recovered records.
The Quick Video to Learn the Entire Solution without Hassles
To see proper step by step working of the tool, 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. Moreover, this the best way to learn how to recover deleted records in SQL server.
Conclusion
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.
Also, if you couldn’t retrieve deleted rows in SQL Server 2008, 2012, 2014, 2016, 2017, 2019 version using LSN method, try the third party software to restore records in database files. Hence, it will help you, resolving the issue in very simple steps.
FAQs
Q-1. Is it possible to undo the command to get back deleted records in SQL DB?
Ans: Yes, using the automated solution mentioned above, there is no way that users can mess up. However, the manual solution might blunder at some point.
Q-2. What is the pricing structure of the software?
Ans: It is available in a one-time payment model. Also, It is quite affordable as well when we compare it to the problem.
Q-3. Can we recover deleted tables & triggers also with the automated tool?
Ans: Yes, along with records, this solution helps users learn how to rollback deleted data in SQL which includes triggers & tables.
Q-4. Which command to use to SQL query to find deleted records in SQL Server easily?
Ans: Follow the below command to get the solution manually. Keep in mind, that there are several restrictions & consequences of this method.
BEGIN TRANSACTION; DECLARE @DeletedRows TABLE ( -- Define columns matching the structure of your table Column1 datatype, Column2 datatype, -- Add more columns as needed ); DELETE FROM table_name OUTPUT DELETED.* INTO @DeletedRows WHERE condition; SELECT * FROM @DeletedRows; ROLLBACK TRANSACTION;