Using SQL Server Compressed Backups

Download PDF

The Quick Scan report in Database Health Monitor detects when you are performing SQL Server backups without compression, when the compression option is available. This applies to Full backups, Transaction log backups and Differential backups.

Not using compressed backups? Why not?

Benefits of compressed backups

  • Faster backup time
  • Faster restore time
  • Less I/O at backup and restore time
  • Since you are using less disk space, you can keep more backups around.

There are a couple minor drawbacks, the compressed backups take more CPU, not much more, but a tiny bit more.  Also the compressed backups don’t compress very much when your database is using Transparent Data Encryption (TDE).

Configuring Compressed Backups

To configure compressed backups, locate the backup job in your maintenance plans, and you will see one of the following dialogs depending on your version of SQL Server.

For SQL Server 2008R2 to SQL Server 2012, it will look like this.

Compressed Backups SQL Server 2008R2

For SQL Server 2014 or newer you will see this screen:

Compressed Backups SQL Server 2014

Once you enable compressed backups you can check in on the Database Health Monitor Backup report after the next backup has been run to confirm that compression is being used.

Related Links

Need Help

Stedman Solutions, the provider of the Database Health Monitor Application offers consulting solutions, and can help with getting your backups and restores, or any other database administration issues you may have.

Stedman Solutions for help TempDB only has a single data file

 

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

2 Comments on “Using SQL Server Compressed Backups

  1. Actually the native compression of SQL is not so good, so I use 7zip to compress the dump-file and the result is much smaller and can be encrypted.
    I do that as I have to send the Backup Files to another Server across the Internet, so transfer time will be reduced (from 4+ hours to less then 2).
    I have automatized the whole process with simple DOS batches and it works smooth.

    • Philippe,
      That is a good point on the level of compression, and the encryption.

      They way that you are doing it, however adds I/O, and the benefit of using the native compression in SQL Server is that it can dramatically reduce the I/O, and speed up the time of the backup.

      If you want to have the smallest possible backup file, then the 7zip option is probably best, but if you want to have the least I/O then the native solution would certainly be better.

      Steve Stedman

Leave a Reply

Your email address will not be published. Required fields are marked *

*