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

question about process order

Hi all,
I have a trigger for update on a table. It does a whole bunch of things,
then calls a SP. I want to then check something afet the SP is completed. To
check this out I put a print statement after the line that called the SP
exec calcdeductables @.claimid, 1
print 'Check SIP'
The SP, by nature update multiple rows in child tables, as A result I would
expect the following:
(1 row(s) affected)
(1 row(s) affected)
(4 row(s) affected)
But with the print statement I am gettingh
(1 row(s) affected)
(1 row(s) affected)
Check SIP
(4 row(s) affected)
Should'nt the Check sip statement come last
Thanks
RobertRobert Bravery (me@.u.com) writes:
> I have a trigger for update on a table. It does a whole bunch of things,
> then calls a SP. I want to then check something afet the SP is
> completed. To check this out I put a print statement after the line that
> called the SP
> exec calcdeductables @.claimid, 1
> print 'Check SIP'
> The SP, by nature update multiple rows in child tables, as A result I
> would expect the following:
> (1 row(s) affected)
> (1 row(s) affected)
> (4 row(s) affected)
> But with the print statement I am gettingh
> (1 row(s) affected)
> (1 row(s) affected)
> Check SIP
> (4 row(s) affected)
> Should'nt the Check sip statement come last
A more interesting question is from where you got that @.claimid. If you
do
SELECT @.claimid = claimid FROM inserted
then you need to rewrite your trigger. A trigger fires once per statement,
and thus "inserted" can include more than more than one row.
If you believe that you know that your table will ever be subject to
single-row inserts and updates, add this to your trigger:
IF @.@.rowcount > 1
BEGIN
RAISERROR('Multi-row operations not supported!, 16, 1)
ROLLBACK TRANSACTION
RETURN
END
If you need to support multi-row operations, you can run a cursor over
"inserted" (and maybe this is what you do already). However, since a
trigger runs in the context of a transaction defined by the statement
that defined it, it is not a good idea to start looping in a trigger, as
this could cause contention issues.
The remedy would be to perform the updates directly the in trigger, in a
set-based fashion rather than running one by one from your procedure. If
you do that your question about output may be become a moot point.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Does the procedure end with that PRINT statement?
ML
http://milambda.blogspot.com/|||HI Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97729620C5F6DYazorman@.127.0.0.1...
> Robert Bravery (me@.u.com) writes:
> A more interesting question is from where you got that @.claimid. If you
> do
> SELECT @.claimid = claimid FROM inserted
> then you need to rewrite your trigger. A trigger fires once per statement,
> and thus "inserted" can include more than more than one row.
> If you believe that you know that your table will ever be subject to
> single-row inserts and updates, add this to your trigger:
Yes I do get @.claimid from the inserted table. But it will alwaus be only
one record that is update. Just the nature of the operation.
Ok so then I need to qualify some things in my mind.
If a update trigger is ona table, and that table will always only have one
row updated at a time, the update trigger would fire, there is now an
inserted table, with the values on the update row in it. Now that trigger
then updates another table.
Do you no get a different inserted table, or is the same inserted table the
one created by the original insert, used.
Thanks for the single-row inserts little guidence you gave there
What I basically whant to do is that the update trigger on claims, updates
another table, which has say 4 rowse to update. Those four rows are related
to claim table. After that related table and its four rows are updates, I
want to perform and agregate check. All this will do is to notify the user
of an above or below threshold.
Thanks
Robert|||Robert Bravery (me@.u.com) writes:
> Yes I do get @.claimid from the inserted table. But it will alwaus be only
> one record that is update. Just the nature of the operation.
That's alright. But add an assertion to state that this is the case, to
prevent accidents.

> Ok so then I need to qualify some things in my mind.
> If a update trigger is ona table, and that table will always only have one
> row updated at a time, the update trigger would fire, there is now an
> inserted table, with the values on the update row in it. Now that trigger
> then updates another table.
> Do you no get a different inserted table, or is the same inserted table
> the one created by the original insert, used.
That's a different one. "inserted" and "deleted" are virtual tables, and
they are only visible within the trigger, nowhere else. So there is no
risk for confusion.

> What I basically whant to do is that the update trigger on claims,
> updates another table, which has say 4 rowse to update. Those four rows
> are related to claim table. After that related table and its four rows
> are updates, I want to perform and agregate check. All this will do is
> to notify the user of an above or below threshold.
I'm not sure that I understand exactly. I assumed that the PRINT was part
of some debug output. Are you now saying that the output is intended for
user consumption?
Generally, triggers are not supposed to generate any output (save for
error messages from integrity checks). It's perfectly legal to so, but
generally client code usually see INSERT, UPDATE, DELETE as operations
that do not delete data. Of course, if you code your application
accordingly, you can have output from the trigger. Still, I would consider
a different solution, if this is the case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||HI Erland,
> That's a different one. "inserted" and "deleted" are virtual tables, and
> they are only visible within the trigger, nowhere else. So there is no
> risk for confusion.
Ok thanks, that makes it easier to understand

> I'm not sure that I understand exactly. I assumed that the PRINT was part
> of some debug output. Are you now saying that the output is intended for
> user consumption?
I did put it there for degug purposes, because I could not get what I wanted
correctly

> Generally, triggers are not supposed to generate any output (save for
> error messages from integrity checks). It's perfectly legal to so, but
> generally client code usually see INSERT, UPDATE, DELETE as operations
> that do not delete data. Of course, if you code your application
> accordingly, you can have output from the trigger. Still, I would consider
> a different solution, if this is the case.
Basically the output would only be a type of informational message
ok in psudo code
the SP would update a table called liability, based on amounts entered into
the claim table.
After the SP has update the needed rows, I would like, to perhaps call
another SP, so
declare @.liab numeric(12,2), @.ded numeric(12,2)
select @.liab=sum(ammount) from claimliability where claimid = 10
select @.ded = ammount from ClaimDeductables where claimid = 10 and clmdedid=
70
if @.liab<@.ded
raiserror('Claim does not partake in SIP', 10,1)
Is this feasable, or am I going about it the wrong way
Thanks
Robert|||HI,
No the Print statement, debug for the time being, is in the trigger and is
after the exec of the SO
Thanks
RObert
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0E2F2CE4-C6DD-496C-8200-73F3BDDFC489@.microsoft.com...
> Does the procedure end with that PRINT statement?
>
> ML
> --
> http://milambda.blogspot.com/|||Since I can't see the entire code used in the process you're debugging, try
putting a PRINT statement at the end of each procedure - e.g. "print
'<procedure name> done."
Are there more triggers?
ML
http://milambda.blogspot.com/|||>The SP, by nature update multiple rows in child tables, as A result I would
>expect the following:
>(1 row(s) affected)
>(1 row(s) affected)
>(4 row(s) affected)
>But with the print statement I am gettingh
>(1 row(s) affected)
>(1 row(s) affected)
>Check SIP
>(4 row(s) affected)
>Should'nt the Check sip statement come last
I have the ouput of PRINT appear out of order relative to the retults
from SELECT (or UPDATE or INSERT) all the time. They apparently are
buffered independently. This is annoying, and I have never seen it
documented, but it is expected and nothing to worry about.
Roy|||HI Roy,
Thanks. THats reassuring
Robert
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:sirpv1duj8up6ssb9as694g5fpntjhq9li@.
4ax.com...
would
> I have the ouput of PRINT appear out of order relative to the retults
> from SELECT (or UPDATE or INSERT) all the time. They apparently are
> buffered independently. This is annoying, and I have never seen it
> documented, but it is expected and nothing to worry about.
> Roy