Production Database servers may have databases which are heavily in size. For the maintenance of those database backups are very hard and lengthy as the backups of those heavily databases take more tome to execute and very CPU, memory and IO consumptive. And important thing is backups activity should be completed with in down time or pick time when more users are not connected with databases.

What is solution?
We have alternative ways to use the some backup tool that can help to use in this matter. But SQL Server itself provide the best feature and supported SQL Server 2008 or newer version. That is "Backup Compression". You can read my earlier posts for Automated All Databases Backups, Database Backup files Verification and Details and Split Database Full Backup to Multiple files

Using this feature , we can take a database backups with compression option. And will really reduce time required to backup it, reduce server IO and less CPU and memory consumptive. It is veryfull feature for the DBA. Let us look on below example which will clear you the difference between the noncompressed and compressed backups.We will first perform non-compressed backups for the database which have 4 GB size.

Using Management Studio:


Using TSQL:

#1. Performing noncompressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_NonCompressedBackup.bak' 
WITH NAME = N'ReportServer-Full NonCompressed Database Backup',
NO_COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

#2. Performing compressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_CompressedBackup.bak' 
WITH NAME = N'ReportServer-Full Compressed Database Backup',
COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

From the result oputput , you can view the time for the backup execution, CPU usage. Here you have screen for the both of the backups size.













You can use below query to get the backup statistics,

SELECT 
bs.database_name AS DatabaseName , -- Database name
backup_size/compressed_backup_size as CompressionRatio, 
CASE bs.type 
 WHEN 'D' THEN 'Full' 
 WHEN 'I' THEN 'Differential' 
 WHEN 'L' THEN 'Log' 
 WHEN 'F' THEN 'File or filegroup' 
 WHEN 'G' THEN 'Differential file' 
 WHEN 'P' THEN 'P' 
 WHEN 'Q' THEN 'Differential partial' 
 END AS BackupType, -- Type of database baclup
bs.backup_start_date AS BackupstartDate, -- Backup start date
bs.backup_finish_date AS BackupFinishDate,  -- Backup finish date
bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
bs.backup_size AS  [BackupSize(In bytes)],  -- Normal backup size (In bytes)
compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
ON (bs.media_set_id=bmf.media_set_id)
and database_name = 'ReportServer'
ORDER BY bs.backup_start_date DESC

You can set the default backup setting to Compressed as following, 

By TSQL :

USE MASTER
GO

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

From UI :

 I hope you liked this feature..