Take the 2-minute tour ×
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's 100% free, no registration required.

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.

Ca anyone point me at that please.

share|improve this question
    
If you need compressed SQL Server backup you can use SQLgzip. It supports all SQL Server versions starting from 2000. Free fully functional version (32-bit) supports different level of compression and TSM. –  user24955 Jun 14 '13 at 7:50

3 Answers 3

up vote 5 down vote accepted

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

share|improve this answer
    
Thank you that worked brilliantly! –  Preet Sangha Sep 6 '12 at 3:08
1  
My pleasure! By the way, I simply did a search like: google.ca/search?q=backup+compression+default+sql+server+2008 to find the answer! –  Max Vernon Sep 6 '12 at 3:14
    
You know I did lots of searches - normally my google Fu is very good. Today it failed me :-) –  Preet Sangha Sep 6 '12 at 4:08
EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
share|improve this answer
    
This is a great way to do this that can be scripted into a SQL Server setup script. –  Max Vernon Sep 6 '12 at 16:52

I know this question has been already answered, but I found an article that easily details all of the answers above with screenshots included.

It also has some more details related to SQL Server compression, including some recommendations and some guidelines for security in order to use the T-SQL Script above to change the default compression.

You can find the article at http://sqlbak.com/blog/how-to-configure-backup-compression/

I hope this helps others too!

share|improve this answer
    
Link only answers are discouraged. You should put the salient reasons and recommendations/guidelines in your answer. –  Preet Sangha Jun 29 at 19:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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