10-22-15, 18:22 #1Registered User
- Join Date
- Jun 2003
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.
10-22-15, 22:11 #2(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
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
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
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
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
10-23-15, 15:13 #3Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
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.