SQL Server Portal

Today, we will discuss, how to recover the deleted data from SQL Server. We have two methods to do it.

1. Through SQL SERVER LOG
2. Through Backup

The first method I have already discussed in my previous article “How to recover deleted data from SQL Server”.

Today we will discuss how to recover it via database backup.
Mostly, we have a perception that if we have a FULL BACKUP before deletion we can easily restore it and can get the old data.
But this is wrong perception, even if you have an earlier full backup was taken about 4 to 8 hours before, you can still recover your deleted records.
I presume your database is in FULL Recovery model.

Let proceed this with the Step by Step approach. But remember date and time is very important in all the recovery process.

Step 1 :
First of all, take a full backup of the database.

BACKUP DATABASE test
TO DISK = 'c:\TEST_FULL_BACKUP.bak'
GO

Lets say, we took this full back up at 6:00 AM.

Step 2 :
Lets create one table and insert few records in it. But remember we took the full backup before doing all these transactions.

Create Table Test_Table
(
[ID] int,
[Designation] varchar(50)
)
Insert into Test_Table ([ID],[Designation]) Values(1,'Officer')
Insert into Test_Table ([ID],[Designation]) Values(2,'Manager')
Insert into Test_Table ([ID],[Designation]) Values(3,'Director')

We did all these transaction at 9:00 AM.

Step 3 :
Lets Delete the record. Lets say at 10.00 AM

Delete from Test_Table

Step 4 :
Lets check the records in the table

Select * from Test_Table

Step 5 :
Now, lets start the process of recovery.

First take a transaction log backup after deletion.

BACKUP LOG TEST
TO DISK = 'C:\TEST_TRANSACTION_LOG.bak'
WITH NORECOVERY;
GO

Step 6 :
After that restore the database FULL BACKUP, that we took at 6.00 AM

RESTORE DATABASE test
FROM DISK = 'c:\TEST_FULL_BACKUP.bak'
WITH NORECOVERY;
GO

Step 7 :
After that, lets restore the transaction log backup (we took after deletion) and specify the time. In the above example we deleted records at 10.00 AM. So we will restore the transaction log by 9:59 AM.

RESTORE LOG test
FROM DISK = 'C:\TEST_TRANSACTION_LOG.bak'
WITH RECOVERY,
STOPAT = 'Oct 09, 2012 09:59:00 AM'

Step 7 :
In the final step, you can browse the table and view the records what you have deleted. Now the records are recovered.

Select * from Test_Table

Read Full Post »