Legato  SQL Backups

Posted on 2006-07-13
Medium Priority
Last Modified: 2008-01-09
         I am using Legato for my backups. I am trying to set up a backup strategy for my SQL data bases which include a daily full and an hourly incremental....  There are 30 DB's most of them are not used but maintained for histrorical purposes....  The full backup is 156GB in size..   One hour later using Legato networker 7.2  The incremental size was 51GB in size..   and every hour after that it was the same size  Can some tell me why, with no changes to the DB's  The Legato backup size is so large      Is there a way to reduce the size?
Question by:Cecilpierce
  • 3
  • 2
LVL 56

Accepted Solution

Handy Holder earned 1500 total points
ID: 17102003
First problem is that it's not possible to do normal incremental or differential backups of SQL (or other transactional databases) so what they do instead is a full backup of the transaction logs and not backup the database.

Second problem is the way SQL (assuming MS SQL) allows transaction log files to grow indefinitely by default instead of occasionally creating a new log file.

Ckeck the logfile sizes and to shrink them create a new transaction log for each one and then run dbcc shrnkdb. The creating a new logfile speeds up the process mentioned on Q11 of http://support.microsoft.com/kb/195760/en-us. Some SQL versions allow you to shrink the file with a maintenance job created under enterprise manager instead of issuing the SQL command. Note the command is shrinkdatabase in some versions.

Author Comment

ID: 17102706
Thank you,  not exactly what I wanted to hear.  need to figure out another way
LVL 56

Expert Comment

by:Handy Holder
ID: 17102863
Don't understand why it isn't what you want to hear. Are the transaction logs 51GB in size or did I guess wrong?

Once you've got the logfiles down to a few meg the incrementals will be much quicker and the backup software agent won't have any problems keeping them small. Logfiles grow and grow until a full backup is done and I'm assuming yours hasn't been backed up for ages.

Once it has taken a full backup it should shrink the transaction logs automatically so the incrementals get much smaller but sometimes the logfiles get so big that the backup software can't do it and you have to do it manually. After that there's an option on the agent to truncate the log automatically.

Author Comment

ID: 17103999
Don't get me wrong,,,   I'm new in the arena and struggling with trying to find a way to reclaim disk space. We currently use the sql backup and store the the .BAK and TRN files on disk.  If I can find a way to to use Legato as the only backup solution I can free up some space...   I'm using SQL 2000   Thank You very much

I also have a 15TB EMC Clariion CDL which is where I was going to store the backups for quick recovery.

any ideas are welcome

I am new at this,,  brought in to learn the backups..  so, my knowledge of the big picture is minimal.
LVL 56

Expert Comment

by:Handy Holder
ID: 17110739
If you're struggling to recover disk space then what I said in the first post ought to gain you nearly 50GB in a couple of days if the transaction logs really are that size, please confirm whether between them they add up to 51GB or not.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Is your phone running out of space to hold pictures?  This article will show you quick tips on how to solve this problem.
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
Suggested Courses
Course of the Month8 days, 20 hours left to enroll

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question