I recently attended a SQL 2012 training class, and one of the coolest features that was discussed during the class was the built-in backup compression. This feature became available in SQL 2008 R2. Most people recommend it be turned on as it saves on Disk I/O , Disk Storage and backup time at the expense of some CPU and Memory usage during the backup. The best part it is it is a simple check box either at the database level or server level to enable.
I’m glad they added this feature, it has always bothered me that backing up basically text data took up so much space.
Enable Compression of Database Backups
- 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.
Source: Microsoft TechNet: View or Configure the backup compression default Server Configuration Option
