SQL Backup offers four compression levels, described below. Generally, the smaller the resulting backup file, the slower the backup process.

Smaller backups save you valuable disk space. For example, if you achieve an average compression rate of 80%, you can store the backup for a 42.5 gigabyte (GB) database on a 8.5 GB DVD-R dual layer disc. Smaller files can also be transferred more quickly over the network, which is particularly useful, for example, when you want to store backups off-site.

To set the compression level:

• Using the graphical user interface, select the required option in the Backup, Schedule Backup Jobs, or Log Shipping wizard, as appropriate.
• When scripting a SQL Backup job, specify the BACKUP command COMPRESSION keyword (defaults to level 1)

The compression level used to create a backup does not noticeably affect the time necessary to restore the backup.

The compression you can achieve depends upon the type of data stored in the database; if the database contains a lot of highly-compressible data, such as text and uncompressed images, you can achieve higher compression. For full backups, you can use the Compression Analyzer to perform a test on the databases to check which compression level will produce the best result for your requirements.

Compression level 4

Compression level 4 uses the LZMA compression algorithm. This compression level generates the smallest backup files in most cases, but it uses the most CPU cycles and takes the longest to complete.

Compression level 3

Compression level 3 uses the zlib compression algorithm.

On average, the backup process is 25% to 30% faster than when compression level 4 is used, and 27% to 35% fewer CPU cycles are used. Backup files are usually 5% to 7% larger.

Compression level 2

This compression level uses the zlib compression algorithm, and is a variation of compression level 3.

On average, the backup process is 15% to 25% faster than when compression level 3 is used, and 12% to 14% fewer CPU cycles are used. Backup files are usually 4% to 6% larger.

Compression level 1

This is the default compression level. It is the fastest compression, but results in larger backup files.

On average, the backup process is 10% to 20% faster than when compression level 2 is used, and 20% to 33% fewer CPU cycles are used. Backup files are usually 5% to 9% larger than those produced by compression level 2.

However, if a database contains frequently repeated values, compression level 1 can produce backup files that are smaller than if you used compression level 2 or 3. For example, this may occur for a database that contains the results of Microsoft SQL Profiler trace sessions.

Compression level 0

If you do not want to compress your backups, specify compression level 0 from the command line or extended stored procedure; in the graphical user interface, clear the Compress backup check box in the wizard. For example, you may want to do this if you require only encryption and you do not want to compress your backups.

Compression percentage

SQL Backup calculates the percentage compression of a backup by comparing the size of the SQL Backup backup with the total database size.

For example, if a database comprises a 10 GB data file and a 3 GB transaction log file and SQL Backup generates a full backup of the database to create a backup file that is 3 GB, the compression for this backup is calculated as 77%, [1-(3/13)]x100.

The compression percentage is displayed in the Activity History