The era of innovation with SQL Server administration side of things keeps happening, yet many of us miss these capabilities getting added into the system. In this article, I want to share a recent story that highlights the importance of utilizing backup compression in SQL Server.

During a routine performance tuning exercise at one of my client’s place, I was asked to review their standard procedures used for daily Agent jobs. As I delved into their maintenance plans and scripts, I discovered that some of the backup scripts were created for SQL Server 2005 and were still being used, even after two upgrades.

Curious about why they were not utilizing the backup compression feature of SQL Server, I asked the DBA about it. They were unaware of the advantages and how easy it was to implement. I helped them modify the script to enable backup compression, and the results were astounding. The backup size reduced drastically.

Let’s take a look at a simple example:

-- Normal Backup performed on the Database
BACKUP DATABASE [AdventureWorks2016] TO DISK  
= 'C:\Backup\AdventureWorks_uncompressed.bak' WITH STATS = 10, NO_COMPRESSION, FORMAT;
GO

-- Normal Backup (with Compression enabled) performed on the Database
BACKUP DATABASE [AdventureWorks2016] TO DISK  
= 'C:\Backup\AdventureWorks_compressed.bak' WITH STATS = 10, COMPRESSION, FORMAT;
GO

As you can see, the above command changes just in a single word, and it can start utilizing the backup compression abilities. In our example, this simple change resulted in a 5 times disk compression, significantly reducing the backup size.

Backup compression is a powerful and often underappreciated feature when it comes to working with SQL Server. It not only saves disk space but also improves backup and restore performance. By reducing the size of backups, you can optimize storage utilization and reduce backup and restore times.

If you haven’t already, I encourage you to explore and utilize the backup compression feature in your SQL Server environment. It’s a simple yet powerful change that can have a significant impact on your backup strategy.

Have you used backup compression in your environment? I would love to hear about your experiences and how efficient this feature has been for you. Please share your thoughts and insights in the comments below.