recover dropped table?

[SarahMS]

Help! I created a script of some views, and ran it on a client's server, not realizing that the option to script depentant objects would drop all tables the views were depentand on. The table I dropped has ALL their data on it!

Can anyone tell me of the best way to recover the table, or can it even be recovered? I would REALLY appreciate any help you can give.

Sarah

 

[tlbroadbent]

Is the database backed up?

If so, restore the database to a new database such as dbname_restore. Then copy the table from dbname_restore to dbname. Hopefully they have the correct options set to allow you recover to a point in time just prior to dropping the table. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[SarahMS]

No, unfortunately. Kind of combined responsibility here - I should have made a backup before doing ANYTHING to it, and the administrator should have had backups scheduled. The most recent backup was over two weeks old. I was hoping for some type of 'undo' or rollback command in sql, but it's sounding like there is nothing of the sort.

 

[tlbroadbent]

There isn't a SQL undo. There are third party products that could help recover but it's a too late for any of them to help now.

If you have a backup from two weeks ago and the transaction log is intact, you should still be able to do a point in time restore. Which version of SQL Server is running? What is the recovery model? Is the transaction log truncated or is it still intact? Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[SarahMS]

They are running sql 2000, and the backup is a .bak file. As far as the transaction log being truncated, I do not know enough about sql to know how to find out. However, I think I should be able to restore from the backup, if no other way, then to set up a completely new database and attach the .bak to it. As far as the recovery model, unless you were asking what type of file, I'm not sure what you are talking about.

I'm sorry I couldn't answer your question more intelligently, and thank you for your time.

 

[tlbroadbent]

Can the DBA assist you in recovery? I really don't expect every developer to know about administering a serever or databases so there is no need to apologize.

SQL 2000 has three ways to set up recovery - Simple, Full and Bulk-Logged. If the recovery model is simple, then the log is truncated on checkpoint and no point in time recovery is possible.

If one of the other recovery models is configured and the log was not truncated in some other way, point in time recovery should be possible. The DBA should do a transaction log backup and then restore fropm the full backup followed by a point in time restore from the transaction backup. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[SarahMS]

Thank you for your help.

Fortunately, the customer has been doing very little data entry in the past two weeks, so we think there are only 8-10 hours worth of data entry lost. We were able to succesfully restore from the backup.

Needless to say, we now have a scheduled backup job running frequently.

Thanks again for your help!

Sarah