Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Transaction Log Backup

Author  Topic 

midnight
Starting Member

1 Post

Posted - 2013-02-07 : 22:02:00
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. 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. 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.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-02-09 : 17:01:03
I would suggest you to create individual files for Tranlog backup with proper naming convention. You can have a job to do this or use Maintenance plan to do it and schedule as a job.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-10 : 02:02:37
If these log backup files are required for Recovery and are part of the Service Level agreement , have you thought of keeping the log backup files in a similar repository to the FULL backups.
I'm thinking about situations where there is a server failure and you need to recover to a point in time

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -