How to recover data when a table is dropped in a database

Question

How to recover data when a table is dropped in a database

Chand00

SSCertifiable

Points: 6466
More actions
December 1, 2007 at 11:10 pm

#201371

Dear All,
One of my colleagues had dropped a table from the production database. We would like to recover the single table instead of complete database. Our database in simple recovery module. Our full backup is yesterday night 10pm and latest diff is around 2 o clock and the table was dropped between 2.30 to 2.45
Thanks & Regards
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]

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

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

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 1

There is no way u can recover single table. You do have to recover whole database using ur full backup then get the diff backup restore and from the recovered database, get the table you want to recover, using select * into currentdatabase.dbo.droppedTable FROM RecoveredDatabase.dbo.dropppedtable

Ian Yates SSCoach Points: 19738 More actions · Answer 2

And whilst it won't help you this time, if you run in full recovery mode you could at least recover up to the minute in such situations. Production DBs should almost always be in this mode.

Hopefully you haven't lost too much data! 🙂

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

If you use one of the third party backups, LiteSpeed or Red Gate Backup, they might do object recovery for you (depends on version). SQLCompare from RedGate, does comparisons from backups as well. That might help.

Vivien Xing SSChampion Points: 12297 More actions · Answer 4

using "select * into currentdatabase.dbo.droppedTable FROM RecoveredDatabase.dbo.dropppedtable" will get the data only. You may lose the index, constraint, or permission if any.

You can script out the table schema from the restored database (full + diff) to recreate the table in the current database, then use "select * into..." to load the data. Or use DTS to transfer the missing table, it has option to add index, permission, etc.

David Zebrowitz Right there with Babe Points: 739 More actions · Answer 5

Simply restore your last good backup to a new temporary database on the server. Then script the table from temp db making sure to include indexes, etc.

Once the table is recreated in your prod db, simply run an insert into statement to copy the data.