Problem
Databases keep getting larger, and the time we are given for backups to complete seldom keeps pace. With SQL 2008 you have the ability to compress your database backups, saving disk space but more importantly saving time.
Solution
Enabling database backups to use compression can be done in one of two ways. You can either configure all backups for the instance to be compressed (it is disabled by default), or you can issue the WITH COMPRESSION command for a specific database backup.
To enabling backup compression at the server level, right-click on the instance name inside of server explorer in SSMS to bring up the Server Properties screen. Go to the ‘Database Settings’ and you should see the following:
In my opinion, this setting is buried on a very busy page, and is quite easy to miss. Fortunately you have a second option to configure this setting by using some T-SQL:
EXEC sys.sp_configure ‘backup compression default’, ‘1’
GO
RECONFIGURE WITH OVERRIDE
GO
If you did not want to enable compression for the server instance, you also have the option of enabling backup compression for individual backups:
BACKUP DATABASE [AdventureWorks]
TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10.BACONMSSQL\Backup\AW_compress.bak’
WITH COMPRESSION
GO
The actual amount of compression you will get depends primarily on the type of data you have in your database. If you are storing .jpg files in your database, for example, then do not expect much benefits from compression. Another factor to consider is encryption. If your data has been encrypted previously, then your compression ratio will be close to 1:1. Lastly you need to consider if your database already has data compression enabled. If you are already compressing data then you will not see much benefit from backup compression.
To calculate the compression ratio for your backups you can utilize two columns in the backupset table inside of the msdb database. This simple T-SQL statement will show you the ratio for all of the backups currently stored in the backupset table.
SELECT backup_size/compressed_backup_size
FROM msdb..backupset
GO
Next Steps
- Enable backup compression for your instance or for individual backups by following the steps listed above.
Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry in roles including programmer, developer, analyst, and database administrator.
LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010. While at Confio, his research and experience helped to create the initial versions of the software now known as SolarWinds Database Performance Analyzer. LaRock joined the SolarWinds family through the acquisition of Confio in 2013.
LaRock is also the Immediate Past President of the Professional Association for SQL Server (PASS) and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses his time working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle, MySQL, SAP, and DB2, making it his mission to give IT and data professionals longer weekends.