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 MasterGOCREATE 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
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 RecoverDeletedDataGORESTORE DATABASE RecoverDeletedData_COPY FROMDISK = 'D:\RecoverDeletedData\RDDFull.bak'WITHMOVE 'RecoverDeletedData' TO 'D:\RecoverDB\RecoverDeletedData.mdf',MOVE 'RecoverDeletedData_log' TO 'D:\RecoverDB\RecoverDeletedData_log.ldf',REPLACE, NORECOVERY;GO
Step12:
In step12 we will apply transaction log to restore deleted rows using LSN “00000025:000001d0:0001”
USE RecoverDeletedDataGORESTORE LOG RecoverDeletedData_COPY FROM DISK = N'D:\RecoverDeletedData\RDOTrLog.trn' WITH STOPBEFOREMARK = ‘lsn:0x00000025:000001d0:0001'
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_CopyGOSelect * from friends
Figure10. 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.
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.
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?
Comments (3)
Commented:
Commented:
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?
Commented:
Thanks for sharing the article and, I really appreciate it. The deleted records are also repairable by Stellar Phoenix SQL Database Repair software. Here is the article: https://www.experts-exchan