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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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.