Post reply

restore deleted table

  • February 11, 2012 at 10:30 pm

    #264377

    can you please help me with this issue

    we have full backup on every sunday evening 8pm

    differencial backup on every day evening 8pm

    and transaction log backup for every 4hrs

    like

    transaction log backup

    8am

    12am

    .....

    one of the user had deleted a table at 3 pm from the database

    but there is not transaction log backup from 2 hours

    then how can i get the deleted table

    can you please explain step by step

    Thank you in advance

  • February 12, 2012 at 1:20 am

    #1446005

    Restore the backups (created before the table drop operation) on a separate server and import the table back with Export Import Utility. Or generate script & data for the table and run it on PROD server.

    BTW how big the database & backups are?

  • February 12, 2012 at 1:41 am

    #1446007

    If the table was deleted exactly at 3pm, then this:

    Restore the latest full backup to a new database WITH NORECOVERY

    Restore the latest differential (that was taken before the table was dropped) WITH NORECOVERY

    Restore the log backups midnight, 4am, 8am, midday all WITH NORECOVERY

    Restore the 4pm log backup WITH STOPAT '14:59:59'

    If the drop table wasn't exactly at 3pm, you may need to try a few times with different times to get the point right before the table was dropped.

    With the restored DB, you can copy the table over to the real database and then drop the copy that you restored.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 12, 2012 at 2:12 am

    #1446010

    Thanks You very much

  • February 12, 2012 at 2:25 am

    #1446012

    Please don’t rush to implement the solution on PROD server if you are not confident enough. If you miss on any step, you should know how to overcome the issue.

    On safer side, please try it on test server. If successful, follow the same on PROD server.

  • February 12, 2012 at 7:21 am

    #1446038

    Worth pointing out, if you have some money to spend, another way around this issue is to look at a third party tool like Red Gate Virtual Restore[/url]. It allows you to mount a backup file as if it were a database, but not actually restore it, saving tons and tons of disk space, and some time. You can then query the backup exactly like a database and retrieve your table that way.

    Full disclosure, I work for Red Gate.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply