Using SQL Server Compressed Backups
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.
For SQL Server 2014 or newer you will see this screen:
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
- Database Health Monitor Backup report
- Database Health Monitor Download
- Stedman Solutions, SQL Server Consulting
- Quick Scan Report
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.
More from Stedman Solutions:
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!
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