Resoring transaction logs

We believe we lost data yesterday in one of our tables, but only have a full
backup from 7/12 and have transaction logs from 7/14. Is there anything we
can do here? Don't you need the transaction logs from the last full backup
time in order to restore from the logs?
Help!Yes, you should be able to apply those transaction logs to that full backup
since you have a break in the sequence.(please someone correct me if I'm
wrong). You don't have logs backed up to tape anywhere?
If not, then you might want to look into Lumigent's Log Explorer to examine
the transaction logs. www.lumigent.com
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||hi Mark. First you need to backup the transaction log (should be available
regardless of the state of the failed db) before you do anything else. Then
you can restore the db from the full specifying no recovery to allow you to
continue restoring. Finally you can restore the transaction logs with a
recovery so that transactions will be rolled forward or back and the db will
then become available.
Please refer to books online for this procedure
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||Yes and No.
Yes - If 7/14 logs are the immediate logs backed up after 7/12 full backup.
You can apply logs all the way up to the most recent backup.
No - You backed up logs two days later? Logs should be backed up much more
frequently. That's what log backup is meant. If at least one log backup was
missing between 7/12 and 7/14, then you can not use the 7/14 logs at all.
Richard
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||..agreed log backup should be performed at LEAST daily, however there should
be no reason why you should be missing any logs since your last full backup,
assuming of course you are not using the simple recovery model -in which
case you can kiss your logs goodbye.
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:eRtXVqsUDHA.1484@.TK2MSFTNGP12.phx.gbl...
> Yes and No.
> Yes - If 7/14 logs are the immediate logs backed up after 7/12 full
backup.
> You can apply logs all the way up to the most recent backup.
> No - You backed up logs two days later? Logs should be backed up much more
> frequently. That's what log backup is meant. If at least one log backup
was
> missing between 7/12 and 7/14, then you can not use the 7/14 logs at all.
>
> Richard
> "Mark" <maxmann@.kc.rr.com> wrote in message
> news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > We believe we lost data yesterday in one of our tables, but only have a
> full
> > backup from 7/12 and have transaction logs from 7/14. Is there anything
> we
> > can do here? Don't you need the transaction logs from the last full
> backup
> > time in order to restore from the logs?
> >
> >
> > Help!
> >
> >
>|||Well, just to explain the situation a little further.
1. We are using Full Recover mode, which should be a good thing as far as
I'm concerned.
2. The reason we were missing the two days of logs is because we have two
jobs set in sql server.
One to do a daily full backup and 1 to do hourly trans log backups,
which is cool. The problem is
that on the 12th, somehow the job to do the full backups go disabled,
and we only have trans log backups
on tape back to the 14th. So that's how the gap happened. It's not
that we were doing trans log backups
every two days. It was just the issue w/ that job being disabled.
3. We have contacted lumigent and they said since we have the db in full
recovery mode that we should
be able to completely restore the database table that got deleted simply
by using the transaction log
that we have of when it happened.
Thanks for all the help.
Mark
"Mark" <maxmann@.kc.rr.com> wrote in message
news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> We believe we lost data yesterday in one of our tables, but only have a
full
> backup from 7/12 and have transaction logs from 7/14. Is there anything
we
> can do here? Don't you need the transaction logs from the last full
backup
> time in order to restore from the logs?
>
> Help!
>|||James, firstly just because Mark states "full
backup from 7/12 and have transaction logs from 7/14" doesnt mean to suggest
that there is a break in the sequence only that he had a one day gap before
had did transaction log backups.
Secondly it doesnt matter whether he has got subsequent logs to tape or not
since the transaction log is *still* available for backup before he starts
the recovery. The Log Explorer tool (very good) is probably unnecessary in
this scenario).
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"James Luetkehoelter" <james.luetkehoetler@.NOSPAMspyglassllc.com> wrote in
message news:eEF4jHtUDHA.964@.TK2MSFTNGP09.phx.gbl...
> Er, this should have read "Shouldn't be able to apply". If there's a
break
> in the logs, you shouldn't be able to do a point-in-time recovery.
> "James Luetkehoelter" <james.luetkehoetler@.NOSPAMspyglassllc.com> wrote in
> message news:%23OK$bcsUDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Yes, you should be able to apply those transaction logs to that full
> backup
> > since you have a break in the sequence.(please someone correct me if I'm
> > wrong). You don't have logs backed up to tape anywhere?
> >
> > If not, then you might want to look into Lumigent's Log Explorer to
> examine
> > the transaction logs. www.lumigent.com
> >
> >
> > "Mark" <maxmann@.kc.rr.com> wrote in message
> > news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > > We believe we lost data yesterday in one of our tables, but only have
a
> > full
> > > backup from 7/12 and have transaction logs from 7/14. Is there
anything
> > we
> > > can do here? Don't you need the transaction logs from the last full
> > backup
> > > time in order to restore from the logs?
> > >
> > >
> > > Help!
> > >
> > >
> >
> >
>|||Hi Mark, as you state your gap is literary that you "missed a backup". This
doesnt mean that you have missing backup data -just less backups. please
refer to and follow my previous posts, there is no reason why you should not
be able to recover point in time of your choosing.
***REMEMBER TO TAKE A FINAL TRANSACTION LOG BACKUP BEFORE ATTEMPTING YOUR
RECOVER***
You should read BOL, section Administering SQL Server/ Backing up and
Restoring Databases/ Using Recovery Models/ Full Recovery
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Mark" <maxmann@.kc.rr.com> wrote in message
news:ethVT$8UDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Well, just to explain the situation a little further.
> 1. We are using Full Recover mode, which should be a good thing as far as
> I'm concerned.
> 2. The reason we were missing the two days of logs is because we have two
> jobs set in sql server.
> One to do a daily full backup and 1 to do hourly trans log backups,
> which is cool. The problem is
> that on the 12th, somehow the job to do the full backups go disabled,
> and we only have trans log backups
> on tape back to the 14th. So that's how the gap happened. It's not
> that we were doing trans log backups
> every two days. It was just the issue w/ that job being disabled.
> 3. We have contacted lumigent and they said since we have the db in full
> recovery mode that we should
> be able to completely restore the database table that got deleted
simply
> by using the transaction log
> that we have of when it happened.
> Thanks for all the help.
> Mark
>
> "Mark" <maxmann@.kc.rr.com> wrote in message
> news:uzfg6DsUDHA.1152@.tk2msftngp13.phx.gbl...
> > We believe we lost data yesterday in one of our tables, but only have a
> full
> > backup from 7/12 and have transaction logs from 7/14. Is there anything
> we
> > can do here? Don't you need the transaction logs from the last full
> backup
> > time in order to restore from the logs?
> >
> >
> > Help!
> >
> >
>

reproducing deadlocks

I have done some research in how to avoid and view trace logs for deadlocks.
now iwant to test this on my test db.
Is there any steps by which deadlock can be reprduced in any test dbsay
authors or pubs?
thanksHi Vicky
You can simulate this using Query Analyser or Management Studio using two
sessions, by starting a transaction and not committing it in one session
while you try to update the same table in the second session you can get
either blocking or a deadlock. This is tested on SQL 2005:
Blocking example
Window 1
USE PUBS
SELECT @.@.SPID AS [Windows 1 SPID]
Window 2
USE PUBS
SELECT @.@.SPID AS [Windows 2 SPID]
Window 1
BEGIN TRANSACTION
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Returned immediately
Window 2
BEGIN TRANSACTION
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Not Returned Immediately
Window 1
EXEC sp_who2 -- Shows [Windows 1 SPID] blocking [Windows 2 SPID]
ROLLBACK TRANSACTION
Window 2
-- Results have now been returned
ROLLBACK TRANSACTION
Deadlock Example
Window 1
USE PUBS
SELECT @.@.SPID AS [Windows 1 SPID]
Window 2
USE PUBS
SELECT @.@.SPID AS [Windows 2 SPID]
Window 1
BEGIN TRANSACTION
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Returned immediately
Window 2
BEGIN TRANSACTION
SELECT * FROM TITLES WITH (UPDLOCK) -- Results Returned immediately
Window 1
SELECT * FROM TITLES WITH (UPDLOCK) -- Results Not Returned Immediately
Window 2
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Not Returned Immediately
In one windows (in my case window 2) you will get a message like:
Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 55) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
Window 1
ROLLBACK TRANSACTION
Window 2
ROLLBACK TRANSACTION
In the window that had the deadlock you will get the error message
Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
HTH
John
"vicky" wrote:

> I have done some research in how to avoid and view trace logs for deadlock
s.
> now iwant to test this on my test db.
> Is there any steps by which deadlock can be reprduced in any test dbsay
> authors or pubs?
> thanks
>
>

reproducing deadlocks

I have done some research in how to avoid and view trace logs for deadlocks.
now iwant to test this on my test db.
Is there any steps by which deadlock can be reprduced in any test dbsay
authors or pubs?
thanksHi Vicky
You can simulate this using Query Analyser or Management Studio using two
sessions, by starting a transaction and not committing it in one session
while you try to update the same table in the second session you can get
either blocking or a deadlock. This is tested on SQL 2005:
Blocking example
Window 1
USE PUBS
SELECT @.@.SPID AS [Windows 1 SPID]
Window 2
USE PUBS
SELECT @.@.SPID AS [Windows 2 SPID]
Window 1
BEGIN TRANSACTION
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Returned immediately
Window 2
BEGIN TRANSACTION
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Not Returned Immediately
Window 1
EXEC sp_who2 -- Shows [Windows 1 SPID] blocking [Windows 2 SPID]
ROLLBACK TRANSACTION
Window 2
-- Results have now been returned
ROLLBACK TRANSACTION
Deadlock Example
Window 1
USE PUBS
SELECT @.@.SPID AS [Windows 1 SPID]
Window 2
USE PUBS
SELECT @.@.SPID AS [Windows 2 SPID]
Window 1
BEGIN TRANSACTION
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Returned immediately
Window 2
BEGIN TRANSACTION
SELECT * FROM TITLES WITH (UPDLOCK) -- Results Returned immediately
Window 1
SELECT * FROM TITLES WITH (UPDLOCK) -- Results Not Returned Immediately
Window 2
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Not Returned Immediately
In one windows (in my case window 2) you will get a message like:
Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 55) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
Window 1
ROLLBACK TRANSACTION
Window 2
ROLLBACK TRANSACTION
In the window that had the deadlock you will get the error message
Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
HTH
John
"vicky" wrote:
> I have done some research in how to avoid and view trace logs for deadlocks.
> now iwant to test this on my test db.
> Is there any steps by which deadlock can be reprduced in any test dbsay
> authors or pubs?
> thanks
>
>

reproducing deadlock

I have done some research in how to avoid and view trace logs for deadlocks.
now iwant to test this on my test db.
Is there any steps by which deadlock can be reprduced in any test dbsay
authors or pubs?
thanksHere are two scripts:
<Script1>
-- Step 1
begin tran
update authors
set
au_fname = 'Joe'
where
au_lname = 'White'
-- Step 3
select
*
from
authors
where
au_lname = 'Green'
-- rollback tran
</Script1>
<Script2>
-- Step 2
begin tran
update authors
set
au_fname = 'Joe'
where
au_lname = 'Green'
select
*
from
authors
where
au_lname = 'White'
-- rollback tran
</Script2>
Note the embedded comments. Open 2 separate sessions and bring up Script1
in one session and Script2 in the other. In Script1, highlight and execute
Step 1. Next, run Script2 in its entirety (Step 2). It will not finish
executing, since it's waiting on Script1. Go back to Script1 and highlight
Step 3. Execute it. Within a few seconds, it will throw error 1205 - a
deadlock. It will also rollback the transaction.
Script2 will now complete. However, it still has an open transaction. Be
sure to rollback the transaction in Script2.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"vicky" <v@.v.com> wrote in message
news:%23LdGE6FjGHA.4748@.TK2MSFTNGP04.phx.gbl...
I have done some research in how to avoid and view trace logs for deadlocks.
now iwant to test this on my test db.
Is there any steps by which deadlock can be reprduced in any test dbsay
authors or pubs?
thanks