How to Configure MSSQL Backup Compression as the Default Option

Introduction

In Microsoft SQL Server, backup compression is a powerful feature that can significantly reduce the size of database backups, thereby saving storage space and improving backup and restore performance. This blog post will guide you through the steps to configure MSSQL backup compression as the default option, ensuring that all future backups are compressed by default.

Method 1

Step 1: Connect to SQL Server Management Studio (SSMS)
Launch Microsoft SQL Server Management Studio and connect to your SQL Server instance using appropriate credentials.

Step 2: Access Server Properties
Right-click on the server name in the Object Explorer and select “Properties” from the context menu. This opens the “Server Properties” dialog box.

Step 3: Navigate to Database Settings
In the “Server Properties” dialog box, navigate to the “Database Settings” page. Here, you’ll find various settings related to database configurations.

Step 4: Locate Backup Compression Default Option
Scroll down to the “Backup and Restore” section within the “Database Settings” page. Look for the “Backup compression default” option. By default, it is set to “Use the default server setting.”

Step 5: Enable Backup Compression
To configure backup compression as the default option, select the “Compress backup” option. This ensures that all future backups will be compressed by default. Alternatively, you can select “Do not compress backup” if you prefer to disable backup compression.

Step 6: Apply and Save Changes
Click on the “OK” button to save the changes and apply the new backup compression default setting. Ref. https://learn.microsoft.com/

Configure MSSQL Backup Compression as the Default Option

Method 2

Use the following query, Ref. https://learn.microsoft.com/

EXEC sp_configure 'backup compression default', 1 ;  
RECONFIGURE;  
GO

Step 7: Verify the Configuration
To verify whether the configuration change has been applied successfully, you can execute the following query:

DECLARE @CompressionEnabled BIT = (SELECT CONVERT(BIT, value)
                                   FROM sys.configurations
                                   WHERE name = 'backup compression default')

IF @CompressionEnabled = 1
    PRINT 'Backup compression is enabled by default.'
ELSE
    PRINT 'Backup compression is disabled by default.'

If the value returned is 1, it means backup compression is enabled by default. A value of 0 indicates that backup compression is disabled by default.

Ref. https://learn.microsoft.com/

Conclusion

Configuring MSSQL backup compression as the default option is a simple process that can have a significant impact on storage space and backup performance. By following the steps outlined in this blog post, you can ensure that all future backups are compressed by default, saving storage costs and improving backup and restore times.

Remember to periodically review and adjust your backup compression settings based on your specific requirements and available resources.