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 :
#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..