Recover a dropped table

Hello everyone
I have accidentally dropped some table from a SQL Server
2000 database, and would like to recover them.
Is there a way to recover a dropped table from a
database? I don't have backup files but the transaction
log hasn't been truncated (it's about 120 MB size). Is
the info somwhere on that log file? how can I recover it?
Thanks in advance for any reply
Antonio MiguelSurely you have some type of backup?
You can try Log Explorer from www.lumigent.com, I'm not sure whether they can re-create the table
based on the info in the tlog from a drop table statement. Also, if you never did a db backup (or if
the db is in simple recovery mode), then the log records might not be in the tlog files anymore (the
log might have been emptied since).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Antonio Miguel" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c392f8$a1096cc0$a401280a@.phx.gbl...
> Hello everyone
> I have accidentally dropped some table from a SQL Server
> 2000 database, and would like to recover them.
> Is there a way to recover a dropped table from a
> database? I don't have backup files but the transaction
> log hasn't been truncated (it's about 120 MB size). Is
> the info somwhere on that log file? how can I recover it?
> Thanks in advance for any reply
> Antonio Miguel
>|||Lumigent's (www.lumigent.com) Log Explorer may be able to save you here.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Antonio Miguel" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c392f8$a1096cc0$a401280a@.phx.gbl...
> Hello everyone
> I have accidentally dropped some table from a SQL Server
> 2000 database, and would like to recover them.
> Is there a way to recover a dropped table from a
> database? I don't have backup files but the transaction
> log hasn't been truncated (it's about 120 MB size). Is
> the info somwhere on that log file? how can I recover it?
> Thanks in advance for any reply
> Antonio Miguel
>|||Thanks Tibor and Allan for your replies.
I can tell you that the db is in full recovery model and
no backup copies have been made (I know it isn't very
smart).
I have also tried logExplorer from lumigent. However, it
doesn't seem to work. It looks as though there is no
trace of the deleted tables in the log file. But how can
this be?. I have not made modifications in the db since I
dropped the tables and the file is 120 MB in size while
the .mdf is only about 50 MB. I think that tables should
be there.
Regarding the logExplorer app, it seems to work once you
have it installed. I mean you install it and do what the
app call an "attach" to a db. Then if you drop a table
you can recover it back.
If you or someone figures another solution, please write.
Thank you in advance for any reply
Antonio Miguel
>--Original Message--
>Surely you have some type of backup?
>You can try Log Explorer from www.lumigent.com, I'm not
sure whether they can re-create the table
>based on the info in the tlog from a drop table
statement. Also, if you never did a db backup (or if
>the db is in simple recovery mode), then the log records
might not be in the tlog files anymore (the
>log might have been emptied since).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Antonio Miguel" <anonymous@.discussions.microsoft.com>
wrote in message
>news:08d501c392f8$a1096cc0$a401280a@.phx.gbl...
>> Hello everyone
>> I have accidentally dropped some table from a SQL
Server
>> 2000 database, and would like to recover them.
>> Is there a way to recover a dropped table from a
>> database? I don't have backup files but the transaction
>> log hasn't been truncated (it's about 120 MB size). Is
>> the info somwhere on that log file? how can I recover
it?
>> Thanks in advance for any reply
>> Antonio Miguel
>
>.
>|||> I can tell you that the db is in full recovery model and
> no backup copies have been made (I know it isn't very
> smart).
Until you perform your first database backup, the log may be truncated
as in the SIMPLE recovery model. This is probably why you have no
record of the action.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Antonio Miguel" <anonymous@.discussions.microsoft.com> wrote in message
news:0eb701c39313$7364e680$a101280a@.phx.gbl...
> Thanks Tibor and Allan for your replies.
> I can tell you that the db is in full recovery model and
> no backup copies have been made (I know it isn't very
> smart).
> I have also tried logExplorer from lumigent. However, it
> doesn't seem to work. It looks as though there is no
> trace of the deleted tables in the log file. But how can
> this be?. I have not made modifications in the db since I
> dropped the tables and the file is 120 MB in size while
> the .mdf is only about 50 MB. I think that tables should
> be there.
> Regarding the logExplorer app, it seems to work once you
> have it installed. I mean you install it and do what the
> app call an "attach" to a db. Then if you drop a table
> you can recover it back.
> If you or someone figures another solution, please write.
> Thank you in advance for any reply
> Antonio Miguel
>
> >--Original Message--
> >Surely you have some type of backup?
> >
> >You can try Log Explorer from www.lumigent.com, I'm not
> sure whether they can re-create the table
> >based on the info in the tlog from a drop table
> statement. Also, if you never did a db backup (or if
> >the db is in simple recovery mode), then the log records
> might not be in the tlog files anymore (the
> >log might have been emptied since).
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Antonio Miguel" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:08d501c392f8$a1096cc0$a401280a@.phx.gbl...
> >> Hello everyone
> >>
> >> I have accidentally dropped some table from a SQL
> Server
> >> 2000 database, and would like to recover them.
> >> Is there a way to recover a dropped table from a
> >> database? I don't have backup files but the transaction
> >> log hasn't been truncated (it's about 120 MB size). Is
> >> the info somwhere on that log file? how can I recover
> it?
> >>
> >> Thanks in advance for any reply
> >>
> >> Antonio Miguel
> >>
> >
> >
> >.
> >