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;
