Our company has never backed up the transaction log file, and is now about 143 gig, with about 35 gig left on the drive, and I have to get this under control. As you might have guessed, I'm relatively new to SQL. We do a full db backup every night. I would like to start a log file backup about every two hours during business hours to truncate the log file and limit the growth, and after a bit, re-size the log file.
You may want to take an initial backup and then shrink it one time to a reasonable size to reclaim some of that 143 GB but do not shrink the log after every backup. Please read this article:Managing Transaction Logs By Gail Shaw
I will be doing this through Management Studio. I will be backing this up onto another drive into a backup folder. My question is regarding the overwrite media options. Do I keep appending to the existing backup set? Do we create new files for every time the log file gets backed up, and if so, how do we do this. I guess I'm asking for the proper way to set this up, and make a restore (if needed) as easy as possible. We are on SQL 2008R2.
Any help or direction would be greatly appreciated to get this on track.
I like to take backups where there is only one backup per file and the file is named with a date and timestamp, i.e. I never append a backup to an existing file. This keeps my restore scenarios simple because I never have to wonder what each file contains and I can tell from the name when it was taken.
You should look into automating your backups. The Maintenance Plan Wizard
is a quick way to setup automated tran log backups and can give you the named files one backup per file that I am talking about.
__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato