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.


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

| improve this answer | |
EXEC sys.sp_configure N'backup compression default', N'1'

See backup compression default Option in the documentation.

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.