Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Transaction Log Backup Expand / Collapse
Author
Message
Posted Friday, February 8, 2013 12:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
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. 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.
Post #1417874
Posted Friday, February 8, 2013 1:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
midnight251 (2/8/2013)
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
Post #1417898
Posted Sunday, February 10, 2013 3:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
Thanks for the reply, that's great info. I'll be on it first thing tomorrow. I take it that I can do this while the DB is in use? We are running MS Dynamics NAV ERP using the DB. I'm going to get this going tomorrow.

Thanks again for the response.
Post #1418145
Posted Monday, February 11, 2013 7:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:18 PM
Points: 3,952, Visits: 7,170
midnight251 (2/10/2013)
Thanks for the reply, that's great info. I'll be on it first thing tomorrow. I take it that I can do this while the DB is in use? We are running MS Dynamics NAV ERP using the DB. I'm going to get this going tomorrow.

Thanks again for the response.
There won't be any issue taking either Full, Differential, or Transaction Log backups while your server is in use. There will be some contention (disk IO) as a result of the backup process but ordinarily, with a decent disk subsystem this won't be an issue. Are you going to be backing this data up locally or to a remote storage device (preferred)?


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1418449
Posted Monday, February 11, 2013 10:36 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, October 9, 2014 4:31 PM
Points: 611, Visits: 510
Wonderfully worded advice opc.three. ^.^

.
Post #1418553
Posted Monday, February 11, 2013 11:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
Thanks for the info. It will be backed up to an external drive.
Post #1418589
Posted Tuesday, February 12, 2013 1:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
Ok, just created a Maintenance Plan to backup the Trans Log file. It's going to run Mon-Fri from 7:00am to 7:00pm every three hours. I did say for it to create a new file for every database, I hope this will create me a file for every trans log backup. I also didn't see an option to truncate the log file in the Maintenance Wizard. It's going to kick off tomorrow morning aftar a full DB backup. Got my fingers crossed.
Post #1419175
Posted Tuesday, February 12, 2013 1:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
midnight251 (2/12/2013)
Ok, just created a Maintenance Plan to backup the Trans Log file. It's going to run Mon-Fri from 7:00am to 7:00pm every three hours. I did say for it to create a new file for every database, I hope this will create me a file for every trans log backup. I also didn't see an option to truncate the log file in the Maintenance Wizard. It's going to kick off tomorrow morning aftar a full DB backup. Got my fingers crossed.

The log backup will mark portions of the log that were backed up as reusable. There is nothing to explicitly 'truncate.'

Is 3 hours often enough? Your log backup interval should represent the amount of data you're organization is willing to lose should the server where the database is hosted be completely lost.

I would run the Maintenance Plan in a test environment and see how it goes. If it does not output file names in the folder structure you like then change the Maintenance Plan until it does, then configure that in your production environment.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1419178
Posted Tuesday, February 12, 2013 7:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
Sound advice opc.three, just removed from the prod DB, and I'll configure on our test DB tomorrow, though not a lot of transactions going through, but at least I'll see how the files are being created, even if no transactions. Over time this would create hundreds/thousands of files, but I guess you could purge them based on the last time of a full backup? I'll keep you posted. Thanks for all the help.
Post #1419269
Posted Tuesday, February 12, 2013 8:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
No worries if there is little or no database activity, a log backup will still be produced.

Use the Maintenance Cleanup Task to delete old backups.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1419278
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse