Impact of Data Compression on Backup Compression with SQL Server 2008

By:   |   Comments (4)   |   Related: > Compression


Problem

SQL Server 2008 introduced data compression and backup compression. Can you explain the impact of data compression when used with backup compression?

Solution

In SQL Server 2008 a database backup can be compressed to save the amount of disk space required for the backup.  It can be performed on databases which use data compression and databases where data compression is not enabled.

In order to make things simple and for easier explanation, this example is carried out on a database which has a single table with millions of records in it. The properties window of the table is displayed in the image below. This tip is covered in 4 steps to ensure clarity with respect to size of the database backups without backup compression and data compression, backup compression without data compression and then look at backup compression for the two types of data compression.

BackupCompressionImpact1

Step 1 - Normal backup with no data compression

As the first step we shall take a backup of the database without backup compression. Remember the table does not have data compression enabled. The below code is used to perform the database backup.

BACKUP DATABASE LearnSQLWithBru
TO DISK N'O:\BackupDirectory\BackupWithoutDataCompression.BAK' WITH NOFORMAT, 
NAME  N'LearnSQLWithBru-Full Database Backup Without Data Compression'NO_COMPRESSION 
GO 

The details of the database backup file is shown in the image below, note that the backup size is just about the same size as the database since the backup is not a compressed backup.

BackupCompressionImpact2

Step 2 - Compressed backup with no data compression

In the next step we shall look at the size of the backup file by compressing the backup. The below code is used to perform the database backup with compression.

BACKUP DATABASE LearnSQLWithBru
TO DISK N'O:\BackupDirectory\CompressedBackupWithoutDataCompression.BAK' WITH NOFORMAT, 
NAME  N'LearnSQLWithBru-Full Database Compressed Backup Without Data Compression'COMPRESSION 
GO 

The details of the new database backup file is shown in the image below (highlighted with a blue background), note that the backup size is reduced to a third of the previous backup, which is common in the case of compressed backups in SQL Server 2008.

BackupCompressionImpact3

Step 3a - Normal backup with row-level compression

We now move to the next step in which we shall first compress the table using row level compression. After that we shall perform a normal database backup (without compression) and a compressed database backup. The table "dbo.DemoCompressionTable" needs to be compressed with row level data compression, which we are going to perform using the below command.

ALTER TABLE dbo.DemoCompressionTable
REBUILD 
WITH (DATA_COMPRESSION ROW);

After the table is compressed, first we shall perform a normal backup of the database. The script below will perform a normal backup of the current database.

BACKUP DATABASE LearnSQLWithBru
TO DISK N'O:\BackupDirectory\NormalBackupWithRowCompression.BAK' WITH NOFORMAT, 
NAME  N'LearnSQLWithBru-Full DB Normal Backup With Row Compression'NO_COMPRESSION 
GO 

The size of this backup file (performed in this step) is smaller than the normal backup file of the same database that we performed in Step 1. The main reason the backup file size is reduced is the decrease in the number of data pages required to store the table due to row level compression applied on the table.

BackupCompressionImpact4

Step 3b - Compressed backup with row-level compression

In this step we shall perform a compressed backup of the database and compare the results with those from the step without backup compression (Step 3a). The below script is used to perform a backup of the database with backup compression.

BACKUP DATABASE LearnSQLWithBru
TO DISK N'O:\BackupDirectory\CompressedBackupWithRowCompression.BAK' WITH NOFORMAT, 
NAME  N'LearnSQLWithBru-Full DB Compressed Backup With Row Compression'COMPRESSION 
GO 

After successfully taking the backup, the below image displays the file size of the new backup along with the old ones.  As you can see we got a little more compression.

BackupCompressionImpact5

What we can conclude with this step is backup compression (for a database with data compression) does not always compresses the backup file with the same ratio when compared to a database without data compression.  If backup compression always compresses the backup with the same ratio, then in the current step we should have a backup file around 400 MB (approx), but we do not because the data is already compressed.


Step 4a - Normal backup with page-level compression

In this next step we shall first compress the table using page level compression.  Next we will perform the same steps as we did in the previous steps, a normal database backup (without compression) and a compressed database backup. The following script will compress the table with page level data compression.

ALTER TABLE dbo.DemoCompressionTable
REBUILD 
WITH (DATA_COMPRESSION PAGE);

After the table is compressed, first we shall perform a normal backup of the database. The script below will perform a normal backup of the current database.

BACKUP DATABASE LearnSQLWithBru
TO DISK N'O:\BackupDirectory\NormalBackupWithPageCompression.BAK' WITH NOFORMAT, 
NAME  N'LearnSQLWithBru-Full DB Normal Backup With Page Compression'NO_COMPRESSION 
GO 

The size of this backup file (performed in this step) is drastically reduced when compared to normal backup file size of the same database that we performed in step 1. The main reason the backup file size is reduced drastically is because page level compression reduces the number of data pages required to store the table more than with row level compression.   So from this we can see that page level compression get better compression than row level compression.

BackupCompressionImpact6

Step 4b - Compressed backup with page-level compression

We repeat the steps to perform a compressed backup of the database and compare the results with those from the step without backup compression (Step 4a). The below script is used to perform a backup of the database with backup compression.

BACKUP DATABASE LearnSQLWithBru
TO DISK N'O:\BackupDirectory\CompressedBackupWithPageCompression.BAK' WITH NOFORMAT, 
NAME  N'LearnSQLWithBru-Full DB Compressed Backup With Page Compression'COMPRESSION 
GO 

Below is the list of backup files after the backup.

BackupCompressionImpact7

After this backup and comparing backup file sizes with those of the previous backups, backup compression does not significantly compress the backups when the database is already enabled with page level data compression.

Conclusion

Backup compression reduces the backup size by approximately 30% when compared to a regular non-compressed backup of the same database when page level compression is enabled. When row level compression is enabled, it reduces somewhat better, approximately 60 %.

Some points to be remembered

  • The results shown in this article might differ with another database with same size due to differences in the datatypes of the columns and the data occupancy in the records.
  • When page level compression is enabled on one table and row level compression is enabled on another table, the backup compression will be proportional in size with respect to the amount of data that exists in those tables.
  • On a partitioned table, one partition can be compressed using row level compression and another using page level compression, i.e both types of compression can be enabled on a single table.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Bru Medishetty Bru Medishetty focuses on Performance Monitoring and Tuning as well as planning and implementing Disaster Recovery solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Sunday, April 11, 2021 - 5:58:38 PM - Eric Back To Top (88509)
Great article, but I was wondering if you noticed any benefits in your tests regarding overall backup timing. For example when you enabled page compression along with backup compression did the overall time you performed your backup decrease or increase compared to your baseline.

Wednesday, February 24, 2021 - 8:18:56 AM - Terry Grignon Back To Top (88288)
What I'd be interested in finding out would be the time factor. Did row or page compression decrease the time it took to do the backup. I deal with a lot of dbs and reducing that time is my current goal.

Friday, February 12, 2016 - 11:52:18 AM - NGCooper23 Back To Top (40669)

 

Not sure it is fair to say that the ROW or PAGE level compression are the best ways to perform backups.

Standard Backup compression already reduces the size of the backup by ~70%

When you add in ROW Compression to the Compressed Backup you save an addition ~2%.

Only ROW Compression – ~19% compared to the normal uncompressed backup size.

When you add PAGE compression you actual have a larger backup than the Standard compression of ~-4%.

Only PAGE Compression – ~50% compared to the normal uncompressed backup size.

 

In your scenario the best backup compression results were the CompressedBackupWithRowCompression.BAK. Not sure on the extra resources needed to save that addition ~2% justifying the end result but we are only talking size here. Would be great to see the resource usage differences for each of these to determine the best backup meathod.

 

 


Monday, July 14, 2014 - 6:51:37 PM - Sri Back To Top (32698)

Nice article!!!















get free sql tips
agree to terms