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 database backup truncate tlog ?

I am running sql server 2000 sp3a on win2k. does complete database backup do
tlog backup and truncate it also ?Jay
No,it does not
This is a great thing in SQL Server that you can have a full backup that
was done let me say at 1 May and at 1 June and doing t-log file backup. In
case of disaster you restore a full backup (at 1 May ) and apply all t-log
file backups till a point of distaster .
If sql server was trunucated a log file during the backup you was not be
able to restore all log files due to breaking chaines of transactions
"Jay" <Jay@.discussions.microsoft.com> wrote in message
news:A4A8062F-6DA1-4AA8-B33B-63F91E3E9737@.microsoft.com...
>I am running sql server 2000 sp3a on win2k. does complete database backup
>do
> tlog backup and truncate it also ?
>|||does complete database backup do
> tlog backup and truncate it also ?
No when your db is in bulk_logged or full recovery model. In order to
truncate the log, you should backup the transaction log, using "backup log".
The db should be set to use bulk_logged or full recovery model in order to
backup the transaction log.
If your database's recovery model is simple, you can not backup the
transaction log. SQL Server will truncate the transaction log after a
checkpoint.
AMB
"Jay" wrote:
> I am running sql server 2000 sp3a on win2k. does complete database backup do
> tlog backup and truncate it also ?
>|||Correction,
> No when your db is in bulk_logged or full recovery model.
No, a full backup of the db does not truncate the log, no matter what
recovery model is it using.
AMB
"Alejandro Mesa" wrote:
> does complete database backup do
> > tlog backup and truncate it also ?
> No when your db is in bulk_logged or full recovery model. In order to
> truncate the log, you should backup the transaction log, using "backup log".
> The db should be set to use bulk_logged or full recovery model in order to
> backup the transaction log.
> If your database's recovery model is simple, you can not backup the
> transaction log. SQL Server will truncate the transaction log after a
> checkpoint.
>
> AMB
> "Jay" wrote:
> > I am running sql server 2000 sp3a on win2k. does complete database backup do
> > tlog backup and truncate it also ?
> >

does database backup truncate tlog ?

I am running sql server 2000 sp3a on win2k. does complete database backup do
tlog backup and truncate it also ?
Jay
No,it does not
This is a great thing in SQL Server that you can have a full backup that
was done let me say at 1 May and at 1 June and doing t-log file backup. In
case of disaster you restore a full backup (at 1 May ) and apply all t-log
file backups till a point of distaster .
If sql server was trunucated a log file during the backup you was not be
able to restore all log files due to breaking chaines of transactions
"Jay" <Jay@.discussions.microsoft.com> wrote in message
news:A4A8062F-6DA1-4AA8-B33B-63F91E3E9737@.microsoft.com...
>I am running sql server 2000 sp3a on win2k. does complete database backup
>do
> tlog backup and truncate it also ?
>
|||does complete database backup do
> tlog backup and truncate it also ?
No when your db is in bulk_logged or full recovery model. In order to
truncate the log, you should backup the transaction log, using "backup log".
The db should be set to use bulk_logged or full recovery model in order to
backup the transaction log.
If your database's recovery model is simple, you can not backup the
transaction log. SQL Server will truncate the transaction log after a
checkpoint.
AMB
"Jay" wrote:

> I am running sql server 2000 sp3a on win2k. does complete database backup do
> tlog backup and truncate it also ?
>
|||Correction,

> No when your db is in bulk_logged or full recovery model.
No, a full backup of the db does not truncate the log, no matter what
recovery model is it using.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> does complete database backup do
> No when your db is in bulk_logged or full recovery model. In order to
> truncate the log, you should backup the transaction log, using "backup log".
> The db should be set to use bulk_logged or full recovery model in order to
> backup the transaction log.
> If your database's recovery model is simple, you can not backup the
> transaction log. SQL Server will truncate the transaction log after a
> checkpoint.
>
> AMB
> "Jay" wrote:

does database backup truncate tlog ?

I am running sql server 2000 sp3a on win2k. does complete database backup do
tlog backup and truncate it also ?Jay
No,it does not
This is a great thing in SQL Server that you can have a full backup that
was done let me say at 1 May and at 1 June and doing t-log file backup. In
case of disaster you restore a full backup (at 1 May ) and apply all t-log
file backups till a point of distaster .
If sql server was trunucated a log file during the backup you was not be
able to restore all log files due to breaking chaines of transactions
"Jay" <Jay@.discussions.microsoft.com> wrote in message
news:A4A8062F-6DA1-4AA8-B33B-63F91E3E9737@.microsoft.com...
>I am running sql server 2000 sp3a on win2k. does complete database backup
>do
> tlog backup and truncate it also ?
>|||does complete database backup do
> tlog backup and truncate it also ?
No when your db is in bulk_logged or full recovery model. In order to
truncate the log, you should backup the transaction log, using "backup log".
The db should be set to use bulk_logged or full recovery model in order to
backup the transaction log.
If your database's recovery model is simple, you can not backup the
transaction log. SQL Server will truncate the transaction log after a
checkpoint.
AMB
"Jay" wrote:

> I am running sql server 2000 sp3a on win2k. does complete database backup
do
> tlog backup and truncate it also ?
>|||Correction,

> No when your db is in bulk_logged or full recovery model.
No, a full backup of the db does not truncate the log, no matter what
recovery model is it using.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> does complete database backup do
> No when your db is in bulk_logged or full recovery model. In order to
> truncate the log, you should backup the transaction log, using "backup log
".
> The db should be set to use bulk_logged or full recovery model in order to
> backup the transaction log.
> If your database's recovery model is simple, you can not backup the
> transaction log. SQL Server will truncate the transaction log after a
> checkpoint.
>
> AMB
> "Jay" wrote:
>