Configuring and Creating a Compressed Database Backup: SQL Server 2008

Configuring and Creating a Compressed Database Backup: SQL Server 2008

This is a follow up to an earlier post providing an overview of Backup Compression in SQL Server 2008.

This is a step by step guide on how to configure and run a compressed database backup.

Configure SQL Server 2008 to allow backup compression by default:

You can either opt to choose to use backup compression when you run a backup or allow backup compression by default.

According to Books Online: At installation, backup compression default is set to 0, which makes backup compression off by default. To change the default to COMPRESSION, set backup compression default to 1. To revert the default to NO_COMPRESSION, set backup compression default back to 0.

To view what settings you have set, run the sp_configure stored procedure in the SSMS query editor. Have a look at the 'backup compression default' column.

The following script can be run in SSMS's query editor to set SQL Server to use backup compression by default: (n.b. You will need to be a member of sysadmin or serveradmin to run this script.)

USE master;
GO
EXEC sp_configure ‘backup compression default’, '1';
RECONFIGURE WITH OVERRIDE;


Configuring SSMS to run backup compression using the GUI:

1. Once you are connected to SQL Server 2008 Instance, right click the server and select properties.
2. In the Server Properties Page, click on Database Settings node which is available on the left panel.
3. On the right side panel, under Backup and restore you will be able to see a check box named compress backup.

• If the 'compress backup' checkbox is unchecked, this means that database backup compression is not enabled at the SQL Server Instance level.

• If the 'compress backup' checkbox is checked, this means that the database backup compression is enabled at SQL Server Instance level. (If you have configured SSMS to run backup compression by default this will automatically be checked)

Creating a compressed database backup using SSMS - Errors:

Once you have configured SSMS to run compressed backups you will want to create a compressed database backup. Follow the instructions to backup your database.
http://msdn.microsoft.com/en-us/library/ms187510.aspx

Note:

Compressed and uncompressed backups cannot occur together in a media set. For more information, see Backup Compression (SQL Server).

In other words, you cannot create a compressed backup and append or overwrite this backup to an existing uncompressed backup.

The following error will appear:

The backup cannot be performed because 'COMPRESSION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'COMPRESSION' or specify 'NO_COMPRESSION'. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.
BACKUP DATABASE is terminating abnormally.

You will need to:

Back up to a new media set, and erase all existing backup sets
For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box. For more information, see Creating a New Media Set.