Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2004
    Posts
    42

    Unanswered: Despirately need help! Must Rollback but don't know how!!!

    Don't ask, but I iadvertantly managed to delete 57,000+ records in the EM console and I need to roll back the database a few minutes to recover said information. I do not care about transactions that have occured since due to shutting the system down immediately upon realizing what I did. I have however never had the need to roll back the database before, so I have experience doing so. Any help is desperately needed and greatly appreciated!!!

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    ASSUMING:
    1. Your database is in Full Recovery mode (check properties)
    2. You have been making regular full backups of the database and these backups are stored on disk and accessible to the database server
    3. You have been making regular transaction log backups of the database and these backups are stored on disk and accessible to the database server

    THEN:
    If the date/time of the last transaction log backup is sufficiently recent for your recovery needs, proceed to step 2:

    1. Backup the committed transactions in the current transaction log:

    BACKUP LOG [databasename]
    TO [DEVICE]
    WITH NO_TRUNCATE

    2.
    RESTORE DATABASE [DatabaseName]
    FROM DISK = '[path to full backup]'
    WITH NORECOVERY
    RESTORE LOG [DatabaseName]
    FROM DISK = '[path to first log backup after last full backup]'
    WITH NORECOVERY
    RESTORE LOG [DatabaseName]
    FROM DISK = '[path to next log file in sequence]'
    WITH NORECOVERY
    .
    .
    .
    RESTORE LOG [DatabaseName]
    FROM DISK = '[path to last log file in sequence]'
    WITH RECOVERY, STOPAT = '10/06/2005 09:05'


    3. Smile confidently at your boss and say, "see, no problem!"


    Alternatively, you can do this through Enterprise Manager. Right click, All Tasks, Restore Database. HOWEVER, I have had problems in the past with the Point in Time recovery feature (it just blows right past the point in time and continues processing transactions).

    Best of luck and best regards,

    hmscott

    Quote Originally Posted by FastCougar
    Don't ask, but I iadvertantly managed to delete 57,000+ records in the EM console and I need to roll back the database a few minutes to recover said information. I do not care about transactions that have occured since due to shutting the system down immediately upon realizing what I did. I have however never had the need to roll back the database before, so I have experience doing so. Any help is desperately needed and greatly appreciated!!!
    Have you hugged your backup today?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by FastCougar
    Any help is desperately needed and greatly appreciated!!!

    Sure,

    Here you go

    http://www.dice.com/
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Brett Kaiser
    Sure,

    Here you go...
    Ouch! That's mean!
    Have you hugged your backup today?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Brett must have been up all night at the grindstone, sharpening his tongue. Yeouch!

    -PatP

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No I'm not, I'm being a realist...or maybe Mr lindman has been rubbing off.

    In either case, would you care lay the odds that this will be recoverable?

    better yet, does anyone want to make a wager
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Posts
    42
    The table information difference from last night to today was roughly 100 records. I was unable to do the recover due to a failure in the process. We needed to get back up and running ASAP, so I'm manually entering the missing 100 records.

    BTW, I still have a job

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    I'm not being snide or trying to be rude, but I hope you will take this as an opportunity to review your database backup and recovery procedures. Know when the backups are made, know where they're stored, and have it all written down somewhere close at hand. Have a script to recover available (you can actually create a job to have the recovery script generated periodically with correct values for file names and such).

    Then...

    practice.

    Practice recovering to a point in time.

    Practice recovering from disk.

    Practice recovering from files that have been moved to tape.

    Practice recovering multiple databases.

    Practice recovering an entire server.



    Congrats on the save (and on keeping your job). I hope to see you again on the forum. Don't mind Brett; he just hasn't had is fourth cup of coffee yet. :-)

    Regards,

    hmscott

    Quote Originally Posted by FastCougar
    The table information difference from last night to today was roughly 100 records. I was unable to do the recover due to a failure in the process. We needed to get back up and running ASAP, so I'm manually entering the missing 100 records.

    BTW, I still have a job
    Have you hugged your backup today?

  9. #9
    Join Date
    Feb 2004
    Posts
    42
    Not that it's an excuse, but I'm not a DBA. I'm a web applications developer who just happens to be in charge of the DB for this application/server. I'm learning alot, but unforutately, sometimes we must learn from our mistakes.

    Thanks for you help and input.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by FastCougar
    Not that it's an excuse, but I'm not a DBA. I'm a web applications developer who just happens to be in charge of the DB for this application/server. I'm learning alot, but unforutately, sometimes we must learn from our mistakes.

    Thanks for you help and input.

    Dude, I would have put money that you didn't have nightly backups...now what you need to do is create transaction dumps say every half hour....

    What is your recovery model?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Posts
    42
    Quote Originally Posted by Brett Kaiser
    Dude, I would have put money that you didn't have nightly backups...now what you need to do is create transaction dumps say every half hour....

    What is your recovery model?
    I'm a web applications developer who obviously knows enough SQL to get into trouble

    I am however NOT that dumb ... I have nightly backups with Full Recovery turned on

    Please, educate me if you don't mind ... how do I create transaction bumps (every 15 minutes would be great if performance isn't going to be an issue).

  12. #12
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by FastCougar
    I'm a web applications developer who obviously knows enough SQL to get into trouble

    I am however NOT that dumb ... I have nightly backups with Full Recovery turned on

    Please, educate me if you don't mind ... how do I create transaction bumps (every 15 minutes would be great if performance isn't going to be an issue).
    The pros will take exception to this recommendation, but I would use the Maintenance wizard (create two wizards, one for the system databases and one for user databases). All the parameters that you need to specify for full backups and log backups are there. Skip over the optimizations and consistency checks for now. In the System maintenance plan, skip the transaction log backups (master, model and msdb should be in Simple Recovery mode).

    Later on you can read about sp_sqlmaint.

    I know your pain. I've been there and I've done it.

    Regards,

    hmscott
    Have you hugged your backup today?

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Listen....as far as the state of things are now, the wizard should be used right now. How are your drives configured? Do you have a c and d partition?

    Here, check this out:

    http://weblogs.sqlteam.com/tarad/category/95.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Feb 2004
    Posts
    42
    Yes, I have a C and D partition. The machine is RAID 5/hotswap, so drive failure is taken care of. The DB is backed up locally and then tap backup is taken from this backup daily as well. I guess the only things lacking is my knowledge and a good solid recovery plan ... backups are being done.

  15. #15
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by FastCougar
    Yes, I have a C and D partition. The machine is RAID 5/hotswap, so drive failure is taken care of. The DB is backed up locally and then tap backup is taken from this backup daily as well. I guess the only things lacking is my knowledge and a good solid recovery plan ... backups are being done.
    Just to verify: are C: and D: partitions of the same RAID-5 disk volume? If so, you're not fully protected in the event of failure (say, for example, failure of the RAID card). For best results (performance and recoverability) the data and the log files must be on separate physical volumes.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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