Database Backup Compression, Amazing feature for DBA - SQL Server 2008 by serverku

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 time to execute and very CPU, memory and IO consumptive. And the important thing is backups activity should be completed within down time or pick a time when more users are not connected with databases.

What is the solution?
We have alternative ways to use the some backup tool that can help to use in this matter. But SQL Server itself provides 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 backup with compression option. And will really reduce the time required to backup it, reduce server IO and less CPU and memory consumption. It is very full features for the DBA. Let us look on below example, which will clear you the difference between the uncompressed and compressed backups. We will first perform non-compressed backups of 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
GO

#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
GO

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 like this feature..