2K Tranaction Log Recovery with Corrupt Backup

I am looking to clarify my understanding of how the SQL Server 2000 recovery
processes works in full recovery mode.
Given the following backup strategy
12/1/2006 8:00 PM FULL BACKUP
12/2/2006 12:00 AM TRANSACTION LOG BACKUP
12/2/2006 06:00 AM TRANSACTION LOG BACKUP
12/2/2006 12:00 PM TRANSACTION LOG BACKUP
12/2/2006 06:00 PM TRANSACTION LOG BACKUP
12/2/2006 8:00 PM FULL BACKUP {Corrupt Backup}
12/3/2006 12:00 AM TRANSACTION LOG BACKUP
12/3/2006 06:00 AM TRANSACTION LOG BACKUP
If I wanted to restore the database to 12/3/2006 06:00:AM would I be able to?
I'm questioning if it would be possible because the FULL backup would break
the Transaction Log chain. Doesn't the transaction log get truncated during
a full backup?
Correct - the full backup on 12/2/06 8.00PM also backups up part of the
transaction log that will exist nowhere else in the log backup chain. You
will only be able to restore to the 12/2/06 6.00PM log backup.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"C RAMSEY" <C RAMSEY@.discussions.microsoft.com> wrote in message
news:D730CB8B-925E-488E-A843-3075EF6E93C6@.microsoft.com...
>I am looking to clarify my understanding of how the SQL Server 2000
>recovery
> processes works in full recovery mode.
> Given the following backup strategy
> 12/1/2006 8:00 PM FULL BACKUP
> 12/2/2006 12:00 AM TRANSACTION LOG BACKUP
> 12/2/2006 06:00 AM TRANSACTION LOG BACKUP
> 12/2/2006 12:00 PM TRANSACTION LOG BACKUP
> 12/2/2006 06:00 PM TRANSACTION LOG BACKUP
> 12/2/2006 8:00 PM FULL BACKUP {Corrupt Backup}
> 12/3/2006 12:00 AM TRANSACTION LOG BACKUP
> 12/3/2006 06:00 AM TRANSACTION LOG BACKUP
> If I wanted to restore the database to 12/3/2006 06:00:AM would I be able
> to?
> I'm questioning if it would be possible because the FULL backup would
> break
> the Transaction Log chain. Doesn't the transaction log get truncated
> during
> a full backup?
>
|||After I posted the message I did a test of the process.
I was able to restore data that was changed after 12/3/2006 6:00 AM.
So I'm a little confused. Because it acted as if there was no break in the
transaction log chain.
My test was to restore the backup as a new database on the same instance. I
inserted a record and created a log backup at 10:00AM. Then I copied the
good backup from last night to another folder. Next I open the last night
backup and deleted data from it to make it an invalid backup. Then I
restored the previous day backup and all transaction logs up to the 10:00 AM
transaction log backup. I queried the table in the new database and the data
was there.
"Paul S Randal [MS]" wrote:

> Correct - the full backup on 12/2/06 8.00PM also backups up part of the
> transaction log that will exist nowhere else in the log backup chain. You
> will only be able to restore to the 12/2/06 6.00PM log backup.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "C RAMSEY" <C RAMSEY@.discussions.microsoft.com> wrote in message
> news:D730CB8B-925E-488E-A843-3075EF6E93C6@.microsoft.com...
>
>
|||I completed an additional test were I insterted some records into a table and
backed up the database after each record as inserted.
Backup sequence was Full, Tran, Tran, Full, Tran, Tran
I then restored
Full, Tran, Tran, Tran, Tran
And all the data was recovered.
--Carlton
"C RAMSEY" wrote:
[vbcol=seagreen]
> After I posted the message I did a test of the process.
> I was able to restore data that was changed after 12/3/2006 6:00 AM.
> So I'm a little confused. Because it acted as if there was no break in the
> transaction log chain.
> My test was to restore the backup as a new database on the same instance. I
> inserted a record and created a log backup at 10:00AM. Then I copied the
> good backup from last night to another folder. Next I open the last night
> backup and deleted data from it to make it an invalid backup. Then I
> restored the previous day backup and all transaction logs up to the 10:00 AM
> transaction log backup. I queried the table in the new database and the data
> was there.
>
> "Paul S Randal [MS]" wrote: