Overwrite transaction backup files every week problem

  • Hi

    I'm currently backing up our transaction log every hour to a file names like __.bak and it works like a charm where the backup file is overwritten once a week with a new one, but it leaves me with up to 24 backup files per database per day which will increase the risc of human error when restoring.

    But now I want to change it so that I only have 1 file per database per day but I can't figure out how to get my script to overwrite on the first write of the day and after that append to the file. Any of you more skilled SQL people got any ideas? I know there is 3rd party tools to accomplish this, but I want plain vanilla SQL.

    Anders

  • Transaction log bakup will have extn of .trn .

    If you want to take in the same file backups

    backup database to disk='path'

    Dont mention with init so that it will append the existing file

  • I would recommend not using a single file device and continue using multiple files. The problem you are going to run into is very simple. Part of your plan should be to offload the backup file(s) to tape.

    Now what is going to happen when you init the device? You have just wiped out all of your transaction log backups that you will need to restore your system to a point in time prior to this backup being run. Oops...

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams (6/17/2009)


    I would recommend not using a single file device and continue using multiple files. The problem you are going to run into is very simple. Part of your plan should be to offload the backup file(s) to tape.

    Now what is going to happen when you init the device? You have just wiped out all of your transaction log backups that you will need to restore your system to a point in time prior to this backup being run. Oops...

    We do bckup our file based backups like transactions log backup to a secondary backup system every day.

  • anders (6/25/2009)


    We do bckup our file based backups like transactions log backup to a secondary backup system every day.

    That is good - but have you verified when that job starts and when it completes? Does it contain everything you need in that file to restore your system back to a point in time prior to running the init?

    Just for arguments sake - let's say that backup is started at 6am and completes at 8am. At 7am, your process starts and initializes the file. Are you sure your file has been backed up? Or have you just lost all the transaction log backups for the previous day?

    What happens if the backup operation fails due to network issues? How about tape issues? Again, are you sure your backup file was backed up before the network/tape issue?

    With individual files, you can keep as many as you need on disk. When you use a single file, you need to make sure you have a valid backup of your backup device *before* you initialize it.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • We use two jobs for each databases tran log backups. One runs once a day and does the init. The other runs every hour.

    Tim White

  • it leaves me with up to 24 backup files per database per day which will increase the risc of human error when restoring

    Take the advice to leave things as they are.

    But mitigate the risk of human error by generating most of the restore commands with a script (insert the contents of a "dir" command into a temporary table using xp_cmdshell or similar, then cursor through the temporary table generating restore commands for each backup file).

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply