Recover data of a Dropped Table

Question

Recover data of a Dropped Table

sashikanta.mishra

Say Hey Kid

Points: 693
More actions
July 28, 2009 at 1:36 am

#207377

Hello Guys,
How can i recover a table (data and Structure) after i drop that table.
I am Using SQL EXPRESS 2005 version.I have recovery model set as FULL for the database.
Thanks & Regards,
Sashikanta Mishra!!

Viewing 10 posts - 1 through 9 (of 9 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

sashikanta.mishra (7/28/2009)
Hello Guys,
How can i recover a table (data and Structure) after i drop that table.
I am Using SQL EXPRESS 2005 version.I have recovery model set as FULL for the database.
Thanks & Regards,
Sashikanta Mishra!!

Restore it till point in time from the full and tran log backups

OR

restore the database with a different name and then import the table...

Pradeep Singh

sashikanta.mishra Say Hey Kid Points: 693 More actions · Answer 2

Please tell me how to restore from the transaction log

Thanks & Regards,

Sashikanta Mishra!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Do you have a backup of this 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sashikanta.mishra Say Hey Kid Points: 693 More actions · Answer 4

Hi Gill,

No i have not taken the Backup of the database before i drop the table. But any way i have taken the data of that table in a temp table. From a previous version of the same database i have copied the Structure and from the temp table i have recovered the data.

Thanks anyway.

Regards,

Sashikanta Mishra!!

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

sashikanta.mishra (7/28/2009)
Hi Gill,
No i have not taken the Backup of the database before i drop the table. But any way i have taken the data of that table in a temp table. From a previous version of the same database i have copied the Structure and from the temp table i have recovered the data.
Thanks anyway.
Regards,
Sashikanta Mishra!!

You got lucky, but I assume this lesson has convinced you to start taking backups ?

ashwin4all1 Ten Centuries Points: 1131 More actions · Answer 6

Hi

Shashi,

as per Brew said na this is lesson for the feture.We have to create good backup stategy on each user and system database,especially the database more than 500 MB should have following backup stategy:

1)weekly once Full database backup

2)Every 15 or 30 or 60 Minute once Transactional lOg backup

3)Every End of the Diff backup is very good.

Thanks and regards

Ashwin vp

Ashwin VP
CSC India ...

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

ashwin4all1 (7/30/2009)
Hi
Shashi,
as per Brew said na this is lesson for the feture.We have to create good backup stategy on each user and system database,especially the database more than 500 MB should have following backup stategy:
1)weekly once Full database backup
2)Every 15 or 30 or 60 Minute once Transactional lOg backup
3)Every End of the Diff backup is very good.
Thanks and regards
Ashwin vp

I don't think backup strategy has much to do with the size of the database. It should be based on the importance of the database. The size of the DB would only determine the details of how you carry out the strategy.

joshanil Valued Member Points: 67 More actions · Answer 8

use point in time recovery, if you know the time when the table is drop

for example the table was drop today 9.30AM

restore last full backup with norecovery option

then

restore last differential backup if any with norecovery option

then

apply all log files which backup before today 9.30AM with norecovery option

then

apply last log backup which you take after table drop with stopat '9.29' with recovery

here the time 9.29 means the table was drop 9:30 so we recover the db till 9.29 in this time the table was in the DB

then

after 9.29 updation do again.

or second option

follow the same procedure and restore db with new name or other destination till the time the table was in db then export the perticular table and import the exect location

this method is helpfull if we don't know the exect time to drop a object or we do not want to lose data after the point in time updation.

if i am wrong please help me to understand

thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Please note: Year old thread.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass