I was fortunate enough to attend Paul Randal’s and Kimberly Tripp’s IETPO1 this past Spring. During the week long training I met Tim Radney (he’s a SQL Consultant at SQLSkills). I approached him, introduced myself and as we were talking, the subject of SQL Server backups came up. I explained my work’s current backup strategy and how I’d like to make it more efficient, both in speed and disk space. Tim suggested I enable the instance-wide backup compression option in SQL Server Management Studio (see image below)
Since then, I have checked that option on all my database servers. In some cases it has compressed the backup file size by 80%. How neat is that!
How to Enable SQL Instance Backup Compression in SQL Server Management Studio
Right click the Instance, click Properties, click “Database Settings” on the left, and make sure there’s a check mark in “Compress backup” check box. Done.