Can a dropped table be recovered?

I'm guessing the answer is 'no', but I thought I'd ask. Basically we have a
client where it looks like someone has issued a DROP TABLE command - or at
least so far as we can tell. The table certainly isn't there.

No backups, of course. That would be _too_ easy.

MSSQL2000 (actually MSDE).

Any thoughts appreciated.

Jim
--
http://www.ursaMinorBeta.co.uk http://twitter.com/GreyAreaUK

"Sometimes when I talk to a Windows person about using a Mac,
I feel like I'm explaining Van Halen to a horse." Merlin Mann

re: Can a dropped table be recovered?

On Thu, 31 Jul 2008 11:45:15 +0100, Jim wrote:
Hi Jim,

The simple answer is, indeed, 'no'.

But there might still be *some* hope.

If the DROP TABLE was executed from within a transaction and that
transaction has not yet been committed, you can roll it back. But I
guess that this is not the case here. :)

Which means that only one hope remains - purchase a third-party product
(or download a trial version) that is able to read the log file and
reconstuct the table from that information. Whether this succeeds
depends on the recovery model used for the database, but it's always
worth a shot.

Well-known third-party programs that are able to read and decode the SQL
Server log are:
* Red Gate's SQL Log Rescue
http://www.red-gate.com/products/SQL...scue/index.htm
* Lumigent Log Explorer for SQL Server
http://www.lumigent.com/products/log_explorer.html
* ApexSQL Log
http://www.apexsql.com/sql_tools_log.asp

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

re: Can a dropped table be recovered?

On 2008-07-31, Hugo Kornelis <hugo@perFact.REMOVETHIS.info.INVALIDwrote:Sadly no transaction, and it's been rebooted by the client a few times as
well.

The odd thing is that I'd swear no-one there knows any SQL at all, so I
can't see one of them issueing a DROP command. The table is, however, not
there. Weird.

Not to worry. Thanks for the links though.

Jim
--
http://www.ursaMinorBeta.co.uk http://twitter.com/GreyAreaUK

"Sometimes when I talk to a Windows person about using a Mac,
I feel like I'm explaining Van Halen to a horse." Merlin Mann

re: Can a dropped table be recovered?

On Thu, 31 Jul 2008 15:06:51 +0100, Jim wrote:
Hi Jim,

Rebooting doesn't clear the transaction logs. Backing up the log does
(but then you have the log backup to use), as well as truncating the log
or having the database in simple recovery (and in either of these two
cases, you're (or rather, the client is) hosed.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

re: Can a dropped table be recovered?

Hugo Kornelis <hugo@perFact.REMOVETHIS.info.INVALIDwrote:
It was in Simple mode. Honestly, it's like they *try* to make things
difficult.

Jim
--
'Cloverfield' in nine words: "What is it?!" "We're gonna die!" BOOM!
Roll credits.

http://www.ursaminorbeta.co.uk http://twitter.com/greyareauk