How to recover a dropped table

Question

How to recover a dropped table

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
November 28, 2012 at 9:51 am

#267506

Hi All,
Is there any way to recover dropped table, if the DB is in simple recovery mode and no backups....this is Dev environment.
From what I know, if the DB is in full reocvery mode, you will take a tail log backup and do a point in time recovery using the backups you have.
Is there any other way.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheMaskedDBA SSCrazy Points: 2787 More actions · Answer 1

No backups ? No recovery(At least I dont think so). But yes - in full recovery model, you could backup the log and do a point in time restore, provided you at leat have the initial full backup you used to create / restore the DB from.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

Yup....thats what I thought. Thanks.

SueTons.

Regards,
SQLisAwe5oMe.

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 3

There is no way. Sorry.

If it's a dev environment, you may not want to set up extensive backup routines (for many obvious reasons) which is yet another reason to have all database objects under source control so you can rebuild as needed.

"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

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 4

Grant Fritchey (11/28/2012)
There is no way. Sorry.
If it's a dev environment, you may not want to set up extensive backup routines (for many obvious reasons) which is yet another reason to have all database objects under source control so you can rebuild as needed.

Oddly eough, I do PIT backups more often (every 10 minutes) than I do for production (every 30 minutes). I've found that there's more data activity o the dev boxes because we work with some reasoably large batches of data. If a Developer screwed up and forgot to do a BEGIN TRAN prior to testing one of those large batches, I can easily bail him/her out without them having to waste a couple of hours trying to recreate the scenario. The deal is that whoever has me do such a restore has to buy me the lunch of my choice. Needless to say, the Devs have learned to pay attetion to what they're doing because I have to bring my lunch every day now. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)