Despirately need help! Must Rollback but dont 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!!!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

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!!!|||Any help is desperately needed and greatly appreciated!!!

Sure,

Here you go

http://www.dice.com/|||Sure,

Here you go...

Ouch! That's mean! :)|||Brett must have been up all night at the grindstone, sharpening his tongue. Yeouch!

-PatP|||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|||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 :p|||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

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 :p|||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.|||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?|||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).|||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|||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|||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.|||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|||Server has Dual everything: Dual Processor, Dual NIC cards, Dual SCSI Controller, etc. The SCSI cards operate in a load balancing capacity and if one fails, the network admin is notified while the other card handles 100% of the load until the admin can replace the failed card.

Correct me if I am wrong, but my understanding is this: in a RAID 5 setup (3 disk array in this case), they are simply partitions that take space scattered across the 3 drives and that you can't specify which partion goes on which disk ... that is the point of RAID is it not? That way, if one drive fails, you swap it out and it rebuilds the missing information from the mirrored information on the other two drives.

Of course, without a solid recovery model and appropriate planning, it doesn't matter how the data is stored :banghead:|||Nevermind ... I just reread your post and noticed you used the word "Volume" when I read it the first time, I interprited that as "disk".|||Been doing a lot of research in the last few hours since I have finished manually replacing all the data that I destroyed. I was wondering if anyone would recommend/discourage the use of Red-Gate Software's SQL Backup (http://www.red-gate.com/products/SQL_Backup/index.htm) and SQL Log Rescue (http://www.red-gate.com/products/SQL_Log_Rescue/index.htm), which is bundled together in a $490 package called the SQL Backup & Rescue Bundle (http://www.red-gate.com/products/sql_bundles/index.htm)