How to Recover Deleted Records in SQL Server 2022
This tutorial is about how to recover deleted records in SQL Server. We will do our best for you to understand this guide. I hope you will like this blog How to Recover Deleted Records in SQL Server. If your answer is yes, please share after reading this.
Check how to recover deleted records in SQL Server
If you accidentally run a DROP or DELETE command on a table with an incorrect WHERE clause and important records were lost, you can recover them using one of the following methods.
Recover deleted data in SQL Server using LSN:
LSNs (Log Sequence Numbers) are unique identifiers assigned to each record in SQL Server transaction logs. Therefore, rows deleted from SQL tables can be recovered if the time of their deletion is known.
To start the recovery process, several prerequisites must be met to recover deleted data from SQL Server table using LSN (Log Sequence Number). For smooth recovery of deleted SQL Server database table rows, you must have a full recovery model or a recovery model saved at the time the information was deleted. Use the below mentioned steps for deleted data recovery from SQL Server 2016, 2015, 2014, 2012, 2008 and 2005.
- Use the below mentioned query to check the number of rows present in the table whose data has been deleted.
SELECT * FROM table_name
- Then, consider reconnecting using the query mentioned below:
USE database nameGOBACKUP LOG TO DISK = N’D:database nameRDDTrLog.trn’WITH NOFORMAT, NOINIT,NAME = N’Databasename-Transaction Log Backup’,SKIP, NOREWIND, NOUNLOAD, STATS = 10GO
- Collect information about deleted records from SQL Server table to recover data.
USE database name GOSelect LSN]Operation, Context, AllocUnitNameFROMfn_dblog(NULL, NULL)WHERE Operation = ‘LOP_DELETE_ROWS’
- Using this query, you will get the transaction ID of deleted records.
- You can run the query to find the specific time the records were deleted using the transaction ID.
USE Database nameGOSELECTOperation, , , , FROM fn_dblog(NULL, NULL) WHERE = ‘000:000001f3′Y = ‘LOP_BEGIN_XACT’
- Gets the current LSN using the previous query.
- Now recover the deleted data from the SQL Server table by starting the restore process.
Recover database name D USE deleted GORESTORE DATABASE database name_COPY FROMDISK = ‘D:database nameRDDFull.bak’WITHMOVE ‘database name’ TO ‘D:RecoverDB database name database.mdf’, MOVE ‘name of log_database’ TO ‘D:RecoverDBname of log_database.ldf’, REPLACE, DO NOT RECOVER; GO
- Finally, check if the deleted records are recovered in the SQL table database.
USE Databasename_Copy GO SELECT * from Table_name
Final Words: How to Recover Deleted Records in SQL Server
Hope you understand this article How to Recover Deleted Records in SQL Server, if your answer is no, you can ask anything via the contact forum section linked to this article. And if your answer is yes, share this article with your family and friends.