Does a Full Backup include transaction logs

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Brian D
    #1

    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

    Tags: None
    • Erland Sommarskog
      #2
      Re: Does a Full Backup include transaction logs

      Brian D (bdaltilio@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, esquel@sommarsk og.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

        Comment

        • Brian D
          #3
          Re: Does a Full Backup include transaction logs

          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

            Comment

            • Russell Fields
              #4
              Re: Does a Full Backup include transaction logs

              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@yaho o.comwrote in message
              news:1184261915 .544240.106090@ 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
              >

                Comment

                • Erland Sommarskog
                  #5
                  Re: Does a Full Backup include transaction logs

                  Brian D (bdaltilio@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, esquel@sommarsk og.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

                    Comment

                    Previous template Next
                    Working...