In this blog post we will discuss how to Enable Backup Compression on by default.
SQL Server 2008 had introduced compressed backup features and nowadays I see quite often enabled at most of my customers where I go for consulting. When we install SQL Server, by default this option is turned off. So if you want to take backup with compression you can write following command.
BACKUP DATABASE [AdventureWorks2014] TO DISK = N'D:\AW1.bak' WITH COMPRESSION GO
Pay special attention to keyword Compression in the above syntax. This becomes very tedious and cumbersome after a while as every single time when we have to take backup we have to specify that particular command. Additionally, when people upgrade their system and have 100s of the different backup job, they wish that they would have a single switch which they can enable or disable and the effect is applied to all the database backup by default.
The good part is that SQL Server has a default switch at the server level which we can enable or disable.
EXEC sys.sp_configure N'backup compression default', N'1' GO RECONFIGURE WITH OVERRIDE GO
When you run above script, it will enable compression by default on all of your database, even though the keyword “WITH COMPRESSION” is not used.
If you have enabled this switch at server level and if you want to disable any particular database for a single database, you can specific “WITH NO_COMPRESSION” keyword to disable the compression for that database.
You can enable the same settings with SQL Server Management Studio as well. Here is the property of the server where you can enable backup compression setting by default.
Please note when you enable compression you may notice your backup takes less space and completes faster along with that there will be a marginal increase in the CPU consumption. Personally I have enabled Backup Compression on all of my databases.
Reference: Pinal Dave (https://blog.sqlauthority.com)