HELP RESTORE DROPPED TABLE!!!
-
I HAVE JUST INADVERTENTLY DROPPED A MAJOR TABLE WITHOUT BACKING UP MY DATABASE.
I HAVE LOST 10 DAYS OF DATA. PLEASE HELP!!!!!
-
- backup your transactionlog !
Do you have a fullbackup taken before this incident, to which the logbackup can be applied ?
If yes, restore the fullbackup with another dbname using the "with norecovery"-option, perform a point in time recovery of the logbackup up to the time you've dropped the table in question (also to that "another dbname"-db) using the "with recovery"-option. Now you can copy the table from your "another dbname"-db to your db.
- other options : 3-party tools :
- log-pi
- log-analyser
- ...
may help out (I have no experiance with them)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If you haven't got SQL backups (inc. tran log backups as in previous post) to cover the period immediately before the incident (but surely you must have???), your only hope is that a windows backup of the db file exists. You will have to retrieve the .dat file, then reattach it to a new DB. If your sys admins can't retrieve the file, I don't think there's anything you can do.
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Lumigent Log Explorer for SQL Server can apparently recover dropped tables.
http://www.lumigent.com/Products/le_sql/le_sql.htm
"Use Log Explorer to:
Identify the source of any data, schema, or permissions change
Receive instant notification via email when key database events occur
Quickly find and resolve application and user activity using powerful browse and filter capabilities against the transaction log
Undo a single operation or multiple transactions
Recover an entire dropped or truncated table "
-
Thank you all for your help and support... unfortunately...
I'll be recreating the last four days from PAPER! The last full backup and transaction logs only get me to Monday.
Hind sight is 20/20, and I'll take a look at those 3rd party products and have Transaction log backups at least nightly from now on.
We thought we were safe with weekly backups, but NO.
If anyone else reads this thread... remember me in your prayers.
-
So OS files on your database server aren't backed up?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
They are NOW! The network admin has egg on his face, too, for not including it in the backups, but not as much egg as mine. I've got an omelette on me and he has a few bits of egg and cheese.
Hard lesson to learn, friend. I hope I never have one of these again.
-
so, you have full and log-backups up til monday, right ?
You have full or bulk-logged recovery model ?
If you take a new log-backup (incremental), you can restore the full with norecovery, the first sequence of log with norecovery, the second ... and then the last with recover up top the certain point in time. Check BOL !
This can be done to an alternate db , even to an alternate sqlserver !
as long as your logbackup-chain is not broken.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yes, you don't have to punch in data from paper. If you haven't clobbered your database already, do a new transaction log backup now.
As said in the last post, what you need to do is restore your full-backup (maybe to a test server if there's new data you want to preserve) with norecovery. It's in books online, or if you're doing this from enterprise manager, there's an option for it. Then restore the new transaction log up to a certain point in time (oh, let's say about 10 minutes before you dropped the table).
This'll work as long as you have your database recovery model set to full, not simple.
-
We back up our transaction log every fifteen minutes during the business day and do a full backup every night when the system is quiet. Admittedly our database is fairly small, but this means that at worst, only 15 minutes of data entry need be redone in case of disaster.
As if all that wasn't enough, our entire network is backed up to tape nightly.
If disk space is not at a huge premium, I recommend backing up as often as possible. Murphy's Law being what it is, you'll be glad you did it.
Dana
Dana
Connecticut, USA
Dana
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply