3rd party backup & transaction log backup

Question

3rd party backup & transaction log backup

Violeta-310146

Mr or Mrs. 500

Points: 539
More actions
May 15, 2006 at 2:05 am

#113020

Hello,
we are using a 3rd party backup software (ArcServe), which is running a full database backup weekly and differential backups daily.
My problem is, that the transaction logs are growing bigger and bigger, so I have to truncate and shrink some of them manually.
My question is: is it a bad idea to make a job to backup the transaction logs once or twice a day to prevent them growing so fast? Is there a risk to corrupt the backups, made by the 3rd party software thereby?
The backup scenario would be like this:
Saturday - full backups made by ArcServe's SQL Agent
Sunday to Friday - differential backups made by ArcServe's SQL Agent
Every 12 hours - transaction log backups, made by the SQL Server Agent.
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

While I don't have any experience with ArcServe for SQL Server (we use native backups), it's generally a good idea to schedule log backups as often as needed. We backup logs hourly, and I know that plenty of companies schedule log backups for every 10 - 30 minutes.

I don't know why backing up more often would corrupt a backup file, especially if you backup to seperate files.

Greg

Jason Hall Hall of Fame Points: 3906 More actions · Answer 2

If you are not backing up your transaction logs and have no need for point in time recovery than you should change the recovery model of your database to simple. When a database uses the simple recovery model the transaction logs are automatically truncated and you do not need to be worrying about them. The only reason to have your database in the full recovery model is if you wish to use point in time recovery and back up your transaction logs.

Violeta-310146 Mr or Mrs. 500 Points: 539 More actions · Answer 3

Thanks for the replies!

Some non-critical databases are in simple recovery. For the others I have to make full backup every time after trunkating/shrinking the log. With a native backup process this would not be a problem, but with the 3rd party software I have to coordinate this with my colleagues, who are operating this software (which is running not only SQL Server backups).

Therefore I was wondering if I can reduce the manual shrinking by backing up the transaction logs without fearing any disasters

Violeta-310146 Mr or Mrs. 500 Points: 539 More actions · Answer 4

Violeta-310146

Mr or Mrs. 500

Points: 539

May 15, 2006 at 12:20 pm

#637955

I meant trunCating

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

You may find that you won't need to shrink the t-log if you back it up more frequently.

Greg

Violeta-310146 Mr or Mrs. 500 Points: 539 More actions · Answer 6

I will have to test with some databases - to backup the logs for a few days and then to test if I can restore from ArcServe's backups without problems...

Thanks again!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Violeta

We have jobs that back up the transaction logs of our databases when they hit 80% full. That way we don't need to shrink our logs (which can cause fragmentation and contention for resources) and they get backed up as and when needed. If Arcserve has a command-line interface, or if the GUI has a scheduler similar to the native one in SQL Server, then this will work for you.

John

Violeta-310146 Mr or Mrs. 500 Points: 539 More actions · Answer 8

I don't think, that it has command line interface, but I am not sure, have to check this an the other options. Thanks, John!