Let’s not bury the lede: the one word for fast backups is COMPRESSION.

“Backup compression is supported on SQL Server editions: Enterprise, Standard, and Developer. Every edition of SQL Server 2008 and later can restore a compressed backup.”

– Docs.Microsoft.com

Before we show how to use backup compression, we’ll talk compression itself: the good and the not-so-good.

Why should I bother with backup compression?

Simply speaking, slow backups mean slow restores. `Nuff said.

Slow backups mean slow restores.

-Sean McCown, for years and years now

We can agree that fast backups (and therefore restores) are good. So what are the elements of a database backup that prevents a fast backup? Like any good DBA, I’m going to blame the network…and back it up with numbers.

The network is usually the slowest piece of technology in your systems. It’s slower than the CPU, of course. It’s also slower than memory, and even slower than SSD access!

Backup files must be transmitted across space to the disk. A big file will take a longer time to pass through the network; a small file will take a shorter time. 

But let’s say you’re backing up locally*, and network isn’t an issue. The system still has to write those files to disk, and the disk is the second-slowest piece of technology. Yes, even solid-state!

The takeaway is this: the biggest bang for our buck is to make our backup files smaller. By definition, that’s what compression does!

*Oh! The horror!! I hope you’re copying those backups to a second location, friend. Which…will also require network time.

Backup compression sounds great. What’s the catch?

In short, you save on disk and network, but it costs CPU.

“By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations.”

– Docs.Microsoft.com

DBAs generally try to schedule big backups during off-hours – like evenings and weekends – so the CPU cost won’t affect anything critical. However, if your shop has limited (or no) off-hours, or if CPU is a major issue, you should definitely conduct some safe tests to see if backup compression will be too impactful.

Any other issues with compressing backups?

Microsoft lists the following three restrictions on backup compression: 

“Compressed and uncompressed backups cannot co-exist in a media set.” 

That seems fair.  

(If you’re not familiar with the idea of a “media set”, here’s a quick primer: 

• If you back up to one new file, that’s one media set. 
• If you back up to ten files, those ten files are one media set. 
• If you back up to a file, then back up again to that same file (without FORMAT, INIT), that’s one media set with two backups in it.)

“Previous versions of SQL Server cannot read compressed backups.” 

Earlier versions of SQL Server can’t read compressed backups. But then, earlier versions of SQL Server can’t read backups from later version of SQL. E.g., if I take an uncompressed backup on SQL Server 2012, my SQL Server 2008 instance won’t be able to read it anyway. 

“NTbackups cannot share a tape with compressed SQL Server backups.”  

I’m tempted to be snarky – oh yes, I’ll be sure to watch for that on my [checks notes] tape backups – but I know some of you still use tape backups as a secondary or archival solution. Mad respect, old schoolers!

Just know that, if you’re backing up to tape (or copying backups to tape), you can’t mix NTbackups with compressed backups.

How do I use backup compression?

You can enable backup compressions at the individual level, at the instance level, or at any granularity using Minion Backup!

Individual backup compression

To enable compression for an individual backup (in a BACKUP statement), add the clause WITH COMPRESSION. For example: 

BACKUP DATABASE [MyDB]
TO DISK = '\\backups\MyDB_20210331_FULL.bak'
WITH COMPRESSION;

Instance-level backup compression

You can enable compression for the entire instance – though you’ll have to decide whether that’s a good idea, now that you’ve read about the gains and the costs.

To enable compression for an entire instance, set the sp_configure setting “backup compression default” to 1 and run RECONFIGURE.

Backup compression in Minion Backup

To enable backup compression for all backups in Minion Backup, run:

UPDATE Minion.BackupTuningThresholds SET [Compression] = 1;

You can also use the Minion.BackupTuningThresholds table to enable backup compression only for specific backup types, or for specific databases, or both.  

Here’s a quick example: our Minion.BackupTuningThresholds table has only one row – the default “MinionDefault/All” row that applies to all backups. If we only want FULL backups to use compression, we would run the following:

-- Set compression OFF for all backups.
UPDATE Minion.BackupTuningThresholds 
SET Compression = 0;

-- Specify compression ON for full backups only.
INSERT INTO Minion.BackupTuningThresholds  ([DBName], [BackupType], [SpaceType], [ThresholdMeasure], [ThresholdValue], [NumberOfFiles], [Buffercount], [MaxTransferSize], [Compression], [BlockSize], [IsActive], [Comment])
SELECT 'MinionDefault' AS DBName
, 'Full' AS BackupType
, SpaceType
,  ThresholdMeasure
,  ThresholdValue
,  NumberOfFiles
,  [Buffercount]
,  [MaxTransferSize]
,  1 as [Compression]
,  [BlockSize]
,  IsActive
,  'Enable compression for FULL backups.' AS Comment
FROM Minion.BackupTuningThresholds  
WHERE ID = 1;

With these two rows in place, all full backups will use the settings in the new “MinionDefault/Full” row*. All other backups will use the default row settings. Of course, we could really get specific with more rows – for example, specify compression on for certain databases, and off for certain databases’ backup types, and so on. Minion Backup is pretty flexible.

*For more information about MB settings – defaults and specific overrides – see “Architecture Overview – Minion Backup“.

Backup compression, in short

Here are the main things to remember about backup compression in SQL Server:

• Slow backups mean slow restores, and that’s bad.
• Network and disk are the slowest pieces of your systems.
• Backup compression makes backup files smaller. Smaller files mean less network use and less disk time!
• Compression comes with a higher CPU cost. That’s probably fine in off-hours, and when your system isn’t limited on CPU.
• You can enable compression for one backup, or for the whole SQL Server instance.
• You can customize backup compression very easily with Minion Backup!

Want to work with us? Contact us here!