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. Alos, an efficient utility to read data of a .ldf file and recover in the designated format.
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 Backup 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.
Now there are prerequisites on your SQL Backup 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 backup.
- Restore the backup of SQL database
- Keep both Databases individually
- Find removed data from backup
- 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.
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, 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. 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.
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 backup of your database. In many scenarios, the above mentioned steps gets fail. Because
- Deficiency of good backup
- 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 repair SQL MDF and NDF files. SysTools SQL Database Recovery is a commercial application which allows recovering Triggers, Rules, Functions, Tables.
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.
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. Software Will Start the Scanning process of the primary database file ( MDF)
4. Preview the SQL Database Components, Software 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 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. 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 rollback deleted records in SQL Server database?
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: The software 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;