How to Compress Backups in SQL Server 2008
What is Backup Compression?
Microsoft introduced a new feature for SQL Server called Backup Compression. This useful feature was first released in the SQL Server 2008, albeit only in the Enterprise edition. A clamor ensued for it to be included in lower editions, and Microsoft listened.
With the release of SQL Server 2008 R2, it was available in the standard version as well.
Database backup files, especially full backups, can grow very large and eat up a lot of disk space. File compression is the obvious solution of reducing the size of backups. Before the introduction of the backup compression feature in SQL Server, a database administrator had to use 3rd-party tools such as WinRAR to compress the backup files. With this nifty new add-in, the compression portion can now be handled as part of the backup process itself.
Clear enough. Now tell me about the benefits.
First is the aforementioned convenience of handling the compression "inline" as part of the backup procedure itself, rather than having to wait for the backup to first complete, then using another application to compress the resultant file.
Backup compression also saves on disk space. However, there is a caveat here: the amount of file reduction you will see depends a lot on the type of data you have in your database. If you have a significant portion of the database holding data types that don't compress well, such as images, video files and sound clips, then you are likely to observe little reduction in file size, perhaps in the order of 2 - 10 %.
An additional benefit is that your backup process will take less time overall, because of the reduced disk I/O activity. This means the database administrator has more time for critically important activities, which for me are sleep and the catching up on the latest 'Game of Thrones' episode.
But wait, all that glitters is not gold. What are the disadvantages?
The compression feature significantly increases the load on the server's CPU while the backup process is running. So backups with compression should be scheduled during periods of low server activity, such as late at night.
Another potential downside was mentioned earlier - if you have images and video files in your database, you may not see much benefit from compression.
Now show me how it's actually done.
There are 2 different modes of enabling backup compression, at the server level for all databases, or as a per-backup option to be enabled every time you perform a backup. For each mode, you can use either the SQL Server Management Studio (SSMS) or a T-SQL code snippet in a SQL Server query window. We will go through both modes.
At the server level: As mentioned previously, this mode will enable backup compression for all backups on that server. The default state for backup compression is 'off'. It can be done via either T-SQL code or SSMS.
- Using T-SQL code:
Run the script below in a SQL Server query window:
EXEC sp_configure 'backup compression default', '1'
RECONFIGURE WITH OVERRIDE
- Using SSMS: Open the SSMS window, connect to the server instance you want to operate on, right-click on it and choose Properties from the menu (see Fig. 1 below). On the window that opens, in the left pane, scroll down and choose Database Settings. On the right side pane, tick the check-box to enable the 'Compress Backups' feature. Click OK and you're done (see Fig. 2 below).
At the database level: This enables backup compression for each backup procedure. Unlike the set-and-forget ability of server-level compression as outlined above, the database administrator must perform database-level backup compression every time he or she performs a backup. This can also be done in 2 ways, using either T-SQL code or via the SQL Server management Studio (SSMS) properties window. Each is demonstrated below:
- Using T-SQL code:
Enter the code below in a SQL server query window. You will replace 'Mysampledb' with the actual name of your database to be backed up, and 'C:\Program Files\Microsoft SQL Server\Mysampledb\Mycompressdb.bak' with the actual path and filename of the compressed backup file.
BACKUP DATABASE [Mysampledb]
TO DISK = N'C:\Program Files\Microsoft SQL Server\Mysampledb\Mycompressdb.bak '
- Using SSMS: In the SSMS window, navigate to the database you want to operate on. Right-click on it and in the menu choose Tasks > Back Up (see Fig. 3 below). This opens another window. In this window, click Options on the left pane, and in the right pane, expand Set backup compression (at the very bottom of window) and choose the Compress backup option (see Fig. 4 below).