How do you backup your Transaction logs? 1

[snootalope]

When we first installed our SQL 2005 server, we were told to create a maintenance plan that backs up the transaction log hourly. So, we did.. However, it's got to different options, overwrite or append. Which do you use?

At the moment we do an overwrite with the file and that's it, nothing furhter, but we also have our sql server setup in a mirror set.

Just curious what others do. Do you backup your transaction log do different places? Do you back it up on tape hourly? Do you keep multiple transaction log backup files?

 

[ptheriault]

You only want to init (or overwrite) you log backup once a day. If you init every hour then you can't recover to a point in time and that is the whole point to backing up your transaction log. If that is not you goal then just set your db in simple recovery mode and forget about it. Here is what a full backup stategy should look like.

once a day perform a full db backup.
after the full backup completes init (overwite) you log backup.
Then begin your hourly log backups.

for example:
12 AM full db backup. runs for 1 hour.
1:15 AM init your log. runs for 5 min.
1:30 AM through 11:45 PM you run incremental log backups.

You may find that a hour is to long a time. Ask yourself how much data could you afford to loose. is 1 hour to much? If so then cut your time interval between backups down to 30 minutes or less. Also, you will need to create 1 maint plan to init your log and one for the incremental.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[snootalope]

Good stuff Paul, thanks for the info.

 

[snootalope]

Paul, actually yesterday I set our log to do an "append" and just keep adding to this one particular transaction log backup. This morning when I came in, after a full backup was done last night, the file hadn't shrunk in size, it was right about 850MB. I was kind of thinking the log would truncate on it's own once the full backup ran. This "init", is that something special or further I have to run after a full backup is done?

If so, can I just create another maintenance plan to run after my full backups are done?

 

[ptheriault]

Yes, If you don't init your log backup will have all your backups (since you started the job) in it. If you ever had to restore you would have to search through that backup for the correct file (log sequence) to start at. I personally feel it's a best practice to init the log after you have successfully backed up your database. That way if you ever have to recover your db you just restore the full backup and your log starting at file #1 and roll forward to the point of failure.

- Paul
- If at first you don't succeed, find out if the loser gets anything.