Storage, although cheap is still a limited resource. In our development and sustenance environments, we often get databases from multiple different sources which we need to use to troubleshoot certain issues or carry out some research and/or other development activity on. The backups of these databases take up considerable amount of space and we ended up either using 3rd party tools, or ensuring that the DBA uses a file compression tool to reduce the disk space consumption. When SQL Server 2008 was released, it’s backup compression feature came to our rescue.

Backup Compression (Single-use)

• Starting SQL Server 2008 Enterprise Edition and above, Microsoft SQL Server has the ability to perform a “compressed backup”
  
  
  
  • Since Developer Edition contains the full feature set of the Enterprise Edition, it too can perform compressed backups
  
  
• Every edition of SQL Server 2008 and later can restore a compressed backup
  
• Compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly
  
  
  
  • Please note the using backup compression increases CPU usage

Specify backup compression using the SSMS UI

When backing up the database, we can choose to compress the backup in the “Options” tab of the Backup database wizard.

Specify backup compression using the T-SQL

The backup database wizard is great, however, is not used  frequently in environments. Most of the production, staging and quality assurance environments use T-SQL statements to backup a database. Compression can be specified in the BACKUP DATABASE statement as shown below:

BACKUP DATABASE [AdventureWorks2012]

TO  DISK = N’C:DataMSSQL11.SQL2K12MSSQLBackupAdventureWorks2012.bak’

WITH NOFORMAT, NOINIT,

NAME = N’AdventureWorks2012-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

Setting Backup Compression by Default

Instead of remembering to choose the backup compression, an alternative arrangement can be done wherein backup compression is always enabled by default on the given SQL Server instance. This can be done in two ways:

Backup Compression by Default setting using the SSMS UI

Within the Object Explorer, we launched the Server Instance Properties window by right-clicking on the SQL Server instance name and choosing “Properties”. When we navigate over to the “Database Settings” page, we find a switch – “Compress backup”. When checked (and the SQL Server contains an Enterprise Edition feature set), all backups using server default setting will be compressed backups.

Backup Compression by Default setting using T-SQL

Because backup compression is a SQL Server instance level configuration setting, the only way we can use T-SQL to set backup compression by default is to use the system stored procedure – sp_configure.

sp_configure ‘backup compression default’, 1

RECONFIGURE

GO

Setting the option ‘backup compression default’ to 1 will cause future backups from the server to be compressed, unless otherwise explicitly stated in the BACKUP DATABASE command.

References:

• Backup Compression: http://technet.microsoft.com/en-us/library/bb964719.aspx
  
• Backup Compression (Default) option: http://msdn.microsoft.com/en-us/library/bb677250(v=sql.105).aspx

Until we meet next time,

Be courteous. Drive responsibly.