Backup Compression

Who wants big backups?  Not me, and probably not you

You can compress your backups, starting from SQL Server 2008. 

In a maintenance plan, just select the option Compress

In a script, add the magic words WITH COMPRESSION

But you can set the default so that if you forget, it compresses for you. 

I'm indebted to Microsoft for this unusually clear description

-- before - 0 means OFF

SELECT value  
FROM sys.configurations  
WHERE name = 'backup compression default' ; 
GO 

EXEC sp_configure 'backup compression default', 1 ; 

RECONFIGURE WITH OVERRIDE ; 
GO
 

--after - 1 means ON

SELECT value  
FROM sys.configurations  
WHERE name = 'backup compression default' ; 
GO


You don't even have to restart anything!


Is it worth doing?  What do you mean, don't you trust me?
I think so.  Here's a way to calculate the actual compression achieved - on my server, out of 1,628 backups, I got a compression ratio of 3.02

SELECT

       sum(backup_size) as All_Backups
       ,sum(compressed_backup_size) as All_Compressed
       ,sum(backup_size)/sum(compressed_backup_size) AS Compression_Ratio
FROM msdb..backupset;