Configure Backup Compression (SQL Server)

Applies to: SQL Server

At installation, backup compression is off by default. The backup compression default server-level configuration option sets the default behavior for backup compression. However, you can override the server-level default when creating a single backup or scheduling a series of routine backups. To change the server-level default, see View or Configure the backup compression default Server Configuration Option.

Use integrated acceleration and offloading

Beginning with SQL Server 2022 (16.x), use Integrated acceleration and offloading to compress backups with Intel® QuickAssist Technology.

Override the Backup Compression Default

You can change the backup compression behavior for an individual backup, backup job, or log shipping configuration.

• Transact-SQL

  To override the server backup-compression default when creating a backup, use either WITH NO_COMPRESSION or WITH COMPRESSION in your BACKUP statement.

  For a log shipping configuration, you can control the backup compression behavior of log backups by using sp_add_log_shipping_primary_databasesp_change_log_shipping_primary_database (Transact-SQL).

• SQL Server Management Studio

  For information about how to view or configure the backup compression default option for an instance of SQL Server, see View or Configure the backup compression default Server Configuration Option.

  You can override the server backup-compression default when creating a backup by specifying Compress backup or Do not compress backup in any of the following dialog boxes:

  • Back Up Database (Options Page)

    When backing up a database, you can control backup compression for an individual database, file, or log backup.

  • Use the Maintenance Plan Wizard

    The Maintenance Plan Wizard enables you to control backup compression for each set full or differential database backups or log backups that you schedule.

  • Integration Services (SSIS) Back Up Database task

    You can control the backup compression behavior when creating a package for backing up a single database or multiple databases.

  • Log Shipping Transaction Log Backup Settings

    You can control the backup compression behavior of log backups.

See Also

Backup Compression (SQL Server)