Does a Full Backup include transaction logs

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
Full backups do not mark any log segments as inactive, Therefore no segments
will be truncated following a full backup. Only log backup mark segments as
inactive. Full backups do not interrupt the log backup chain.
This is so if you have a bad full backup, you can go to an earlier full
backup and restore logs through the time of the bad backup and up to
current.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian D" <bdaltilio@.yahoo.com> wrote in message
news:1184253274.265128.108590@.w3g2000hsg.googlegro ups.com...
> 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
>
|||Brian D (bdaltilio@.yahoo.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, 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
|||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
|||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" <bdaltilio@.yahoo.com> wrote in message
news:1184261915.544240.106090@.d55g2000hsg.googlegr oups.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
>
|||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
|||I agree completely with Erland. In my experience, I have never been
rewarded or punished because I did or did not have a backup. I was always
judged on whether I had a working recovery plan. Start from the recovery
side and build a complete plan, including a backup plan, that meets the
business needs. Finally, test your recoery plan to see that it actually
works and that it can be done in the agreed-upon time.
If you don't test, you have a recovery hope, not a recovery plan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996C38071EF5Yazorman@.127.0.0.1...
> Brian D (bdaltilio@.yahoo.com) writes:
> 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