Quick Scan Report – Not Using Compressed Backups

Using compressed backups in SQL Server provides several compelling benefits that enhance backup efficiency, performance, and storage management. Here’s an overview:

1. Reduced Storage Requirements

  • Benefit: Compressed backups significantly reduce the size of backup files, often by 60-80%, depending on the data’s compressibility.
  • Impact: Lower storage costs and more efficient utilization of disk space, especially in environments with frequent backups.

2. Faster Backup and Restore Times

  • Benefit: Smaller backup sizes translate to faster backup creation and quicker restores. This is particularly critical for meeting tight Recovery Time Objectives (RTOs).
  • Impact: Operations like disaster recovery or moving databases between environments are completed more quickly.

3. Less Network Bandwidth Usage

  • Benefit: When storing backups on a remote server or in the cloud, compressed backups reduce the amount of data that needs to be transmitted over the network.
  • Impact: Saves bandwidth, reduces costs for cloud storage transfers, and speeds up offsite backup processes.

4. Improved Server Performance During Backups

  • Benefit: Compression reduces the I/O workload because there’s less data to write to disk.
  • Impact: While compression does increase CPU usage, the trade-off often leads to better overall performance compared to uncompressed backups.

5. Integration with Native SQL Server Tools

  • Benefit: Backup compression is built into SQL Server (since SQL Server 2008), meaning no third-party tools are required.
  • Impact: Easy to enable and manage, either via T-SQL (WITH COMPRESSION) or through SQL Server Management Studio (SSMS).

6. Compatibility with Differential Backups

  • Benefit: Compressed backups can be used alongside differential backups, further optimizing storage and backup performance.
  • Impact: Flexibility in designing your backup strategy while still leveraging compression benefits.

7. Reduced Backup Storage Costs in the Cloud

  • Benefit: Compressed backups save on storage costs in cloud environments like Azure Blob Storage or Amazon S3, where pricing is often based on data size.
  • Impact: Cost savings grow significantly over time, especially for large databases.

When to Be Cautious

  • Increased CPU Usage: Compression requires additional CPU cycles. On systems with limited CPU resources, this could impact performance, so it’s essential to test in your environment.
  • Data That’s Already Compressed: Certain types of data (e.g., encrypted or image-heavy databases) may not compress well, reducing the benefits.

To monitor Compressed backup in sql server, 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 backup in sql server

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

Compressed backup in sql server

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.

Compressed backup in sql server

Pro Tip: Using Stedman Solutions’ Managed Services can help you configure and optimize your SQL Server backup strategy, including leveraging compressed backups. We’ll ensure your backups are efficient, cost-effective, and meet your RTO and RPO requirements. Learn more about our services here.