SQL Server Backup Compression Default

In SQL Server 2008 Microsoft added the long overdue backup compression feature.  You may have noticed in the backup dialog box the pull down menu to use the DEFAULT SERVER SETTING for compression.

image

To determine what the DEFAULT value actually is:

image

Out of the box, SQL Server 2008 backup compression is turned OFF.  To avoid the need to select COMPRESSION every time a backup is taken (assuming that is your intent), you can enable backup compression at the server level:

Enable Backup Compresion:
  1. sp_configure ‘backup compression default’, 1
  2. reconfigure
  3. go

This will simply allow you to not have to worry about explicitly selecting the backup COMPRESSION option in the backup dialog box.

If all of your servers are in SQL Server’s new CENTRAL MANAGEMENT SERVER, you can enable backup compression on all of the servers by executing the above command ONE time.

Stay curious… keep learning…

0 Comments

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>