SQL Server Database Backup Compression

SQL Backup Master v4.3 introduced the ability to pass custom parameters to the database backup command that is sent to SQL Server. One example of how this capability can be useful is if you wish to enable SQL Server’s built-in backup compression capability.

Before you begin, though, keep in mind that native backup compression is available only in certain versions and editions of SQL Server. You’ll need the Enterprise or Standard edition of SQL Server 2008 R2 or later to use this feature – it isn’t supported in lesser editions such as Express, Web, etc.

It’s also worth noting that SQL Backup Master’s compression feature will typically yield superior results than SQL Server’s built-in backup compression. However, SQL Server’s backup compression feature has the advantage of requiring less working disk space and in some cases might be a better fit for your backup workflow.

To enable SQL Server backup compression in SQL Backup Master:

  1. Double-click your backup job in the SQL Backup Master main window
  2. Click the Backup job settings link
  3. Select the Database tab
  4. Enable the Append the following arguments to the backup database command checkbox
  5. Add the keyword COMPRESSION to the backup database command arguments field

The result is shown in the screen shot below.

sql-server-backup-compression

Next, you’ll likely want to disable SQL Backup Master’s compression feature. Although you aren’t strictly prohibited from using it in conjunction with SQL Server’s built-in backup compression, you won’t typically see much (if any) benefit from doing so. To disable it, select the Compression & Encryption tab in the backup job settings window and select the Compression Disabled option.

You can now save your changes and execute the backup job. The resulting database backup will have a .bak file extension and should be appreciably smaller than its uncompressed counterpart.

IMPORTANT: We strongly recommend that you run backup and restore tests every time you change your backup job settings – especially when using advanced features like the one described above.

posted by SQL Backup Master at 1:15 pm