February 9, 2011 at 5:54 am
Hi ,
I accidentally did a DROP Table Tablename .
My last full backup was around 4 months back . THe table was created a week ago . ANd It the DB is in SIMPLE recovery mode ?
Is there a way I can recover this Table ??
Regards
Kay
February 9, 2011 at 6:12 am
Nope.
It's gone.
You really should reexamine your backup strategies. Once every four months, even in a development environment, is way too long. If it's a production environment, it means that the business is willing to put up with a four month data loss. I don't know many businesses that would like that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 9, 2011 at 6:15 am
ApexSQL claim their Recover product works even in SIMPLE mode.
Check this screenshot at Getting Started/Selecting Recovery Option.
I suggest you begin taking regular back ups straight away
February 9, 2011 at 6:28 am
MysteryJimbo (2/9/2011)
ApexSQL claim their Recover product works even in SIMPLE mode.Check this screenshot at Getting Started/Selecting Recovery Option.
I suggest you begin taking regular back ups straight away
I've got no clue on how the heck they are doing this, but I can't wait to see if it works for the OP!
February 9, 2011 at 6:30 am
He doesn't have the transaction logs too, how can he
recover back the tables from apex tool..
I dont think its possible,
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 9, 2011 at 6:33 am
I would like to see it work as well, though I am doubtful as their website states that on simple mode it degrades the quality and quantity of data recovable. still without any decent backups it seems like its worth a shot.
February 9, 2011 at 6:37 am
I guess what apex means is that as long as you start to recover from the log before doing any other transactions of any types then you have a good chance...
Kind of like those undelete softwares. As long as the data is not overwritten on the disk then you have some hope.
February 9, 2011 at 6:38 am
Ninja's_RGR'us (2/9/2011)
I've got no clue on how the heck they are doing this, but I can't wait to see if it works for the OP!
If it's run soon enough, before the log records are overwritten by new ones, maybe. Depends on size or log, activity on DB, other stuff, etc.
Oh, and Apex SQL log retains for about $1000. That's a fair bit of money for an accident.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2011 at 6:42 am
So long as the data page still exist and havent been reused, it may be possible to recover the table.
I'm pretty sure when a drop command is issued, the pointer is removed from the GAM/SGAM but the data pages themselves are not touched.
February 9, 2011 at 6:44 am
GilaMonster (2/9/2011)
Oh, and Apex SQL log retains for about $1000. That's a fair bit of money for an accident.
Most companies would buy a package from Apex rather than a single app. My previous company had 4 universal packages.
February 9, 2011 at 6:44 am
I suppose on pretty slow systems or off hours that makes sense to try. But on a reasonably heavily used system... you better be very quick with the keyboard.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 9, 2011 at 6:45 am
GilaMonster (2/9/2011)
Ninja's_RGR'us (2/9/2011)
I've got no clue on how the heck they are doing this, but I can't wait to see if it works for the OP!If it's run soon enough, before the log records are overwritten by new ones, maybe. Depends on size or log, activity on DB, other stuff, etc.
Oh, and Apex SQL log retains for about $1000. That's a fair bit of money for an accident.
Well at my rates, 4 days of work would be worth that 1000$ for that single incident.
One more option maybe the op didn't consider.
Do you have by any chance a copy of the script you use the create or load the table? In that case you could at the very least do a select into and then adjust the missing constraints / indexes from there.
February 9, 2011 at 6:46 am
Grant Fritchey (2/9/2011)
I suppose on pretty slow systems or off hours that makes sense to try. But on a reasonably heavily used system... you better be very quick with the keyboard.
Let's hope that this is a locally installed server and that he stopped using that db as soon as he saw the error! 😉
February 9, 2011 at 6:46 am
Thnanks for all the Info. I did a reload of the table from other source.
Regards
Kay
February 9, 2011 at 6:49 am
MysteryJimbo (2/9/2011)
I'm pretty sure when a drop command is issued, the pointer is removed from the GAM/SGAM but the data pages themselves are not touched.
Correctish, but without the allocation pages and without knowing where the first IAM or cluster root page is, you're going to be reading and manually cracking every page in the database. Not a trivial operation, even on a small database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply