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/sqlserverstor...ne/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 dat
a
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/sqlserverstor...ne/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 an
d
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 d
ata
> was there.
>
> "Paul S Randal [MS]" wrote:
>|||Paul,
I respectfully disagree here... The full backup does include some log record
s, but it doesn't remove
those log records from the ldf file (it doesn't truncate the log). Thanks to
this, the chain of log
backups is unaffected by intermediate database backups.
We can confirm this by looking at the LastLSN and FirstLSN eported by RESTOR
E HEADERONLY. Below is a
script that show this, and also confirms that you don't need to restore an i
ntermediate database
backup.
I know that I brought this up with someone in the dev team years ago, wanted
a confirmation that
this is a supported, not just some lucky fluke. I did get such a confirmatio
n. Knowing who you are,
I'm of course a bit worried now...
BOL does confirm in an example that you can skip an intermediate database ba
ckup:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9b12be51-5469-46f9-8e86-e938
e10aa3a1.htm
See "Alternative 2: Restore the database by using an earlier full database b
ackup".
However that same BOL topic has below statement at the top:
"The immediately previous full database backup or differential database back
up must be restored
first."
The two contradicts each other, and I believ (and hope) that the example is
the correct one and the
statement immediate above should be corrected.
--DROP DATABASE test
CREATE DATABASE test
GO
CREATE TABLE test..t(c1 int identity)
BACKUP DATABASE test TO DISK = 'C:\test.bak' WITH INIT
INSERT INTO test..t DEFAULT VALUES
BACKUP LOG test TO DISK = 'C:\test.bak'
INSERT INTO test..t DEFAULT VALUES
BACKUP LOG test TO DISK = 'C:\test.bak'
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\test.bak'
INSERT INTO test..t DEFAULT VALUES
BACKUP LOG test TO DISK = 'C:\test.bak'
INSERT INTO test..t DEFAULT VALUES
BACKUP LOG test TO DISK = 'C:\test.bak'
--Check out LastLSN and FirstLSN below
RESTORE HEADERONLY FROM DISK = 'C:\test.bak'
--Try the restore:
RESTORE DATABASE test FROM DISK = 'C:\test.bak' WITH FILE = 1, NORECOVERY, R
EPLACE
RESTORE LOG test FROM DISK = 'C:\test.bak' WITH FILE = 2, NORECOVERY
RESTORE LOG test FROM DISK = 'C:\test.bak' WITH FILE = 3, NORECOVERY
RESTORE LOG test FROM DISK = 'C:\test.bak' WITH FILE = 5, NORECOVERY
RESTORE LOG test FROM DISK = 'C:\test.bak' WITH FILE = 6, RECOVERY
--DROP DATABASE test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OLLX$P5HHHA.4896@.TK2MSFTNGP04.phx.gbl...
> Correct - the full backup on 12/2/06 8.00PM also backups up part of the tr
ansaction log that will
> exist nowhere else in the log backup chain. You will only be able to resto
re to the 12/2/06 6.00PM
> log backup.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>