2

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

3 Answers 3

2

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.

1
  • This seems to work. I added 'With INIT' to each backup command.
    – user16176
    Jan 18, 2010 at 11:48
1

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

1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .