Compression Levels

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

To set the compression level:

using the graphical user interface, select the required option in the Back Up, Schedule Backup Jobs, or Log Shipping wizard, as appropriate.
using the SQL Backup toolkit, specify the BACKUP command COMPRESSION keyword (defaults to level 1)

Note that if SQL Backup Lite is installed on the SQL Server, you can choose only compression level 0 (no compression) or compression level 1.

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

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. For more information about the Compression Analyzer, see Compression Analyzer.

Compression level 3

Compression level 3 uses the zlib 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 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