If you have accidently deleted data from database tables in MS SQL Server, you can restore the deleted data (tables) using Log Sequence Number (LSN). But for this, you need the transaction backup file. If you have not created backup or the backup file is not readable, then read this article to know the ways to recover deleted data from MS SQL Server without backup.
Ways to Recover Deleted Data from MS SQL Server without Backup
If you don’t have a backup of your MS SQL database, then follow the below ways to recover deleted data.
1 - Use the Transaction Log Files (.ldf)
Transaction log files record each change made to the database. If the error logging is enabled, you can use the transaction log files to recover the deleted data in MS SQL Server. This method is useful in scenarios where you have mistakenly executed the DELETE or UPDATE command or if your data was deleted due to corruption in the SQL database file. To check if the error logging is enabled, run the below command:
EXEC xp_readerrorlog 0, 1;
If the command returns any log (see the figure below), it indicates that error logging is enabled.
Now, you can use the fn_dblog() - an in-built function in SQL to view the transaction log files. The fn_dblog() function allows you to view the records in the active part of transaction log file. It accepts two parameters - the initial log sequence number and the ending log sequence number. You can also pass a NULL value to return all the logs. You can read the deleted records using this function. In the output values, the LOP_DELETE_ROWS operation contains the deleted rows while TransactionID and AllocUnitID provide details about the deleted rows. The column values are displayed in hexadecimal format.
Here are steps to recover deleted data from SQL Server using fn_dblog() function:
Note: For demonstration purpose, we will first create a new database, insert the data into it, delete some data from the tables, and then use the fn_dblog() function to recover the deleted data.
USE [master]; CREATE DATABASE TestRecoverDeletedData1; USE TestRecoverDeletedData1; GO CREATE TABLE Table1 ( [Sr.No] INT IDENTITY, [Date] DATETIME DEFAULT GETDATE (), [City] CHAR (25) DEFAULT 'City_name'); USE TestRecoverDeletedData1; |
|
Next, insert some data (rows) in the table (Table1) by executing the below statements: INSERT INTO Table1 DEFAULT VALUES; GO 100 |
|
Next, delete some data from the table by using the below command: USE TestRecoverDeletedData1; Go DELETE Table1 WHERE [Sr.No] < 16 GO Select * from Table1 |
|
The above command will delete all the rows with serial numbers less than 16 from the Table1. |
|
Now, check the deleted rows using the fn_dblog function (see the below example). USE TestRecoverDeletedData1 GO SELECT [Current LSN], [Transaction ID], Operation, Context, AllocUnitName FROM fn_dblog(NULL, NULL) WHERE Context = 'LOP_DELETE_ROWS' |
|
The above script will display all the deleted rows in the database. |
|
Check and locate the columns, like Transaction ID and AllocUnitName. These columns contain information about the deleted data. Now, you can recreate the deleted data by using this information. For this, use the below syntax: sql INSERT INTO Table1(Col1, Col2, ...) VALUES (RecoveredValue1, RecoveredValue2, ...); |
Limitations of using fn_dblog() to recover deleted records from SQL Server:
- It is an undocumented command. So, it is not recommended for a production environment.
- Analyzing the transaction log files can help you view the deleted records formatted in hex values, which is difficult to understand.
- It does not directly recover deleted records from MS SQL transaction log files. It only lets you view them. To retrieve data, you need to reconstruct data manually using the recovered values.
- It doesn’t guarantee complete or accurate data recovery.
2 - Use a Professional SQL Server Database Repair Tool
For quick and secure recovery of deleted records from SQL Server database, you can use a professional SQL database repair tool, such as Stellar Repair for MS SQL. The tool scans the SQL database (MDF/NDF) file and displays all the database objects, including the deleted records. You can then select and save the deleted records in a new database file. This helps you recover deleted data directly from the SQL database file without needing a backup file. You can even save the database objects in different file formats, like CSV, HTML, and XLS. The tool also provides a preview of the recoverable data before saving. It supports SQL Server 2019 and all the earlier versions.
Conclusion
If you’ve the database backup available and know the time of data deletion, you can use the transaction log with Log Sequence Number (LSN) to recover the deleted data from SQL Server. However if you don’t have a backup, then follow the methods discussed above to recover the deleted data, including rows and records, from the MS SQL Server database. If the error log is enabled, you can use the fn_dblog function to view the deleted records and then reconstruct them. However, if you want to recover deleted data in SQL Server directly, use an advanced SQL recovery tool, like Stellar Repair for MS SQL.
