Recover Deleted Data In SQL Server

Sometime we delete data from database unknowingly and now we look for some solution to recover deleted data. The best solution is to restore data from last database backup. But what happen if there is time difference means there is time difference between the time backup was taken and time data deleted. Let’s see an example:

Database Backup Taken At - 4:00 PM  On 23rd Jun 2011

Data Deleted At –  10:00 PM  On 23rd Jun 2011

So if we follow the above solution to restore from backup than we will not get data entered or modified or deleted after 4:00 PM.

Now the solution to above problem is to use StopAt while restore database. In this case you can only recover data if your database Recovery model is: Full or Bulk Logged Recovery (Default model is Full Recovery Model)

Now, we have Full database backup taken at 4:00 PM (C:\VBDotNet_20110623_1600.BAK) and a database from which One table has been deleted. To recover Table we have to perform the following action:

Take Transaction Log Backup of database where table has been deleted.

Now we have two files: 1 Backup file taken at 4:00 PM and Transaction Log backup taken just now. Restore database backup with No Recovery Option:

RESTORE DATABASE [VBDotNet_Copy]
FROM DISK = N'C:\VBDotNet_20110623_1600.BAK'
WITH FILE = 1,
MOVE N'VBDotNet' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VBDotNet_Copy.mdf', 
MOVE N' VBDotNet_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\VBDotNet_Copy.LDF', 
NORECOVERY, STATS = 10
GO

Now Restore Transaction Log Backup with No Recovery and STOPAT option.

Restore Database [VBDotNet_Copy]
From Disk=N'C:\VBDotNet_log_20110623_2230.Bak'
With NoRecovery, STOPAT='2011-06-23 22:00:00'
Go

If more than 1 Transaction Log Backup then we will have to restore all the transaction log backup with No-recovery in the Sequence they were backed up.

Finally run below command to complete restore:

Restore Database [VBDotNet_Copy] With Recovery

Go

Now we have data till time specified with StopAt. We can copy data from Copied database to Actual Database.