Can a dropped table be recovered?

[Jim]

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

[Hugo Kornelis]

Re: Can a dropped table be recovered?

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

>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

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_Log_Rescue/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

[Jim]

Re: Can a dropped table be recovered?

On 2008-07-31, Hugo Kornelis <hugo@perFact.R EMOVETHIS.info. INVALIDwrote:

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

>>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.

>
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. :)

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

[Hugo Kornelis]

Re: Can a dropped table be recovered?

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

>Sadly no transaction, and it's been rebooted by the client a few times as
>well.

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

[Jim]

Re: Can a dropped table be recovered?

Hugo Kornelis <hugo@perFact.R EMOVETHIS.info. INVALIDwrote:

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.

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