SQL Server 2008 Backup Compression
I’m still in process to make myself familiar with latest SQL Server release i.e. SQL Server 2008. To begin with, I concerted on new features/enhancements related to database administration and came across with this interesting new feature - SQL Server 2008 Backup Compression.
In the past few years, there has been a tremendous growth in database sizes and consequently a steep increase in overall spending on datacenter space. While as a DBA, we may have limited measures to counter data growth, we however have major control on space usage when it comes to dealing with database Backups and Restores (particularly with large databases).
SQL Server 2008 offers you capability of compressing your database backups *. The idea behind compression is simple: To Save Space and allow the backup of more data onto a given media set.
Let’s have a closer look at this exciting new feature; SQL Server 2008 Backup Compression
By default, the ‘Backup Compression’ is set OFF. This can be set to ON (enabled) from Management Studio GUI (refer screen) or with T-SQL Command (refer code)
-----------------------------------------------------------------------------------
USE master;
GO
EXEC sp_configure ‘backup compression default’, '1';
RECONFIGURE WITH OVERRIDE;
-----------------------------------------------------------------------------------
Once enabled, all the subsequent backups will be compressed by default.
Note: If in case you don’t want to enable the ‘Backup Compression’ at server level, you can override this setting for a specific backup job.
Now, let’s understand how ‘Backup Compression’ works. I’ll illustrate with a simple example of manually enabling the backup compression for a specific backup job.
Example
- I have a database named ‘A_large_database’ of size 1997.94 MB’s
- I will now take a database backup with below T-SQL. REMEMBER: The default compression is NOT enabled.
----------------------------------------------------------------------------------
BACKUP DATABASE A_large_database
TO DISK = 'D:\tempdb\Before_Compression.bak'
GO
----------------------------------------------------------------------------------
- The size of backup file created is 4272 KB’s
- Will now try taking a backup with compression
----------------------------------------------------------------------------------
BACKUP DATABASE A_large_database
TO DISK = 'D:\tempdb\With_Compression.bak'
WITH COMPRESSION
GO
----------------------------------------------------------------------------------
- The size of backup file created is 775 KB’s i.e. Just 18 % of our previous backup files size.
Immediate Benefits:
1. Less storage and Tape requirements
2. Reduced spending (due to point 1)
3. Faster database recoveries - will cover this in detail in my subsequent blog post, “How to achieve faster Database recoveries using SQL Server 2008 Backup Compression”
All in all, SQL Server 2008 Backup Compression is a very useful feature and can be adopted in your environment without much change to your applications. For more details, please refer Backup Compression >> https://technet.microsoft.com/en-us/library/bb964719.aspx
Additionally, if you have below question:
Q. Can I have ‘Backup Compression’ on database where ‘Data Compression’ is already enabled?
A. Straight answer is ‘YES’, as backup compression uses a different method of compression, hence with ‘Data Compression’ enabled, data is compressed twice. Example:
<> Database Size 100 GB’s
<> With ‘Data Compression (Row)’ giving 30% compression, then database is 70 GB’s
<> With ‘Backup Compression’ giving additional 50% compression, the backup set size will be 35 GB’s
However, it’s not recommended to use ‘Backup Compression’ on database where ‘Data Compression’ is enabled, as this will hog CPU and will yield limited benefits. For details, refer >> https://blogs.msdn.com/b/psssql/archive/2008/03/24/how-it-works-sql-server-2008-backup-compression-database-compression-and-total-data-encryption.aspx
* This feature is currently available only in SQL Server 2008 Enterprise Edition, however, all SQL Server 2008 edition can restore a compressed backup.
Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.