Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

My backup files are considerably larger than the original database files. How can I keep the files size down?

I am using SQL Server 2000

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

Is your backup job set to append to the existing backup?

From "SQL Server 2000 Backup and Restore"

Disk Backups

A database may be backed up to disk file or a disk backup device.

Any database can be backed up to a random disk file at any time. The file may either be initialized or the backup may be appended to an existing backup file.

There is a checkbox for turning on or off appending versus overwriting in the SQL wizard for setting up backups. If you can't see how to do it through the maintenance plans you may need to stop the existing backup job and recreate it.

share|improve this answer
    
This seems to work. I added 'With INIT' to each backup command. –  user16176 Jan 18 '10 at 11:48
add comment

What size are your transaction log files, the LDFs? Are you doing T-log backups if you're in full-recovery mode?

share|improve this answer
add comment

Using any third party software for compression (LiteSpeed, RedGate)?

Like any data file, a SQL DB is reserving X amount of space per row/record regardless if it uses all the space it reserves, but that reservation is reflected in the used disk space. If you take a bak file generated from SQL and run it through NTBackup it'll compress it some you'll notice (although that isn't recommended). That is because NTBackup is clearing some of that blank reserved space with the SQL DB file. 3rd party compression software does this as well, and I believe the upper versions of SQL have built-in compression.

Something to think about.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.