Recovery of dropped data that was never back up

Hi

I am trying to recover a set of tables with data that was dropped. This data was not backed up. Please advice, is there any way this data can be recovered.

With SQL, the way to go back in time is to restore backups. You really can't recover dropped tables without any backups.

Depending on how valuable and critical the information is (which begs the obvious question of why it wasn't backed up if it was valuable), you may be able to do some forensic data mining on the files themselves. The data is not actually overwritten, just marked as free mujch as the filesystem does. So as long as the space has not been reused, there may still be information out there. SQL has no utilities for this, however.

You can either examine the raw files with a hex editor, or someone may know of a 3rd-party utility to make it slightly easier. None of this is of course supported.

Recovering dropped SQL database

I am in a big trouble ,

accidently i have issued a DROP DATABASE XXX command in the sql client, thinking its a local server....... The whole 4 months of database database is now dropped.

Please help me , how can i recover the database ....

thanks in advance

If you don′t have a backup of the data, I will have to tell you, that there is no UNDO button for that. Sorry.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

recover Dropped table

Hi,
This is a dissaster.
tables are dropped from the database, which there is no transaction log (it
was set up with simple recovery mode.)
Data has never been backup.
Is there anyway that the data can be recover from those dropped table.
Thanks,
DaraDara,
If there is no transaction log, Lumigent won't help you.
If the data has never been backed up, then I have nothing else to offer you.
(If you had a backup, then you could restore it to another database and copy
the tables from the restore database back to your working database.)
Russell Fields
Crazy Idea: If ABSOLUTELY NOTHING has been done to the database since the
tables were dropped and if you are willing to pay for the help, Microsoft
might be able to root out the tables from the database structure. This is
NOT likely though.
"Dara Bunhim" <bdara@.racha.org.kh> wrote in message
news:eCQbzvtcDHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> This is a dissaster.
> tables are dropped from the database, which there is no transaction log
(it
> was set up with simple recovery mode.)
> Data has never been backup.
> Is there anyway that the data can be recover from those dropped table.
> Thanks,
> Dara
>|||Not sure if this will work. If there was a file system backup, recover data
and log files from the backup before the table was dropped and do sp_attach.
Good luck!
"Russell Fields" <rlfields@.sprynet.com> wrote in message
news:ej3axNucDHA.1532@.TK2MSFTNGP10.phx.gbl...
> Dara,
> If there is no transaction log, Lumigent won't help you.
> If the data has never been backed up, then I have nothing else to offer
you.
> (If you had a backup, then you could restore it to another database and
copy
> the tables from the restore database back to your working database.)
> Russell Fields
> Crazy Idea: If ABSOLUTELY NOTHING has been done to the database since the
> tables were dropped and if you are willing to pay for the help, Microsoft
> might be able to root out the tables from the database structure. This is
> NOT likely though.
> "Dara Bunhim" <bdara@.racha.org.kh> wrote in message
> news:eCQbzvtcDHA.3520@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > This is a dissaster.
> >
> > tables are dropped from the database, which there is no transaction log
> (it
> > was set up with simple recovery mode.)
> >
> > Data has never been backup.
> >
> > Is there anyway that the data can be recover from those dropped table.
> >
> > Thanks,
> > Dara
> >
> >
>

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