Transaction Logging Backup Set

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

    Transaction Logging Backup Set

    Hi

    I take one nightly full database backup at 02:00 and backup the
    transaction log to one backup set every 15mins.

    The commands to do this are as follows and are set up to run as
    database jobs:

    -- Database Backup
    BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
    NOUNLOAD , NAME = N'ThikosDatabas e backup', NOSKIP , STATS = 10,
    NOFORMAT

    -- Transaction Log Backup Every 15mins.

    BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
    = N'TransactionLo gBackUp', NOSKIP , STATS = 10, DESCRIPTION =
    N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
    RETAINDAYS = 0

    At the moment the transaction log backups every 15mins are simply
    being added one by one to the one backup set. I want the backed up
    transaction logs in the backup set after 24hrs to be overwritten so
    disk space does not add up. I thought RETAINDAYS would do this as an
    expirydate is set to expire on the time it is created so i would have
    expexted it to be overwritten when that time comes around the next
    day. (But is doesent)

    I think it is ok to overwrite the transaction log in the backupset
    after 24hrs as you only need the transaction logs since the last full
    backup (which happens nightly at 02:00) for recovery.

    Does anyone have any ideas?

    Many thanks.

    Thiko!
    Tags: None
    • Igor Raytsin
      #2
      Re: Transaction Logging Backup Set

      -- Database Backup
      BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
      NOUNLOAD , NAME = N'ThikosDatabas e backup', NOSKIP , STATS = 10,
      NOFORMAT

      -- After that (once a day)
      BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH INIT ...

      -- Transaction Log Backup Every 15mins.
      BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
      = N'TransactionLo gBackUp', NOSKIP , STATS = 10, DESCRIPTION =
      N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT



      "Thiko" <biwombi@hotmai l.com> wrote in message
      news:b8d3b40c.0 312080359.3e373 f33@posting.goo gle.com...[color=blue]
      > Hi
      >
      > I take one nightly full database backup at 02:00 and backup the
      > transaction log to one backup set every 15mins.
      >
      > The commands to do this are as follows and are set up to run as
      > database jobs:
      >
      > -- Database Backup
      > BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
      > NOUNLOAD , NAME = N'ThikosDatabas e backup', NOSKIP , STATS = 10,
      > NOFORMAT
      >
      > -- Transaction Log Backup Every 15mins.
      >
      > BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
      > = N'TransactionLo gBackUp', NOSKIP , STATS = 10, DESCRIPTION =
      > N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
      > RETAINDAYS = 0
      >
      > At the moment the transaction log backups every 15mins are simply
      > being added one by one to the one backup set. I want the backed up
      > transaction logs in the backup set after 24hrs to be overwritten so
      > disk space does not add up. I thought RETAINDAYS would do this as an
      > expirydate is set to expire on the time it is created so i would have
      > expexted it to be overwritten when that time comes around the next
      > day. (But is doesent)
      >
      > I think it is ok to overwrite the transaction log in the backupset
      > after 24hrs as you only need the transaction logs since the last full
      > backup (which happens nightly at 02:00) for recovery.
      >
      > Does anyone have any ideas?
      >
      > Many thanks.
      >
      > Thiko![/color]


        Comment

        • Thiko
          #3
          Re: Transaction Logging Backup Set

          Hi!

          This is very helpful. Thank you very much.

          Could you tell me please why to truncate the transaction log
          immediately after the full database backup command? What happens if I
          need to recovery some of the transactions that were taking place
          during the full database backup, for example? Would it be better to
          do the truncate transaction log with the INIT immediately before the
          full database backup takes place?

          Many Thanks.

          Thiko!

          "Igor Raytsin" <n&i@cyberus.ca > wrote in message news:<3fd52eac_ 2@news.cybersur f.net>...[color=blue]
          > -- Database Backup
          > BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
          > NOUNLOAD , NAME = N'ThikosDatabas e backup', NOSKIP , STATS = 10,
          > NOFORMAT
          >
          > -- After that (once a day)
          > BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH INIT ...
          >
          > -- Transaction Log Backup Every 15mins.
          > BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
          > = N'TransactionLo gBackUp', NOSKIP , STATS = 10, DESCRIPTION =
          > N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT
          >
          >
          >
          > "Thiko" <biwombi@hotmai l.com> wrote in message
          > news:b8d3b40c.0 312080359.3e373 f33@posting.goo gle.com...[color=green]
          > > Hi
          > >
          > > I take one nightly full database backup at 02:00 and backup the
          > > transaction log to one backup set every 15mins.
          > >
          > > The commands to do this are as follows and are set up to run as
          > > database jobs:
          > >
          > > -- Database Backup
          > > BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
          > > NOUNLOAD , NAME = N'ThikosDatabas e backup', NOSKIP , STATS = 10,
          > > NOFORMAT
          > >
          > > -- Transaction Log Backup Every 15mins.
          > >
          > > BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
          > > = N'TransactionLo gBackUp', NOSKIP , STATS = 10, DESCRIPTION =
          > > N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
          > > RETAINDAYS = 0
          > >
          > > At the moment the transaction log backups every 15mins are simply
          > > being added one by one to the one backup set. I want the backed up
          > > transaction logs in the backup set after 24hrs to be overwritten so
          > > disk space does not add up. I thought RETAINDAYS would do this as an
          > > expirydate is set to expire on the time it is created so i would have
          > > expexted it to be overwritten when that time comes around the next
          > > day. (But is doesent)
          > >
          > > I think it is ok to overwrite the transaction log in the backupset
          > > after 24hrs as you only need the transaction logs since the last full
          > > backup (which happens nightly at 02:00) for recovery.
          > >
          > > Does anyone have any ideas?
          > >
          > > Many thanks.
          > >
          > > Thiko![/color][/color]

            Comment

            • Igor Raytsin
              #4
              Re: Transaction Logging Backup Set

              Hi,

              There is some misunderstandin g here:

              1. You do not need to truncate transaction log manually. Full Backup or
              Transaction Log Backup will do it for you.
              2. INIT option does not truncate log. INIT specifies that all backup sets
              should be overwritten.


              Regards,
              Igor


              "Thiko" <biwombi@hotmai l.com> wrote in message
              news:b8d3b40c.0 312090343.1f1b7 6dd@posting.goo gle.com...[color=blue]
              > Hi!
              >
              > This is very helpful. Thank you very much.
              >
              > Could you tell me please why to truncate the transaction log
              > immediately after the full database backup command? What happens if I
              > need to recovery some of the transactions that were taking place
              > during the full database backup, for example? Would it be better to
              > do the truncate transaction log with the INIT immediately before the
              > full database backup takes place?
              >
              > Many Thanks.
              >
              > Thiko!
              >
              > "Igor Raytsin" <n&i@cyberus.ca > wrote in message[/color]
              news:<3fd52eac_ 2@news.cybersur f.net>...[color=blue][color=green]
              > > -- Database Backup
              > > BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
              > > NOUNLOAD , NAME = N'ThikosDatabas e backup', NOSKIP , STATS = 10,
              > > NOFORMAT
              > >
              > > -- After that (once a day)
              > > BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH INIT ...
              > >
              > > -- Transaction Log Backup Every 15mins.
              > > BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
              > > = N'TransactionLo gBackUp', NOSKIP , STATS = 10, DESCRIPTION =
              > > N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT
              > >
              > >
              > >
              > > "Thiko" <biwombi@hotmai l.com> wrote in message
              > > news:b8d3b40c.0 312080359.3e373 f33@posting.goo gle.com...[color=darkred]
              > > > Hi
              > > >
              > > > I take one nightly full database backup at 02:00 and backup the
              > > > transaction log to one backup set every 15mins.
              > > >
              > > > The commands to do this are as follows and are set up to run as
              > > > database jobs:
              > > >
              > > > -- Database Backup
              > > > BACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,
              > > > NOUNLOAD , NAME = N'ThikosDatabas e backup', NOSKIP , STATS = 10,
              > > > NOFORMAT
              > > >
              > > > -- Transaction Log Backup Every 15mins.
              > > >
              > > > BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
              > > > = N'TransactionLo gBackUp', NOSKIP , STATS = 10, DESCRIPTION =
              > > > N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,
              > > > RETAINDAYS = 0
              > > >
              > > > At the moment the transaction log backups every 15mins are simply
              > > > being added one by one to the one backup set. I want the backed up
              > > > transaction logs in the backup set after 24hrs to be overwritten so
              > > > disk space does not add up. I thought RETAINDAYS would do this as an
              > > > expirydate is set to expire on the time it is created so i would have
              > > > expexted it to be overwritten when that time comes around the next
              > > > day. (But is doesent)
              > > >
              > > > I think it is ok to overwrite the transaction log in the backupset
              > > > after 24hrs as you only need the transaction logs since the last full
              > > > backup (which happens nightly at 02:00) for recovery.
              > > >
              > > > Does anyone have any ideas?
              > > >
              > > > Many thanks.
              > > >
              > > > Thiko![/color][/color][/color]


                Comment

                Previous template Next
                Working...