Does a Full Backup include transaction logs

[Brian D]

In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?

Thanks.
Brian

[Erland Sommarskog]

Brian D (bd*******@yaho o.com) writes:

In MS SQL 2005 when you do a Full Backup does it also backup and
truncate the transaction logs or do I need to back the transaction
logs up separately?

You need to backup the transaction log separately. The rationale is that
the last recent backup may have gone lost, or be broken. If the log
backups are OK (and you saved them), you can recover from an older
full backup + the translog backups.


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Brian D]

Erland,

We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?

Brian

[Russell Fields]

Brian,

If you want to save a little bit of backup space, run the 6pm log backup
just before the full backup. If you run it after, you will have backed up
the 12pm-6pm log activities twice.

Do NOT truncate the log ever, if you want to be able to restore the full
backup and then apply the logs to the full backup. If you were to backup
the database, then truncate the 6pm logs, the 12am, 6am, 12pm log backups
are all useless, since there is an unbridgeable transaction log gap between
the full backup and the first log.

RLF

"Brian D" <bd*******@yaho o.comwrote in message
news:11******** **************@ d55g2000hsg.goo glegroups.com.. .

Erland,

We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?

Brian

[Erland Sommarskog]

Brian D (bd*******@yaho o.com) writes:

We do a full backup daily at 6pm. If we do a trans log backup 4 times
a day at 12 am, 6am, 12pm and 6pm, should the 6pm trans log be
immediately before or after the full backup or can we just skip the
6pm one all together? If I skip the 6pm trans log backup should I
truncate the log immediately after the full backup?

First ask yourself: if the database goes belly-up, how much can we afford
to lose? In the case, the database file goes bad, there is a possibility
to take a final backup of the log. But if the log file disappears, this
means that you could lose up almost six hours of work. Can you afford
that?

No, that is it not a leading question. There are businesses where even
the loss of five minutes of data is a disaster. And there are businesses
where a full backup once a night without log backup is perfectly sufficient.
I just want you to evaluate where you fit in. Taking log backups as rarely
as you do, appears a bit unusual, so it might be that your business is
content with restoring the backup from last night. In which case, dealing
with the log is just extra overhead for you. For the rest of the post I will
nevertheless assume that this six-hour window is right for you.

I can't see that it matter whether you back up the log before or after
the full backup, but you should back up the log sometime there. In a
way log backups and full backups are independent of each other.

Russell mentioned that you should never truncate the transaction log.
I like to point out another thing. Where do you write the transaction log
dumps? Do you append them to the same device? Do you ever use WITH INIT?
Here is something to be careful with so that you don't lose part of a
log chain.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx