Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Unanswered: SQL 2008 R2 - Restore question - Rollback an accidental delete

    Hi- I have a client who accidentally deleted data from a table this morning around 10:15 AM. Classic case of accidentally did not highlight the WHERE condition in the DELETE statement and ran it.

    1. Database is in Full Recovery mode.
    2. Unable to locate the last backup file.
    3. Transaction Log file is around 128GB while the mdf is around 212 GB.

    Is there a way we could restore the database to a new name and take it back to 10AM, get the table data back and re-insert into the production database?

    Please let me know.

    Thanks
    Sarat

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    had the Same thing happen to me the boss did it (had told him a number of time you will break it one day)

    What i did was

    1 .. back it up

    2 .. drop the database the had the Missing Data attach the backup one exported the missing table
    drop that one attach the one with the missing data turn off the Key so when i did the import the autonumber did change in the missing table

    3 .. jump in msaccess (its what i know) link said table did the import append the missing data to the table that had the missing data

    4 .. back into SQL turn the KEY back on it worked

    5 .. got bears for the hole nite.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am a little fuzzy on myle's step number 2.

    If you had the last full back up, you could possibly backup your transaction log, restore your last full backup, and then do a "Point in time" restore of your transaction log stopping at the point just before your user hosed your data, but without that full database backup, if there was one, you are pretty hosed. Also I think the last full backup would have to probably coincide with the beginning lsn of that transaction log.

    I suspect that you had no disaster recovery plan in place and you are totally hosed. Sorry. I am not even a DBA anymore, but one of the first things I ask about in a new shop is how is disaster recovery handled. A trapeze artist always makes sure he has a net to catch him or her. A database developer should always have a plan in place for when things go south as well.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •