Posted by Erland Sommarskog on 07/12/07 22:18

Brian D (bdaltilio@yahoo.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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]