Set Backup Compression on SQL Server 2008R2 on by default

Question

I can't seem to find the option to set the server property that will enable compression on SQL database backup to be ON by default, so I don't have to manually set it every time.

Can anyone point me at that please.

Answer 1

Technet Online says:

To view or configure the backup compression default option

In Object Explorer, right-click a server and select Properties.

Click the Database settings node.

Under Backup and restore, Compress backup shows the current setting of the backup compression default option. This setting determines the server-level default for compressing backups, as follows:

If the Compress backup box is blank, new backups are uncompressed by default.

If the Compress backup box is checked, new backups are compressed by default.

If you are a member of the sysadmin or serveradmin fixed server role, you can also change the default setting by clicking the Compress backup box.

from: http://msdn.microsoft.com/en-us/library/bb933863.aspx

Answer 2

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

See backup compression default Option in the documentation.

This method can be easily included in a SQL Server setup script.