Data deletion by mistake is a very common error that can cost thousands and in some cases millions of dollars in the industries and companies. Not only the users make this errors but also experienced DBAs have this problem in the daily tasks because of different reasons like a migration, backup errors or simple stress and human errors.
First solution, the transaction log file
The first option that we will see is an option related to checking the log file using the function fn_dblog included in SQL Server. This function is not officially documented, but you can find some articles on the internet.
Let’s say that we have a table named customers:
Create table customers ( Id int, Name varchar(40), Lastname varchar(50), Email varchar(30) )
Let’s insert some data on it:
insert into customers values (1, 'John','Rambo','firstname.lastname@example.org'), (2, 'Janet','Jackson','email@example.com')
We will now delete the rows:
delete customers where id=1 or id=2
To check the deleted information deleted in the log file, you can use the following query:
SELECT [Current LSN], [Transaction ID], Operation, Context, AllocUnitName, [Log Record Length], [Offset in Row] FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS' and allocunitname='dbo.customers'
The fn_dblog gets internal information and user information about the transaction operations in a legible way. Microsoft uses for internal use, but it is possible to use it if it is important.
Some important columns returned by this function are is the LSN which is the Log Sequence Number. It is a Log identifier to identify each record. The transaction ID identifies the transaction. The operation that we want to see is the delete operation (LOG_DELETE_ROWS). You have the Log Record Length, and the AllocUnitName is the object affected in this case.
To recover the information, you can restore the database at a specified LSN. You will first need to back up the transaction log:
BACKUP LOG db_name TO DISK = 'C:\sql\backupdb1a.TRN' GO
This sentence will back up the log file. The next step will be to restore the backup until the LSN where the last delete operation occurred.
RESTORE LOGdb_name FROM DISK = N'C:\sql\backupdb1a.TRN' WITH STOPBEFOREMARK = '0X0000000023:00000100:003'
In the STOPBEFOREMARK, you need to specify the LSN obtained from the fn_dblog function. The value of the Current LSN is the value used in the STOPFEFOREMARK. For more information about LSN and recovering this data, refer to the following link:
Second solution, Stellar Phoenix SQL Database Repair
Another solution is the Stellar Phoenix SQL Database Repair. This SQL Recovery Software can be used to recover deleted SQL data.
In order to test this tool, we will first create a simple table:
Create table customersStellar ( Id int, Name varchar(40), Lastname varchar(50), Email varchar(30) )
Insert some data in the table:
insert into customersStellar values (1, 'John','Malkovich','firstname.lastname@example.org'), (2, 'Jake','Jonson','email@example.com')
We will now delete some data from the table in order to test the recovery options:
delete customers where id=1 or id=2
Now, we will try to recover the deleted rows.
To do this, you need to stop the SQL Server Service. Before stopping the services check where are your MDF files (and log files) to repair. Use this query to show the MDF files in the database that you need to find the files:
The sp_helpfile is a system stored procedure used to list the data and log files including properties information like the path, filegroup, size, maxsize, growth and more.
You can Stop the service using the SSMS by right-clicking on the instance and press the Stop option in the context menu:
Press the select Database button and select an MDF file to repair (in this case to restore data):
We will check the option Include Deleted Records to recover the rows deleted. Once checked, press the Repair button to restore the deleted rows:
Once repaired, you will be able to see the deleted rows in SQL Server.
Note that you cannot recover truncated rows with this method. The truncate statement used to truncate all the rows does not retain information in the log to recover the data. The delete statement by the other hand can be restored in this situation.
In this article, we went through that how to recover deleted data from tables in SQL Server.The first method is to read the SQL Server transaction log and restore the data using the LSN. The problem with this method is that you need to convert the LSN, use a converter or use functions with may be tedious at least the first time.
The second method is to use Stellar Phoenix SQL Database Repair which is a simpler option, easier to use and learn. This method includes an option to recover deleted records.
If you have questions about recovering data, LSN, the Stellar Phoenix tool, feel free to contact us or write your comments.
Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.Get 7 days free