Database Simplified Headline Animator

Database Simplified

Thursday 6 October 2011

Recover Deleted Data In SQL Server

This is common problem when we unknowingly delete data from database and look for solution to recover deleted data. Most common solution is we generally recover data from last “FULL BACKUP Of  Database” 

But What if The Duration between the Time Last Full Backup was Taken and the Time Data Deleted is very long. Let See Following Example.

Full Database Backup Taken At - 11:00 AM   On 1st Jan 2011

Data Deleted At –  6:00 PM  On 1st Jan 2011

So if you follow above common solution recovering data from FULL Database Backup then you probably will be able to get data till 11:00 AM only. What about data which modified or inserted between 11:00 AM to 6:00 PM ??

The Solution to Above Issue Is Use Of STOPAT while restoring database.

You Can only recover data if your database Recovery Model is : Full Or Bulk Logged Recovery (Default Is FULL Recovery Modal)

Ok So, We Are Left With Following Things

  • Full Backup Of Database Take At 11:00 AM
  • A Database Where Data From A Table has been deleted.

Perform Following Steps

  1. If You Have Taken Any Transaction Log Backup After Full Backup then You Must Have Those Log backup with you. If you have not taken any transaction log back then Goto Next Step
  2. Take Transaction Log Backup Of Database where data has been deleted.

Now Suppose You Have Following 2 Files.

  1. SQLDB_20110101_1100.BAK Taken At 11 AM
  2. TestDB_Log_20110101_0630.BAK  Taken Just Now
  3. Restore Full Backup Of Database With NoRecovery Option See Following Script For Example


  4. RESTORE DATABASE [SQLDB_Copy]
    FROM  DISK = N'D:\SQLDB_20110101_1100.BAK'
    WITH  FILE = 1, 
    MOVE N'SQLDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQLDB_Copy.mdf', 
    MOVE N'SQLDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQLDB_Copy_1.LDF', 
    NORECOVERY, STATS = 10
    GO
  5. Now Restore Transaction Log Backup With NoReovery With STOPAT option.


  6. Restore Database [SQLDB_Copy]
    From Disk=N'D:\SQLDB_log_20110101_0630.Bak'
    With NoRecovery, STOPAT='2011-10-06 21:30:00'
    Go
  7. If You Have More then 1 Transaction Log Backup  Then You Will Have To Restore All The Transaction Log Backup with No-recovery In the Sequence They Were Backed up.
  8. Finally complete your database restore operation with following command


  9. Restore Database [SQLDB_Copy] With Recovery
    Go
  10. Now You Have Data Till Time Specified With STOPAT,  You Can Move Data From CopyDatabase to Actual Database

NOTE : STOPAT Can Be Used Only With Transaction Log Backup.

5 comments:

  1. Hello Kuldeep,

    You have written very informative article. I want to add an important point that is Recovery of deleted records is also possible if your database is in simple recovery model. For this, you can try any third party recovery for SQL log explorer software like RedGate.

    Regards,
    Sam Joseph

    ReplyDelete
  2. Hi Sam
    Thanks A lot for sharing information. I am not sure But I think redgate works only for older versions like 2000.

    ReplyDelete
  3. Hi Kuldeep,

    This will recover everything until that date, but what if I am interested in just some of the records?

    This is an article which takes almost the same path as you did, but goes in the direction of letting you recover exact/specific records.

    http://sqlbak.com/blog/recover-deleted-data-in-sql-server/

    ReplyDelete
  4. Great share, I have read a lot of articles for deleted record recovery but this one is totally helpful. well, there is alternate options too i found while struggling with such issue. which is SQL Log Analyzer tool which can recover deleted data from transaction log file.

    ReplyDelete
  5. Great post. I was checking constantly this blog and I am impressed! Very useful information specifically the last part: I care for such information much. I was seeking this certain information for a very long time. Thank you and best of luck. Index: Lock Files It.

    ReplyDelete