Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    46

    Exclamation Unanswered: Problem with my backup

    I'm having a problem with my backup strategy. My transaction log is setup as a job to backup every hour (it's in SQL Server Agent/Jobs not Database Maintenance Plans).

    Unfortunately, it doesn't look like the older transactions are falling out of the backup, so it's just getting bigger and bigger (now about 4Gb and too big for the available space on the harddrive).

    The SQL for the job is:
    BACKUP LOG [TPO_ProdData] TO [TPO_ProdData_Log] WITH NOINIT , NOUNLOAD , NAME = N'TPO_ProdData backup', NOSKIP , STATS = 10, NOFORMAT

    I'm doing a Full backup on a daily basis, so I only need to keep 1 day of the transaction logs in the backup.

    How can change the job to overwrite the data that is more than 1 day old?

    Thanks for the help,

    Marc

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on the BACKUP LOG documentation, my first guess would be the NOSKIP parameter. You ought to review all of your parameters, some of them don't make sense to me.

    -PatP

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040

    Post

    The NOINIT is the killer here. It appends the current tran log backup to the file.

    I see two options here.
    1. Build a maintenace plan and specify the number of days to retain as 1. Then drop your job in lieu of the maintenance plan.
    2. Create a second backup job that runs once a day with the NOINIT changed to INIT. That will purge the tran log. But make sure you have a full backup in place before you do that!

    Also, do what Pat said. NOSKIP and NOUNLOAD are for tape, not for disk backups. RTFM (Read the full manual !!!)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Silly me! I presumed that at least the log backup made with the full database dump would use INIT instead of NOINIT. The idea being to keep all of the log dumps for a given full backup in a single dump file...

    Good catch!

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Creating a maintenance plan is unnecessary if you know xp_sqlmaint/sqlmain.exe functionality. If this is the case just convert your current job to use either of them (xp_sqlmaint is the same as SQLMAINT.EXE but can be invoked from T-SQL job type)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •