SQL Server : Backup Compression

Today's will talk about Compress Backup feature? how to take compress backup and enable backup compression feature in sql server.
Backup compression is a powerful feature offered by SQL Server (versions 2008 and above) that allows take the backup in compressed file which reduce the amount of storage space used by the backup files while at the same time increasing the speed of recovery which goes hand in hand with lower CPU usage when restoring the database from your backup.

Recommendations
When you are creating an individual backup, configuring a log shipping configuration, or creating a maintenance plan, you can override the server-level default.
Also, backup compression is supported for both disk backup devices and also for tape backup devices.

Two ways to configure
1.) Through SSMS
1.a. In the Object Explorer window, right-click the database server for which you want compressed backups to be created and select Properties.
1.b. Click the Database Settings page in the left side menu
1.c. Under Backup and restore, the Compress backup tickbox shows the current setting for this option.

- If the Checkbox is unchecked, new backups are created uncompressed by default.
- If the Checkbox is checked, new backups are compressed by default.

Note: You need Syadmin or Serveradmin fixed server role, Then only can change the default setting by checking the check box.

2.) Using TSQL
2.a. Start by connecting to the Database Engine and pressing the New Query button to create the script file.
2.b. To configure the backup compression default option at server-level so that backups are created compressed by default, run the following query:

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

These settings take effect immediately so no server restart is required.
If you would just like to see the status of backup compression without using SSMS interface, you can run the following T-SQL query:

SELECT value
FROM sys.configurations
WHERE NAME = 'backup compression default';

There is also the option of overriding the server backup-compression default when creating a backup with T-SQL by using either WITH NO_COMPRESSION or WITH COMPRESSION option in your BACKUP statement.
An example statement of this can be seen below:

BACKUP DATABASE TestDB TO DISK = 'D:\MSSQL\Backup\TestDB.bak'
WITH FORMAT, COMPRESSION;