TSQL - Database backup with compression

The scripts below contrast a typical backup script, versus one that uses compression. You might want to consider compressing to save space.


Typical backup script:

BACKUP DATABASE xxx
TO DISK = 'C:\Backup\xxx.BAK'
WITH FORMAT,
MEDIANAME = 'C_Backup',
NAME = 'Full database backup of xxx';


This one compresses your backup, saving you, in my experience, about 75% of the space (an 800 MB dbase compresses, typically, to around 200 MB).

BACKUP DATABASE xxx
TO DISK = 'C:\Backup\xxx.BAK'
WITH COMPRESSION,
MEDIANAME = 'C_Backup',
NAME = 'Full database backup of xxx';


Compatibility tested on: MS SQL Server 2008 Standard.