Microsoft first introduced backup compression in SQL Server 2008. With backup compression you can speed up our backups, while making your backups smaller. While this sounds good, keep in mind compression doesn’t come for free. SQL Server requires more CPU to compress a backup then not to compress a backup. The reason compressed backups run faster is because the backup blocks are compressed therefore more of your database can be writing to a backup file with each I/O operation. Because less I/O is performed your backups run faster, and they are smaller because less blocks are written.
The size of your compressed backup will be smaller than a non-compressed backup. Of course, the compression saving number can vary greatly depending on the data in your database and how compressible that data is. You will need to test compressed backup in your situation to truly understand how much faster they will run and how much disk space you might save with backup compression.
To create compressed database backups, all you need to do is add the COMPRESSION option to the BACKUP command as shown below:
BACKUP DATABASE AdventureWorks2016CTP3
TO DISK = 'C:tempAdventureWorks2016CTP3_compressed.bak'
WITH FORMAT, COMPRESSION;# # #
See All Articles by Columnist Gregory A. Larsen