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)

Backup_Compress_Jpg

-----------------------------------------------------------------------------------

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.

Comments

  • Anonymous
    December 23, 2008
    Hi, Nice to read your first technical post. Looking forward to read more of them. With regards, ashish narmen

  • Anonymous
    December 26, 2008
    >Immediate Benefits: >1. Less storage and Tape requirements Saving on tape requirements might be incorrect if your tape unit does on-the-fly hardware compression.  I remember testing compressing databases in the mid-90's, and with DAT drives, we didn't save any space. (the DAT drives were doing their own compression)

  • Anonymous
    December 26, 2008
    Good going Varun - Nice post!!

  • Anonymous
    December 29, 2008
    Hello Dale, Appreciate your query ! SQL Server 2008 Backup compression results in “Less storage and Tape requirements”, is being said in a scenario when a database backup is compressed using native software compression (SQL 2008 Backup Compression in this case) and stored in a Disk or Tape Media. The overall results are smaller sized backups and reduce backup time. Further, if a Tape Drive can detect a compressed data/file then it should not expand already compressed data. This feature however is not available in all Tape Drives.