By Alison / Last Updated December 20, 2021

Is SQL Server Database Backup Compression Achievable?

As using, the SQL Server databases can grow very big, so as the backups. They take a lot disk spaces, and every time backing up databases or move around can be time-consuming. Therefore, you may wonder if there’s any way to create compressed backups.

The answer is YES. MSSQL Backup Compression is a powerful feature on SQL Server 2008 and later versions, but it’s off by default. To make use of this feature, you can either enable Backup Compression option with GUI, or use a backup script with compression command.

In this article I will show you the specific steps of how to back up database in SQL Server using script with compression commands.

Using Script to Create Compressed SQL Database Backups

Basically, if you want to always backup database with compression, you can just use commands to turn on the default option of backup compression, then all backups will be compressed until you turn it off again.

If you want to use compress option only when making specific backups, then you can add WITH COMPRESSION to the statement when you need. Scripts are provided in the following.

Commands to Turn on The Backup Compression Default

If you just want to turn on the Backup Compression default, you can launch SQL Server Management Studio (SSMS) and click New Query, input the following commands:

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Type N’1’ is to turn on the Backup Compression default, and N’0’ is off. Once it’s on, every time you backup they will all be compressed, until you change it again. In this way you can change the default as you like.

Script to Backup Single or Few SQL Databases with Compression

Launch SQL Server Management Studio (SSMS) and connect to your instance, click New Query on the Standard bar, or right-click your database name and select New Query. Input the following statement:

BACKUP DATABASE databasename TO DISK = 'filepath' WITH COMPRESSION

Then click Execute to start.

Backup Command with Compression

★Tips:
As for the Filepath, you need to type the path and name of your file.  For example: D:\backup\database.bak
If you want to back up more databases, you can repeat this statement with changed database names. And if you do not want to compress the backup, you can just delete WITH COMPRESSION.

Script to Backup All SQL Databases with Compression

If you want to back up all SQL databases of one instance with compression, launch SSMS and connect as usual, click New Query and input the following statements:

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'filepath'  --add a path, e.g. D:\backup\
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
with COMPRESSION
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Then click Execute on the toolbar to start.

Script to backup all databases with compression

Besides, there are some other statements that may be helpful to you:

The statement: 'WHERE name NOT IN ('master','model','msdb','tempdb')' is used for exclude the databases you do not want to back up. If you want to back up more, you can change it as need. For example:

WHERE name NOT IN ('tempdb')

And if you want to involve database name in the filename, you can change the statement: 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

into:

SELECT @fileDate = CONVERT ( NVARCHAR ( 20 ), GETDATE (), 112 ) + '_' +
REPLACE ( CONVERT ( NVARCHAR ( 20 ), GETDATE (), 108 ), ':' , '' )

If you want to display the progress stats of your backup, you can add 'STATS=' after 'WITH COMPRESSION'. For example:  

WITH COMPRESSION, STATS=5 (change the number as you like)

*If you have further needs, you can also refer to SQL Server backup script.

One Simpler Way to Enable SQL Database Backup Compression

Using SQL scripts to back up databases is not that friendly for novices, it’s complex and tedious, and any tiny mistake may fail this backup. It’s better that you are acquaintance with T-SQL, and know how to change it according to your requirements.

But if you cannot, I suggest you try simpler ways, for example, software with visual management. It’s more convenient and most of all, you can change the settings as you like with simple clicks. I think AOMEI Centralized Backupper Database is a great solution for following reasons:

✦ Change Compression Level: In settings of AOMEI Centralized Backupper Database Edition you can change the level of Compression. The higher it is, the smaller the files are, the quicker when restore, and of course, takes longer to back up.

✦ Back up/Transfer Across Within LAN: Unlike SSMS, Centralized Backupper Database can help you accomplish multiple backups of all desktop computers, laptops, workstations and servers within LAN, and transfer them to any computer as you like. 

✦ Backup Schedule: You can schedule an automatic backup task to back up your databases for settled periods in Centralized Backupper Database. And you can set a task before you start to back up, start it with a single click when you need it.

✦ Centralized Backup Functions: Except for SQL Server database backup, there are other functions in this software such as File Backup, System Backup, Disk Backup, Partition Backup, Basic Sync, Real-Time Sync, and Mirror Sync. All of them can be achieved by simplified steps.

How to Enable Backup Compression Using Centralized Backupper Database

First, download and install AOMEI Centralized Backupper Database on your computer. Here’s a 30-day free trial:

Download Free Trial Win PCs & Servers
Secure Download

Launch the software, and install Agent package by clicking Computers Install Client Program. 

Install Client Program

Before you started, you need to request control of the computers you want to backup. Click Computers Uncontrolled Computers, select the correct IP and right-click it to Request Control.

Request Control

When your requests are accepted by all the clients, you can then start to create centralized backup task.

1. Click Tasks New Task SQL Server Backup. In the prompt window you can see 3 steps.

SQL Server Backup

2. Click Add Computers in step1, then select the correct IP of your client computer.

Add Computers

3. Step2 is of most importance, which makes this software powerful and unique. You can select multiple instances and databases to backup once in this step.

Select Instances and Databases

4. Select a path for storage in Step3, fill in the blank of Share or NAS Path and Display Name, the added paths will be saved for your future use.

Select Path

5. Click Settings Compression, you can choose the level of compression. The higher the level is, the smaller the backup file will be, but it will take longer time. For most times, Normal is enough. Then click OK > Start Backup > Create and Execute Tasks.

Backup Settings Compression

6. Except for that, you can also set automatic backup schedule of different day/week/month period in Schedule beneath Settings. Besides, you can choose full or differential backup in Advanced.

Schedule Settings

Summary

In this article I introduced some SQL scripts to back up database with compression, sincerely hope this could help you.

And of course, if you don’t like the tedious script, you can also choose a simpler and more convenient way like AOMEI Centralized Backupper Database Edition. Using this software you can not only back up multiple instances and databases once, but also choose different types of backup as you like.

Besides, it supports Windows 10/8.1/8/7/Vista/XP, and allows backup on SQL Server 2005/2008/2012/2014/2016/2017/2019. As an enterprise backup solution, it can manage backups all computers within LAN effortlessly, which is definitely worth a try.