#0199-SQL Server-Backup compression-Single Use & Default setting


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.


image


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.


image


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:



Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

4 thoughts on “#0199-SQL Server-Backup compression-Single Use & Default setting

  1. dishdy

    All automated backups that I have ever put into production always included compressing it using either winzip or 7zip (including Oracle databases – I will now check if Oracle has this feature).

    Yes, this is a welcome feature in SS2008 and good to know about.

    It seems to me that the default for an installation should be to always compress a backup. Is there any reason why you would not want to compress a backup?

    Like

    Reply
  2. Nakul Vachhrajani

    @dishdy: I don’t see any reason not to have backups compressed by default. I mean, if one is going to compress it using a 3rd party tool, why not compress it right out of the box? All my servers have compressed backups ON by default.

    Like

    Reply
  3. Dubelewskyj Oleksandr

    It is another tool in toolbox.
    Why do not compress by default?
    Because you can create backups in work hours. You can back up to another disk, but CPU in this situation is going to be higher.
    As additional tip: compressed backup is still bigger then “normal” archive.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s