does restore operation break tlog bckup chain?

Hi all!
I'm thinking of switching my production db to full recovery model.
Now, I've some questions regarding tlog backups chain. What can
possibly break chain of transaction log backups?
And yet another question. Does restore operation break transaction log
backups chain? I mean, should every restore operation be followed by
full db backup?
with regards
Maciej Szymanski> What can possibly break chain of transaction log backups?
In the FULL recovery model, truncating the log without creating a log backup
file (BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG) will break the log backup
sequence.
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
The script below illustrates that a backup isn't needed following a restore.
CREATE DATABASE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY FULL
GO
USE MyDatabase
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BACKUP DATABASE MyDatabase
TO DISK='C:\Backups\MyDatabase.bak'
WITH INIT
INSERT INTO MyTable VALUES(2)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log1.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH RECOVERY
GO
USE MyDatabase
INSERT INTO MyTable VALUES(3)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log2.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log2.bak'
WITH RECOVERY
GO
USE MyDatabase
SELECT * FROM MyTable -- 3 rows returned
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Maciej Szymanski" <maciek@.kolobrzeg.com.pl> wrote in message
news:85a5f2.0401170757.4de3ca20@.posting.google.com...
> Hi all!
> I'm thinking of switching my production db to full recovery model.
> Now, I've some questions regarding tlog backups chain. What can
> possibly break chain of transaction log backups?
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
> with regards
> Maciej Szymanski

does restore operation break tlog bckup chain?

Hi all!
I'm thinking of switching my production db to full recovery model.
Now, I've some questions regarding tlog backups chain. What can
possibly break chain of transaction log backups?
And yet another question. Does restore operation break transaction log
backups chain? I mean, should every restore operation be followed by
full db backup?
with regards
Maciej Szymanski> What can possibly break chain of transaction log backups?
In the FULL recovery model, truncating the log without creating a log backup
file (BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG) will break the log backup
sequence.

quote:

---

> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?

---

The script below illustrates that a backup isn't needed following a restore.
CREATE DATABASE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY FULL
GO
USE MyDatabase
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BACKUP DATABASE MyDatabase
TO DISK='C:\Backups\MyDatabase.bak'
WITH INIT
INSERT INTO MyTable VALUES(2)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log1.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH RECOVERY
GO
USE MyDatabase
INSERT INTO MyTable VALUES(3)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log2.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log2.bak'
WITH RECOVERY
GO
USE MyDatabase
SELECT * FROM MyTable -- 3 rows returned
Hope this helps.
Dan Guzman
SQL Server MVP
"Maciej Szymanski" <maciek@.kolobrzeg.com.pl> wrote in message
news:85a5f2.0401170757.4de3ca20@.posting.google.com...

quote:

---

> Hi all!
> I'm thinking of switching my production db to full recovery model.
> Now, I've some questions regarding tlog backups chain. What can
> possibly break chain of transaction log backups?
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
> with regards
> Maciej Szymanski

---

Does placing the transaction log on dedicated RAID volume make sense with Simple Recovery

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
DavidLarry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the log after each
transaction. It truncates after each time it performs a checkpoint (read about checkpoint in Books
Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com...
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would NOT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the next
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>sql

Does placing the transaction log on dedicated RAID volume make sense with Simple Recovery

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
David
Larry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the log after each
transaction. It truncates after each time it performs a checkpoint (read about checkpoint in Books
Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com...
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would NOT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the next
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>