SQL SERVER – How to Enable Backup Compression On by Default

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.


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'

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.

SQL SERVER - How to Enable Backup Compression On by Default backupcompression

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)

SQL Backup, SQL Scripts, SQL Server
Previous Post
SQL SERVER – What Are Ghost Records and How to Clean Them Up?
Next Post
SQL SERVER – How to Delete a Single Table From SQL Server Backup File (.bak)

Related Posts

5 Comments. Leave new

  • I agree that this website is great .

  • Sanath Kumar I S
    June 16, 2018 12:18 pm

    is this option avaliable in SQL 2008 EXPRESS edition

  • Hi Sir,
    I am using below scripts to take database backup in SQL Server 2017 Enterprise edition. But I found there is no change in database backup size compress and without compress.
    I have enabled backup compression on by default option also.
    1) Without Compression
    BACKUP DATABASE [HRCNx Test13012019] TO DISK = N’C:TestHRCNx Test13012019.bak’ WITH NOFORMAT, NOINIT, NAME = N’HRCNx Test13012019-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
    2) With Compression
    BACKUP DATABASE [Database Name] TO DISK = N’C:Testdatabase.bak’ WITH NOFORMAT, NOINIT, NAME = N'[Database Name-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
    Please suggest on this.

  • Thank you


Leave a Reply