Faster Backups with SQL Server Backup Compression

Database compression is a feature that Microsoft introduced in SQL Server 2008, but many people still don’t understand or regularly use the feature. The power of this feature is to both speed up the backup process, and to save disk space. The speed benefit is a result of reduced disk activity as you stream the compressed backup file directly to disk. You can use all your available CPU cycles to perform the backup straight to disk, and since the smaller file is saved to disk you will probably reduce any potential delay as you write the backup file to disk. The other obvious benefit is the resulting backup file can also be much smaller. In my experience, I’ve seen compression between 20-50 percent, but you will need to test your backup to determine your real space savings based on the contents of the database and how well your data can be compressed.

Perform a test backup without compression and see how long it takes and how large the resulting BAK file is for your sample database. Then backup the same database using compression to see if it is faster and how much smaller the BAK file is when it is complete.

To create compressed database backups, all you need to do is add the COMPRESSION option to the BACKUP command as shown below:

BACKUP DATABASE MyDatabase
 TO DISK = 'H:\BACKUPS\MyDatabase.BAK'
 WITH FORMAT, COMPRESSION;

Compression has been a feature available with SQL Server 2017 in both the Standard and Enterprise editions:

Feature	Enterprise	Standard	Web	Express with Advanced Services	Express
Server core support 1	Yes	Yes	Yes	Yes	Yes
Log shipping	Yes	Yes	Yes	No	No
Database mirroring	Yes	Yes Full safety only	Witness only	Witness only	Witness only
Backup compression	Yes	Yes	No	No	No
Database snapshot	Yes	Yes	Yes	Yes	Yes
Always On failover cluster instances2	Yes	Yes	No	No	No
Always On availability groups3	Yes	No	No	No	No
Basic availability groups 4	No	Yes	No	No	No
Online page and file restore	Yes	No	No	No	No
Online indexing	Yes	No	No	No	No
Resumable online index rebuilds	Yes	No	No	No	No
Online schema change	Yes	No	No	No	No
Fast recovery	Yes	No	No	No	No
Mirrored backups	Yes	No	No	No	No
Hot add memory and CPU	Yes	No	No	No	No
Database recovery advisor	Yes	Yes	Yes	Yes	Yes
Encrypted backup	Yes	Yes	No	No	No
Hybrid backup to Windows Azure (backup to URL)	Yes	Yes	No	No	No

You can read more about compression here.

Script to Remove SQL Server Compression

This script will identify the tables and indexes that use compression and generate a script to remove the compression. This is useful, for example, if you are attempting to move a database from Enterprise Edition to Standard Edition. Standard Edition doesn’t currently support encryption.

SELECT DISTINCT ‘ALTER TABLE [' + 
                SCHEMA_NAME(schema_id) + '].[' + NAME + '] 
                REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);’  
FROM sys.partitions p join sys.objects o on p.object_id = o.object_id 
WHERE o.TYPE = ‘u’ and data_compression_desc != ‘NONE’ 
UNION  
SELECT ‘ALTER INDEX ALL ON [' + 
                SCHEMA_NAME(schema_id) + '].[' + NAME + '] 
                REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);’ 
FROM sys.partitions p join sys.objects o on p.object_id = o.object_id 
WHERE o.TYPE = ‘u’ and data_compression_desc != ‘NONE’

The Top 5 Sql Server Features You Aren’t Using And Why

The features in an product like SQL Server are so numerous that you may not even be aware of some of them, much less using them all in your database environment. Thomas LaRock lists some of the features that he likes (hint: they are only available in the Enterprise Edition), but you may not be using.

Here is my list of the top SQL Server features that most of us are simply not using.

1. RESOURCE GOVERNOR

2. DATABASE SNAPSHOTS

3. POLICY BASED MANAGEMENT

4. SQL AUDIT

5. NATIVE ROW AND/OR PAGE COMPRESSION

That’s right, every one of those features is only in the Enterprise Edition.