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

how can I set my primary key to star again from 1' for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible'
thanks!Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibison

question about primary key

how can I set my primary key to star again from 1' for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible'
thanks!Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibisonsql

question about primary key

how can I set my primary key to star again from 1? for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible?
thanks!
Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibison

question about order in which queries fire in trigger

I ran into this trigger in a project. Is this tigger firing Delete from
webproducts join Deleted... first? and then Delete from webproducts join
Inserted... second? Or is the ...join Inserted firing first and then the
...deleted?
The table SubDetail gets updated on some column, any column. No deletes
involved. What would constitute deleted if it isn't Delete from webproducts
join Inserted?
----
CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
for update
delete WebProducts
from WebProducts wp
inner join Deleted d
on (d.RecordID = wp.RecordID )
delete WebProducts
from WebProducts wp
inner join Inserted i
on (i.RecordID = wp.RecordID)
return
---
Thanks,
RichOn Fri, 12 May 2006 15:17:01 -0700, Rich wrote:

>I ran into this trigger in a project. Is this tigger firing Delete from
>webproducts join Deleted... first? and then Delete from webproducts join
>Inserted... second? Or is the ...join Inserted firing first and then the
>...deleted?
>The table SubDetail gets updated on some column, any column. No deletes
>involved. What would constitute deleted if it isn't Delete from webproduct
s
>join Inserted?
(snip code)
Hi Rich,
I'm not sure if I understand all your questions.
However:
1. Statements in a trigger are executed sequentially, top-down (unless
you explicitly change order of execution with GO TO, WHILE, IF or other
control-flow statements).
2. In an UPDATE trigger, the deleted pseudo-table holds the before
update image of all affected rows and the inserted pseudo-table holds
the after update image of all affected rows.
Hugo Kornelis, SQL Server MVP|||Rich (Rich@.discussions.microsoft.com) writes:
> I ran into this trigger in a project. Is this tigger firing Delete from
> webproducts join Deleted... first? and then Delete from webproducts
> join Inserted... second?
Yes, statements are executed sequentially.

> The table SubDetail gets updated on some column, any column. No deletes
> involved. What would constitute deleted if it isn't Delete from >
> webproducts join Inserted?
In an UPDATE trigger, both the "inserted" and "deleted" tables are
popoulated. "inserted" holds the after-image data, and "deleted" is
before-image. That is how the row looked like before the update.

> CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
> for update
> delete WebProducts
> from WebProducts wp
> inner join Deleted d
> on (d.RecordID = wp.RecordID )
> delete WebProducts
> from WebProducts wp
> inner join Inserted i
> on (i.RecordID = wp.RecordID)
Seems funny to run two deletes like that...
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|||Thank you all for your replies. Actually, I left out other stuff from that
trigger which I did understand. However, the explanations provided here did
enlighten me about one thing that I was not aware of, the image of the table
before the action - "Deleted".
I actually do need to capture the before image, and I had no idea how to
describe that. Now I understand. Thank you all for explaining this to me.
Now I understand.
It doesn't solve my immediate problem, but now I think I can figure it out.
Incase anyone cares, my problem is that I am updating this table from a
client app using com ADO and want to retrieve the records affected count. I
f
...deleted... comes after ...inserted... in the trigger, I can retrieve th
e
counts of records affected. But if ...deleted... comes before ...inserted..
.
then
cmd.Execute j, , adExecuteNoRecords
is not returning the count of records affected. But at least now I can
tweak the trigger a little since I have a better idea what is going on.
Thanks again all,
Rich
"Rich" wrote:

> I ran into this trigger in a project. Is this tigger firing Delete from
> webproducts join Deleted... first? and then Delete from webproducts join
> Inserted... second? Or is the ...join Inserted firing first and then th
e
> ...deleted?
> The table SubDetail gets updated on some column, any column. No deletes
> involved. What would constitute deleted if it isn't Delete from webproduc
ts
> join Inserted?
> ----
--
> CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
> for update
> delete WebProducts
> from WebProducts wp
> inner join Deleted d
> on (d.RecordID = wp.RecordID )
> delete WebProducts
> from WebProducts wp
> inner join Inserted i
> on (i.RecordID = wp.RecordID)
> return
> ---
> Thanks,
> Rich|||Let me clarify a few things here.. Rich.. if you don't mind.
Take your case
delete WebProducts
from WebProducts wp
inner join Deleted d
on (d.RecordID = wp.RecordID )
delete WebProducts
from WebProducts wp
inner join Inserted i
on (i.RecordID = wp.RecordID)
Here first you are joining with deleted table on recordID and then the same
with inserted. And as Hugo and Erland have mentioned, Deleted has the old
image and Inserted has the new image. So this delete with join from deleted
is actually redundant.
There are two cases. One where the update is happening on the column
"RecordID".
In that case you will not be having any row in the table with the value of
"RecordID" that you have in deleted. So the first delete will not delete any
rows (Though I made an assumption here, that RecordID is the PKey :)
And if RecordID is not being updated, then the first delete will delete the
rows and the second delete will try to delete the same rows which will not
happen, so you will be getting rowcount = 0.
What I don't understand is your requirement. Do you want to delete the
updated rows from the table'|||On Fri, 12 May 2006 20:23:01 -0700, Omnibuzz wrote:
(snip)
> So this delete with join from deleted
>is actually redundant.
Hi Omnibuzz,
Good catch!

>What I don't understand is your requirement. Do you want to delete the
>updated rows from the table'
Same here. I hope that Rich will return and explain his requirements
some more.
Hugo Kornelis, SQL Server MVP|||examnotes <Rich@.discussions.microsoft.com> writes:
>Thank you all for your replies. Actually, I left out other stuff from that
>trigger which I did understand. However, the explanations provided here di
d
>enlighten me about one thing that I was not aware of, the image of the tabl
e
>before the action - "Deleted".
>I actually do need to capture the before image, and I had no idea how to
>describe that. Now I understand. Thank you all for explaining this to me.
>Now I understand.
>It doesn't solve my immediate problem, but now I think I can figure it out.
>Incase anyone cares, my problem is that I am updating this table from a
>client app using com ADO and want to retrieve the records affected count.
If
>...deleted... comes after ...inserted... in the trigger, I can retrieve th
e
>counts of records affected. But if ...deleted... comes before ...inserted.
.
>then
>cmd.Execute j, , adExecuteNoRecords
> is not returning the count of records affected. But at least now I can
>tweak the trigger a little since I have a better idea what is going on.
I think you need to skip adExecuteNoRecords and instead loop over
:NextRecordset. Each UPDATE and DELETE causes a rowcount. Obviously,
you want the first: the number of rows inserted. You must loop over
.NextRecordset to get all rowcounts, even if you don't need them.
--
Erland Sommarskog, Stockholm, esquel@.sommarskog.se

question abot primary key

how can I set my primary key to star again from 1?? for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.

is that possible??

thanks!I think you are talking about an IDENTITY column, which is not the same as a
Primary Key (although IDENTITY is used as a surrogate key).

DBCC CHECKIDENT ('TableName', RESEED,0)

--
David Portas
SQL Server MVP
--|||>> how can I set my primary key to start again from 1? <<

Please read a book on RDBMS; you have absolutely no idea what you are
doing.

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

A primary key **BY DEFINITION** is a subset of attributes (columns).
Keys do not "start" anywhere. Each entity in the data model has a
unique primary key by its nature; it is not assigned by the PHYSICAL
storage used to hold the PHYSICAL representation of the DBMS.

If you had a 1950's magnetic tape file, then your question would make
sense. They start at record #1 each time you hang a new magnetic tape
on the drive, which is obviously your mental model. This is the
difference between a VIN number and a parking space number.