June 17, 2009 at 10:12 am
Hi
I'm currently backing up our transaction log every hour to a file names like
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
June 17, 2009 at 1:08 pm
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
June 17, 2009 at 1:58 pm
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 25, 2009 at 12:09 pm
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.
June 25, 2009 at 6:54 pm
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 26, 2009 at 7:35 am
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
June 26, 2009 at 8:25 am
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