<

Go Premium for a chance to win a PS4. Enter to Win

x

How to recover deleted rows in SQL Server

Published on
16,845 Points
13,445 Views
4 Endorsements
Last Modified:
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Note: All screenshots are applied to SQL Server 2012 Enterprise Evaluation Edition.
 
1. Introduction:
In this article we will get to know that how can we recover deleted data if it happens accidently. We can recover deleted rows if we know the time when data is deleted. We can achieve this goal using LSN ( Log Sequence Numbers ). As per Microsoft, “Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). We will use these LSNs to recover our deleted data.
 
2. Requirements:
To recover deleted rows from the table in database, the database must have the FULL Recovery or BULK-LOGGED Recovery Model at the time the deletion occurred, and be quick to act so the logs are still available for you to recover from.

SIMPLE Recovery doesn’t support transaction log backup hence it is not possible to recover from the SIMPLE recovery model.
 
3. Steps Involved:
Let’s set up environment for recovering deleted rows step by step (the first 8 set up the scenario, and important to step through):
 
Step1:
Create a new database named RecoverDeletedData and set recovery model as FULL (if it is not set as FULL by default). We will use this database to demonstrate (firstly) deleting some rows, and then, how we can recover those deleted rows from the transaction log.
 
So, let's get started... Use the commands below to create your sample database (you might need to change drive letters as/if necessary) :
 
USE Master
GO
CREATE DATABASE [RecoverDeletedData]
ON PRIMARY
( NAME = N'RecoverDeletedData',
FILENAME = N'D:\RecoverDeletedData\RecoverDeletedData.mdf'
SIZE = 4096KB, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'RecoverDeletedData_log',
FILENAME = N'D:\RecoverDeletedData\RecoverDeletedData.ldf',
SIZE = 1024KB,  FILEGROWTH = 10%)
GO

Open in new window



rdd1.jpeg Figure1. Creating database RecoverDeletedData
 
 
Step2:
In 2nd step we will create a table for our environment.
 
CREATE TABLE [Friends] (
    [Sr.No] INT IDENTITY,
    [Name] varchar (50),
    [City] varchar (50));

Open in new window


 
Step3:
After creating table “Friends”, let’s insert some values into it.
 
USE RecoverDeletedData
GO
 
insert into friends values (1, 'IanRox', 'Delhi')
insert into friends values (2, 'Jim', 'New York')
insert into friends values (3, 'Catherine', 'Las Vegas')
insert into friends values (4, 'John', 'California')
insert into friends values (5, 'Katie', 'Mexico')
insert into friends values (6, 'Sabrina', 'Indiana')
insert into friends values (7, 'Alfred', 'Hamburg')
insert into friends values (8, 'Vaibhav', 'Bangalore')
insert into friends values (9, 'Vijeta', 'Mumbai')
insert into friends values (10, 'YashRox', 'Sultanpur')

Open in new window


 
Step4:
After inserting records check the inserted records with below query:
 
USE RecoverDeletedData
GO
Select * from friends

Open in new window




rdd3.jpegFigure2. Fetching data from friends table of RecoverDeletedData database
 
Step5:
In step5, we will take full backup of database “RecoverDeletedData”
 
USE RecoverDeletedData
GO
BACKUP DATABASE [RecoverDeletedData]
TO DISK = N'D:\RecoverDeletedData\RDDFull.bak'
WITH NOFORMAT, NOINIT, NAME = N'RecoverDeletedData-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Open in new window


 
rdd4.jpegFigure3. Full Backup database RecoverDeletedData
 
 
Step6:
Now we will go ahead and delete some rows so that we can recover them with the help of LSNs.
 
USE RecoverDeletedData
GO
DELETE friends
WHERE [Sr.No] >5
GO

Open in new window


 
rdd5.jpegFigure4. Deleting rows from friends table
 
Step7:
Now check the “friends” again from below query:
 
select * from friends

Open in new window


 
rdd6.jpegFigure5. Fetching rows from friends table after deletion
 
Ah! only 5 records left as we deleted the records from 6th rows to 10th rows.
 
Step8:
Now take transaction log backup of the database.
 
USE RecoverDeletedData
GO
BACKUP LOG [RecoverDeletedData]
TO DISK = N'D:\RecoverDeletedData\RDDTrLog.trn'
WITH NOFORMAT, NOINIT,
NAME = N'RecoverDeletedData-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Open in new window


 
rdd7.jpegFigure6. Backup transaction log for RecoverDeletedData
 
Step9:
Now to recover deleted rows we must gather information for deleted rows. To gather information about deleted rows we can run below query:
 
USE RecoverDeletedData
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation  = 'LOP_DELETE_ROWS'

Open in new window


rdd8.jpegFigure7. Finding Transaction ID of deleted rows

From the above query we will get Transaction ID of deleted rows, now we have to find at what time rows got deleted.
 
Step10:
In step10 we will find exact time when rows got deleted with below query using Transaction ID “000:000002f1”:
 
USE RecoverDeletedData
GO
 
SELECT
 [Current LSN],  Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = ‘000:000002f1'
AND
    [Operation] = 'LOP_BEGIN_XACT'

Open in new window


 
After executing the above query we will get the current LSN “00000025:000001d0:0001” [ Apology, here I forgot to take screenshot]
 
Step11:
In step11 we will proceed with restore operation to recover deleted rows with below query:
 
USE RecoverDeletedData
GO
 
RESTORE DATABASE RecoverDeletedData_COPY FROM
DISK = 'D:\RecoverDeletedData\RDDFull.bak'
WITH
MOVE 'RecoverDeletedData' TO 'D:\RecoverDB\RecoverDeletedData.mdf',
MOVE 'RecoverDeletedData_log' TO 'D:\RecoverDB\RecoverDeletedData_log.ldf',
REPLACE, NORECOVERY;
GO

Open in new window


 
rdd9.jpegFigure8. Restoring with FULL backup
 
Step12:
In step12 we will apply transaction log to restore deleted rows using LSN “00000025:000001d0:0001”
 
USE RecoverDeletedData
GO
RESTORE LOG RecoverDeletedData_COPY FROM DISK = N'D:\RecoverDeletedData\RDOTrLog.trn' WITH STOPBEFOREMARK = ‘lsn:0x00000025:000001d0:0001'

Open in new window


rdd10.jpegFigure9. Restoring transaction log using LSN
 
Here one thing we need to remember that the LSN values are in hexadecimal form and for restoring with LSN value we need it in decimal form. To convert it to decimal form just put 0x before LSN like below in stopbeforemark clause
 
STOPBEFOREMARK = ‘lsn:0x00000025:000001d0:0001'
 
Step13:
As in above screenshot restore operation successfully completed so here we check that our deleted records are back in RecoverDeletedData_Copy Database:
 
USE RecoverDeletedData_Copy
GO
Select * from friends

Open in new window


 
rdd11.jpegFigure10. Fetching records from RecoverDeletedData_Copy database
 
Wow!! we got all records back…
 
In this way we can recover deleted records for any database provided database is in FULL or BULK-LOGGED Recovery model.
 
4: Final Words:
So from the above explanation and steps it is very clear that how we can recover deleted rows if we deleted it accidentally. But before that we must have an idea about LSNs, transaction logs etc.
 
Have a great day and happy recovery :)
 
4
Comment
2 Comments
 
LVL 10

Expert Comment

by:Jason clark
Thanks for sharing this helpful article. But in my scenario i didn't have any recent backup of my deleted records also the recovery mode is set to simple. Then I tried SysTools SQL Log Analyzer and successfully recovered deleted records via SQL Transaction log file. it's really helpful if you don't have any backup of your database.
1
 

Expert Comment

by:parag Deshmukh
Hi Yashwant,

I did not get one thing, when we are taking full backup of database before deletion of rows. Restore of that backup should directly give us all rows since we have taken backup before deletion of rows. Then why do we need transaction log backup?
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month