Tuesday, November 1, 2011

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:
FROM DISK = N'C:\VBDotNet_20110623_1600.BAK'
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', 

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'
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

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


  1. Hello Arun,

    Thanks for sharing this informative information. I want to add one more option to recover SQL server database i.e 3rd party data recovery software. These recovery software efficiently & effectively recover deleted data. They will charge some amount according to their features.

  2. I want to share one more application that can be used to recover corrupt SQL MDF file & recover deleted SQL data. It provides you free demo facility which can help you to repair & see the preview of SQL database. Download this tool from