Question about restoring transaction log

Hello everyone
I have accidentally deleted a whole bunch of tables with
data from a SQL Server 2000 database (I kwon this soesn't
look not very clever but ... I thought a DSN was
pointing to my MSDE on my PC but it was pointing to the
Win 2000 server). I hadn't made any backup copies yet but
have read that all the information the SQL DBMS manages
is saved in a file called the transaction log. My
transaction log is about 120 MB (the real one on the
server) and hope that the info I have deleted is still
stored there somehow.
The question I pose here is obviously how can I restore
back this tables if posible.
Thank you in advance for any reply
Antonio MiguelHi,
This is assuming your database's recovery model is not set to "Simple".
You are going to do a point-in-time restore. First do a full database
backup make sure "Delete Inactive Entries from Transaction Log" is not
checked; then, backup the transaction Log.
I usually use Enterprise Manager for restoring databases, BOL has the steps
for using Query Analyzer. Using EM, when restoring the database check the
box beside "Point in time restore" enter the time prior to when you deleted
the tables.
"Antonio Miguel" <amgarcia@.dephimatica.es> wrote in message
news:00d001c39276$27386770$a401280a@.phx.gbl...
> Hello everyone
> I have accidentally deleted a whole bunch of tables with
> data from a SQL Server 2000 database (I kwon this soesn't
> look not very clever but ... I thought a DSN was
> pointing to my MSDE on my PC but it was pointing to the
> Win 2000 server). I hadn't made any backup copies yet but
> have read that all the information the SQL DBMS manages
> is saved in a file called the transaction log. My
> transaction log is about 120 MB (the real one on the
> server) and hope that the info I have deleted is still
> stored there somehow.
> The question I pose here is obviously how can I restore
> back this tables if posible.
> Thank you in advance for any reply
> Antonio Miguel|||If Antonio never did a database backup, then the log is truncated at 70% full even if the recovery
mode is full. This is called the "auto-truncate mode" and is not the same thing as simple recovery
mode.
So, Antonio's best bet might be Log Explorer from www.lumigent.com.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Bianca Blount" <blountb@.noemail.com> wrote in message
news:u0%23aBMokDHA.2776@.tk2msftngp13.phx.gbl...
> Hi,
> This is assuming your database's recovery model is not set to "Simple".
> You are going to do a point-in-time restore. First do a full database
> backup make sure "Delete Inactive Entries from Transaction Log" is not
> checked; then, backup the transaction Log.
> I usually use Enterprise Manager for restoring databases, BOL has the steps
> for using Query Analyzer. Using EM, when restoring the database check the
> box beside "Point in time restore" enter the time prior to when you deleted
> the tables.
>
> "Antonio Miguel" <amgarcia@.dephimatica.es> wrote in message
> news:00d001c39276$27386770$a401280a@.phx.gbl...
> > Hello everyone
> >
> > I have accidentally deleted a whole bunch of tables with
> > data from a SQL Server 2000 database (I kwon this soesn't
> > look not very clever but ... I thought a DSN was
> > pointing to my MSDE on my PC but it was pointing to the
> > Win 2000 server). I hadn't made any backup copies yet but
> > have read that all the information the SQL DBMS manages
> > is saved in a file called the transaction log. My
> > transaction log is about 120 MB (the real one on the
> > server) and hope that the info I have deleted is still
> > stored there somehow.
> > The question I pose here is obviously how can I restore
> > back this tables if posible.
> >
> > Thank you in advance for any reply
> > Antonio Miguel
>|||Thanks for the information. I have not heard of "Auto-Recovery" mode. What
is it and is it something that can be controlled by the DBA?
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:O6OQzgukDHA.708@.TK2MSFTNGP10.phx.gbl...
> If Antonio never did a database backup, then the log is truncated at 70%
full even if the recovery
> mode is full. This is called the "auto-truncate mode" and is not the same
thing as simple recovery
> mode.
> So, Antonio's best bet might be Log Explorer from www.lumigent.com.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>|||Not "auto recovery". It is called "auto-truncate". This means that if SQL Server know that a backup
of the transaction log will be useless (say you never performed a database backup in the first
place, for instance), then SQL Server will empty the log when it is 70% full. This is badly
documented in Books Online. Last time I looked it took a long time before I found the few word that
BOL mentions on this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Bianca Blount" <blountb@.noemail.com> wrote in message news:ei$zeDxkDHA.2776@.tk2msftngp13.phx.gbl...
> Thanks for the information. I have not heard of "Auto-Recovery" mode. What
> is it and is it something that can be controlled by the DBA?
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:O6OQzgukDHA.708@.TK2MSFTNGP10.phx.gbl...
> > If Antonio never did a database backup, then the log is truncated at 70%
> full even if the recovery
> > mode is full. This is called the "auto-truncate mode" and is not the same
> thing as simple recovery
> > mode.
> >
> > So, Antonio's best bet might be Log Explorer from www.lumigent.com.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
>