<

How to Recover Deleted SQL Data with the Help of Best SQL Recovery Software

Published on
388 Points
188 Views
2 Endorsements
Last Modified:
Bharat Bhushan
Experts in data recovery from corrupt Exchange mailboxes, SQL database, and Outlook emails.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and consists of using a SQL Recovery Software.

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.


Getting Started


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','jrambo@hotmail.com'),
(2, 'Janet','Jackson','jjackson@hotmail.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','jmalkovich@hotmail.com'),
(2, 'Jake','Jonson','jjonson@hotmail.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:


sp_helpfile



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.


Conclusion


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.

2
Comment
1 Comment
 
LVL 53

Expert Comment

by:Mark Wills
Good Article,

Even as a SQL Server "expert", always good to know about tools - especially when it can make life much easier.

Stellar Phoenix SQL Database Repair looks the goods.

Thanks for sharing :)

Cheers,
Mark Wills
2

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month