MSSQL Backup Compression | Space-Saving Best Practice

What is MSSQL Backup Compression and When to Use It

Regular backups of MSSQL databases allow you to restore data in case of accidental loss, ensures data security. In order to store as many backups as possible, we must plan disk space usage wisely.

There are many ways to save space. You can use Maintenance Plan in SQL Server to delete old backup files automatically, or choose a simpler, and commonly used way which will be introduced in this article, MSSQL Backup Compression.

Backup Compression is a practical inbuilt option that allows you to compress full, differential, transaction log backups while creating. It was introduced in SQL Server 2008 and has been available on Enterprise, Standard, and Developer editions of subsequent versions. But please note that it is off by default.

The Advantages and Restrictions of Backup Compression

Should you enable MSSQL Backup Compression option? To answer this question, first let’s take a look at the SQL Server Backup Compression disadvantages and advantages.

• Advantages: Enable MSSQL Backup Compression you can compress backups while creating with one click. Compressed backup files are smaller and take up less space than uncompressed ones, so you can store more backup files in the same location, which is easier to manage and faster to move.
• Disadvantages: Backup Compression is not available on SQL Server Express and versions before 2008. The Backup Compression option will increase CPU usage. And compressed backups cannot be saved in the same media set as uncompressed backups.

So here is the conclusion: whether to use Backup Compression or not depends on your actual needs. If you are not using the Express version, and you do not have enough disk space to keep the number of backups you want, then Backup Compression can be very helpful for you.

Below I will provide a way to estimate the size of your database backups, you can use it to determine if you need Backup Compression.

How to Estimate SQL Server Backup Size before Creating

1. Launch SSMS and connect to your instance.

2. Expand Databases. Right-click the name of the database you want to back up and select New Query. Input the following statement:

EXEC sp_spaceused @updateusage = 'true'

3. Then click Execute on toolbar.

In Results, the value in Reserved column is considered the approximate size of the backup.

How to Use MSSQL Backup Compression Option

Actually, it is very easy to create compressed backups using the inbuilt MSSQL Backup Compression option. In this part, I will show you 1 way to turn on the Backup Compression default, 2 ways to use Backup Compression option for only once, and one alternative to create compressed backups with higher compression level.

How to Enable MSSQL Backup Compression Default

1. Launch SSMS and connect to your instance.

2. Right-click the instance name in Object Explorer, and select Properties.

3. Turn to Database Settings page, check Compress backup option.

4. Click OK to save the change.

Then you can create backups of SQL databases in normal ways, and they will all be compressed. If you don’t want to compress the backups anymore, just uncheck Compress backup option in Properties.

How to Enable MSSQL Backup Compression Once

If you only want to create MSSQL backup database for only once, then turn on and turn off the backup compression default may be laborious for you. Here I provide you with 2 ways that are more suitable for you.

✦ Way 1. Enable Backup Compression Option on SSMS GUI

1. Launch SSMS and connect to your instance.

2. Expand Databases. Right-click the name of the database you want to backup, and select Tasks > Back Up…

3. Select the Backup type and specify a Destination path.

4. Turn to Options page, select Compress backup in Compression section.

Note: The compressed backups cannot be in the same media set as uncompressed backups.

✦ Way 2. Add WITH COMPRESSION in T-SQL Backup Statement

1. Launch SSMS and connect to your instance.

2. Click New Query on the Standard bar, and input the following statement:

BACKUP DATABASE databasename TO DISK = 'filepath\filename.bak' WITH COMPRESSION

Note:
databasename: the name of the database you want to back up.
filepath: where you want to store the backup.
filename.bak: name this backup with the suffix .bak.

3. Click Execute on the toolbar to start backup.

If you want to create compressed backups of all databases in MSSQL, you can refer to the 3rd way in scripts to backup database with compression

Create Compressed SQL Database Backup with Higher Compression Level

MSSQL Backup Compression is not available on SQL Server Express and versions before 2008, and you cannot select compression level on SQL Server. That’s why I will introduce you AOMEI Centralized Backupper Database Edition, which provides you 2 compression levels to further save disk space.

Except for compress backups, as a professional software, Centralized Backupper Database allows you to centrally control all the laptops, workstations, and servers within LAN from one computer. You can remotely back up multiple databases on another computer within LAN, and monitor the Network, CPU, Memory, and Disk Usage to avoid failure.

When restoring, you can choose to restore the backup to the local server, or to another server of the controlled client computers remotely. For enterprises, it can promote efficiency and reduce management costs.

Here’s a 30-day free trial for you:

To properly use Centralized Backupper Database, especially if you want to manage multiple computers within LAN, you need to go to the Computers page first. Click Install client program on the upper bar to download the Agent on client computers manually or remotely.

For those computers which download client program manually, you need to request control over them for further operations.

Click Computers > Uncontrolled Computers, select single or multiple client computers and click Request Control on the upper bar.

A permission request window will pop up on their computers, once all permissions are received, you can manage them at any time.

✦ How to Create Compressed SQL Backups with Higher Compression Level

Launch AOMEI Centralized Backupper Database Edition.

Click Tasks > New Task and select SQL Server Backup to create a new backup task.

In pop-up window, you can change the task name by moving your cursor to the task name and click it.

Then follow the 3 steps beneath the task name.

Step 1, click Add Computers to add the controlled client computer you want to back up with.

Step 2, click Add to select single or multiple SQL instances and databases you want to back up.

Step 3, select a location as the destination path. You can click Add Storage to add a share or NAS path as a storage end.

◆ Click Settings to configure the SQL Server backup compression.

As you can see, it provides 2 compression levels. The higher the level is, the smaller the backup files are.

◆ You can also enable encryption and email notification for this backup task in Settings.

◆ Click Schedule next to Settings, you can select to run this task Once, Daily, Weekly, or Monthly. Each option allows you to select specific days and start time. You can also change it after created.

◆ Click Advanced in Schedule Settings window, you can choose to perform Full or Differential Backup.

Now you can click Start Backup to select Only create the task, or Create and execute the task.

Once created, the tasks will be listed in Tasks separately for further edit or restore. When restoring, it will list all the backups, and distinguish them by the backup type and dates in their names.

Follow the simple 2 steps, and you choose to restore the database to original location, or to another computer within LAN. You can refer to SQL Server restore from compressed backup

How to Check if SQL Server Backup is Compressed

Compressed backup files are not specially identified. Therefore, when you restore SQL Server database from a backup file, you may not know whether the backup is compressed or not. Here I will provide you a way to check it.

1. Launch SSMS and connect to your instance.

2. Click New Query on the Standard bar, and input the following statements:

RESTORE HEADERONLY FROM DISK = 'filepath\filename.bak'

3. Then click Execute on toolbar.

In Results, if the value of the Compressed column is 1, then the backup is compressed. If it is 0, then the backup is uncompressed.

Summary

In this article, I briefly introduced MSSQL Backup Compression, and analyzed when to use it. Provided with 1 way to estimate SQL Server backup size before creating, 1 way to turn on Backup Compression default, 2 ways to enable Backup Compression once, 1 way to check if the backup is compressed, and 1 alternative to create backups with higher compression level.

In the area of SQL Server backup, AOMEI Cyber Backup is an expert. Except for providing 2 compression levels, you can also use it to perform full or differential backup, schedule automatic backup tasks, and enable backup encryption, email notifications.

For enterprises, it allows a specialist to remotely back up databases on another computer within LAN, or restore database to another computer, which can efficiently reduce manual errors.