What Happens With Data Compression + Backup Compression

Jess Pomfret tests what happens when you enable backup compression for databases with already-compressed tables in SQL Server:

What happens if I use data compression and backup compression, do I get double compression?

This is a great question, and without diving too deeply into how backup compression works I’m going to do a simple experiment on the WideWorldImporters database.  I’ve restored this database to my local SQL Server 2016 instance and I’m simply going to back it up several times under different conditions.

After restoring the database it’s about 3GB in size, so our testing will be on a reasonably small database.  It would be interesting to see how the results change as the database size increases, perhaps a future blog post.

Click through for the answer.

Related Posts

Creating Timelines With dbatools

Marcin Gminski shows how to pull SQL Agent and backup history out of SQL Server and display it as a visual history timeline: Currently, the output from the following commands is supported: Get-DbaAgentJobHistory Get-DbaBackupHistory You will run the above commands as you would normally do but pipe the output to ConvertTo-DbaTimeline, the same way as you […]

Read More

Finding The Last Database Restore Time

Lori Brown shows how you can see the last time a particular database was restored: I have a client that uses a lot of disconnected log shipping on a few servers.  They do this because they want to have a copy of their database that is actually hosted by software vendors for reporting purposes.  So, […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031