Is transaction log file reuseable after data file is Restored?

We have a database that is set to Simple recovery mode and is backed up
every night. The data files and sql server and OS files are on the same
disk and the log file is on a different disk. (There are no log file
backups.)
During the day the disk controller on the disk with the data files has
failed and has subsequently been replaced and is being restored from tape.
My question is: can the log file on the other (healty) disk be reliably used
to "replay" it's committed transactions into the restored database?
thanks,
Paul Ritchie.
No. When you restore the FULL Database backup, it will write over the
existing log file. Whatever transactions that were in the transaction log
when the backup was taken and committed will roll forward, all those that
had not committed will roll back. This is called recovery. You will end up
with a database in a state it was in when the backup was taken.
Sincerely,
Anthony Thomas

"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
We have a database that is set to Simple recovery mode and is backed up
every night. The data files and sql server and OS files are on the same
disk and the log file is on a different disk. (There are no log file
backups.)
During the day the disk controller on the disk with the data files has
failed and has subsequently been replaced and is being restored from tape.
My question is: can the log file on the other (healty) disk be reliably used
to "replay" it's committed transactions into the restored database?
thanks,
Paul Ritchie.
|||Thanks Anthony,
So we're saying that committed transactions in the log file of a database
set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
Explorer I guess!)
ie these are transactions that have occurred subsequent to the backup being
taken. The MDF file is lost and restored from backup and this perfectly
good record of the subsequent transactions is not able to be used?
Why keep them then?
cheers,
Paul.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
> No. When you restore the FULL Database backup, it will write over the
> existing log file. Whatever transactions that were in the transaction log
> when the backup was taken and committed will roll forward, all those that
> had not committed will roll back. This is called recovery. You will end
up
> with a database in a state it was in when the backup was taken.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
> news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
> We have a database that is set to Simple recovery mode and is backed up
> every night. The data files and sql server and OS files are on the same
> disk and the log file is on a different disk. (There are no log file
> backups.)
> During the day the disk controller on the disk with the data files has
> failed and has subsequently been replaced and is being restored from tape.
> My question is: can the log file on the other (healty) disk be reliably
used
> to "replay" it's committed transactions into the restored database?
> thanks,
> Paul Ritchie.
>
|||"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uYhlBvu4EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Thanks Anthony,
> So we're saying that committed transactions in the log file of a database
> set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
> Explorer I guess!)
>
Not even them. Committed transactions are deleted from the log file upon a
checkpoint in SIMPLE RECOVERY mode.
So, within minutes (or less) the log file no logner has that transaction
anymore.

> ie these are transactions that have occurred subsequent to the backup
being
> taken. The MDF file is lost and restored from backup and this perfectly
> good record of the subsequent transactions is not able to be used?
> Why keep them then?
In Simple mode they aren't kept.
In FULL they are and can be used.
[vbcol=seagreen]
> cheers,
> Paul.
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
log[vbcol=seagreen]
that[vbcol=seagreen]
end[vbcol=seagreen]
> up
tape.
> used
>
|||It is not for the COMMITTED transactions that the log files are used for.
Regardless of RECOVERY mode, it is the in process, non-committed, open
transactions that the log files are used for, without them, then there would
be no ROLLBACK during the reocvery process. This is the part of the ACID
properties that keeps the database consistent: if I pull money from one
account, it BETTER show up in another, viz., completely committed.
Sincerely,
Anthony Thomas

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:PT7wd.1397$DQ3.1285@.twister.nyroc.rr.com...
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uYhlBvu4EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Thanks Anthony,
> So we're saying that committed transactions in the log file of a database
> set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
> Explorer I guess!)
>
Not even them. Committed transactions are deleted from the log file upon a
checkpoint in SIMPLE RECOVERY mode.
So, within minutes (or less) the log file no logner has that transaction
anymore.

> ie these are transactions that have occurred subsequent to the backup
being
> taken. The MDF file is lost and restored from backup and this perfectly
> good record of the subsequent transactions is not able to be used?
> Why keep them then?
In Simple mode they aren't kept.
In FULL they are and can be used.
[vbcol=seagreen]
> cheers,
> Paul.
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
log[vbcol=seagreen]
that[vbcol=seagreen]
end[vbcol=seagreen]
> up
tape.
> used
>

Is transaction log file reuseable after data file is Restored?

We have a database that is set to Simple recovery mode and is backed up
every night. The data files and sql server and OS files are on the same
disk and the log file is on a different disk. (There are no log file
backups.)
During the day the disk controller on the disk with the data files has
failed and has subsequently been replaced and is being restored from tape.
My question is: can the log file on the other (healty) disk be reliably used
to "replay" it's committed transactions into the restored database?
thanks,
Paul Ritchie.No. When you restore the FULL Database backup, it will write over the
existing log file. Whatever transactions that were in the transaction log
when the backup was taken and committed will roll forward, all those that
had not committed will roll back. This is called recovery. You will end up
with a database in a state it was in when the backup was taken.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
We have a database that is set to Simple recovery mode and is backed up
every night. The data files and sql server and OS files are on the same
disk and the log file is on a different disk. (There are no log file
backups.)
During the day the disk controller on the disk with the data files has
failed and has subsequently been replaced and is being restored from tape.
My question is: can the log file on the other (healty) disk be reliably used
to "replay" it's committed transactions into the restored database?
thanks,
Paul Ritchie.|||Thanks Anthony,
So we're saying that committed transactions in the log file of a database
set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
Explorer I guess!)
ie these are transactions that have occurred subsequent to the backup being
taken. The MDF file is lost and restored from backup and this perfectly
good record of the subsequent transactions is not able to be used?
Why keep them then?
cheers,
Paul.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
> No. When you restore the FULL Database backup, it will write over the
> existing log file. Whatever transactions that were in the transaction log
> when the backup was taken and committed will roll forward, all those that
> had not committed will roll back. This is called recovery. You will end
up
> with a database in a state it was in when the backup was taken.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
> news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
> We have a database that is set to Simple recovery mode and is backed up
> every night. The data files and sql server and OS files are on the same
> disk and the log file is on a different disk. (There are no log file
> backups.)
> During the day the disk controller on the disk with the data files has
> failed and has subsequently been replaced and is being restored from tape.
> My question is: can the log file on the other (healty) disk be reliably
used
> to "replay" it's committed transactions into the restored database?
> thanks,
> Paul Ritchie.
>|||"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uYhlBvu4EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Thanks Anthony,
> So we're saying that committed transactions in the log file of a database
> set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
> Explorer I guess!)
>
Not even them. Committed transactions are deleted from the log file upon a
checkpoint in SIMPLE RECOVERY mode.
So, within minutes (or less) the log file no logner has that transaction
anymore.
> ie these are transactions that have occurred subsequent to the backup
being
> taken. The MDF file is lost and restored from backup and this perfectly
> good record of the subsequent transactions is not able to be used?
> Why keep them then?
In Simple mode they aren't kept.
In FULL they are and can be used.
> cheers,
> Paul.
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
> > No. When you restore the FULL Database backup, it will write over the
> > existing log file. Whatever transactions that were in the transaction
log
> > when the backup was taken and committed will roll forward, all those
that
> > had not committed will roll back. This is called recovery. You will
end
> up
> > with a database in a state it was in when the backup was taken.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
> > news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > We have a database that is set to Simple recovery mode and is backed up
> > every night. The data files and sql server and OS files are on the same
> > disk and the log file is on a different disk. (There are no log file
> > backups.)
> >
> > During the day the disk controller on the disk with the data files has
> > failed and has subsequently been replaced and is being restored from
tape.
> >
> > My question is: can the log file on the other (healty) disk be reliably
> used
> > to "replay" it's committed transactions into the restored database?
> >
> > thanks,
> > Paul Ritchie.
> >
> >
>|||It is not for the COMMITTED transactions that the log files are used for.
Regardless of RECOVERY mode, it is the in process, non-committed, open
transactions that the log files are used for, without them, then there would
be no ROLLBACK during the reocvery process. This is the part of the ACID
properties that keeps the database consistent: if I pull money from one
account, it BETTER show up in another, viz., completely committed.
Sincerely,
Anthony Thomas
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:PT7wd.1397$DQ3.1285@.twister.nyroc.rr.com...
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uYhlBvu4EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Thanks Anthony,
> So we're saying that committed transactions in the log file of a database
> set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
> Explorer I guess!)
>
Not even them. Committed transactions are deleted from the log file upon a
checkpoint in SIMPLE RECOVERY mode.
So, within minutes (or less) the log file no logner has that transaction
anymore.
> ie these are transactions that have occurred subsequent to the backup
being
> taken. The MDF file is lost and restored from backup and this perfectly
> good record of the subsequent transactions is not able to be used?
> Why keep them then?
In Simple mode they aren't kept.
In FULL they are and can be used.
> cheers,
> Paul.
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
> > No. When you restore the FULL Database backup, it will write over the
> > existing log file. Whatever transactions that were in the transaction
log
> > when the backup was taken and committed will roll forward, all those
that
> > had not committed will roll back. This is called recovery. You will
end
> up
> > with a database in a state it was in when the backup was taken.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
> > news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > We have a database that is set to Simple recovery mode and is backed up
> > every night. The data files and sql server and OS files are on the same
> > disk and the log file is on a different disk. (There are no log file
> > backups.)
> >
> > During the day the disk controller on the disk with the data files has
> > failed and has subsequently been replaced and is being restored from
tape.
> >
> > My question is: can the log file on the other (healty) disk be reliably
> used
> > to "replay" it's committed transactions into the restored database?
> >
> > thanks,
> > Paul Ritchie.
> >
> >
>sql

Is this SQL related problem?

Any idea the cause of this error. I got it while trying to execute a stored proc (in sql 2005). I had just restored database and below error is stucking me to move ahead. Any help please

Error invoking method 'ExecuteQuery' for transaction (Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding)

Timeout means that the server does not replies or completes the job within 30 sec (default)
Are you sure the the database is online ?
Can you execute a simple query such as select @.@.spid without having error ?
Can you post the query ?