How to Restore Deleted Data from SQL Server 2019, 2017, 2016?
If you accidentally DROP or DELETE a table with an incorrect WHERE clause and lose important records. Then follow the blog to know how to restore deleted data from SQL Server 2019, 2017, 2016 version. Shows an example of restoring deleted data from an SQL Server using the Transaction Logs.
Deleted rows can be recovered if the deletion time is known. This can be done using the Registration Sequence Number (LSN). LSN is a unique identifier given to each record that exists in the SQL Server transaction log. The following sections will discuss the process of recovering deleted SQL Server data and tables with the help of transaction log and LSN.
Also Read: How to Fix DBCC CHECKDB Not Working in SQL Server?
Use Transaction Logs to Restore Deleted Data
Before starting the SQL Server deleted data recovery process, some prerequisites must be met. To easily recover deleted data items from a SQL Server database, you must have the BULK-LOGGED or FULL recovery model on first deletion. Some quick action is required so that the logs are still available for data recovery.
Follow the steps below to restore deleted data items from SQL Server 2019, 2017, 2016, 2014, 2012, 2008, and 2005 using transaction logs.
Step 1: Use the query mentioned below to check the number of rows in the table where the data was accidentally deleted:
SELECT * FROM Table_name
Step 2: Get a backup of the database transaction log using the 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
Step 3: To restore deleted data from SQL Server table, we need to collect information about deleted rows. Run the query given below to achieve this
USE Databasename GO Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS'
From the above query, we’ll get the transaction ID of the deleted row (for example, 000:000002r5). This ID will now be used to determine when those rows will be deleted.
Step 4: In this step, we will use the transaction ID 000:000002r5 to find the exact time the row was deleted. This is done by running the query below:
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'
Running this query will return the current Log Sequence Number (LSN) value (for example, 00000030:000002s0:0002).
Step 5: Now we will start the recovery process to restore the deleted data from the missing SQL Server table rows. This is done using the following query:
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: Now apply the transaction log with LSN 00000030:000002s0:0002 to restore the deleted row:
USE Databasename GO RESTORE LOG Databasename_COPY FROM DISK = N'D:\Databasename\RDOTrLog.trn' WITH STOPBEFOREMARK = ‘lsn:0x00000030:000002s0:0002'
Step 7: The SQL table deleted record restore process will complete successfully. Now check if the deleted record is back in the database named Databasename_Copy.
USE Databasename_Copy GO Select * from Table_name
Steps to Perform SQL Server Object Level Recovery
Limitations of Transaction Logs Steps
Transaction Logs are the first solution to perform for restoring SQL Server deleted data items. But this method also have some limitations, as mentioned below:
- The method of restoring deleted data from SQL Server tables is time-consuming because it involves several long queries to execute.
- It is extremely complex to implement for users who do not have enough technical knowledge.
- There is a higher chance of losing data due to errors in applying and executing queries.
Know How to Solve SQL Database Restore Failed Issue?
Automated Solution to Restore Deleted Data from SQL Server
RecoveryTools SQL Server Database Recovery Software can help you restore all the data items from SQL Server 2005, 2008, 2012, 2014, 2016, 2017 and 2019 in a few easy steps. In addition to deleted data, the software can restore all other objects such as Tables, Views, Triggers, Stored Procedures, etc., while maintaining data integrity.
This tool offers two scanning options: Quick and Advanced. Quickly scan healthy database files (.mdf and .ndf). Advanced scans of severely damaged SQL database files to restore deleted objects.
Easily run the tool in Windows OS 11 (64-bit), 10, 8, 7 (32-bit / 64-bit) and Windows Server 2016, 2012, 2008.
Also Read: How to Fix SQL Backup Database is Terminating Abnormally?
Concluding Lines
Although the SQL Server Log Sequence Numbers solution can restore deleted data, it is not recommended for users due to its complexity. Instead, an automated solution is recommended to restore deleted data items from SQL Server in Windows OS.
