How to Backup SQL Server Database with Compression (Script)

If you find your SQL Server database backups are taking more and more disk space, and every time you back up it just takes too long, you can try using script to create compressed SQL database backups.


By Crystal / Updated on June 30, 2023

Share this: instagram reddit

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 moving them around can be time-consuming. Therefore, you may wonder if there’s any way to create compressed database 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 it, you can either enable Backup Compression option with GUI, or use backup script with compression command.

In this article I will show you how to backup database with compression using SQL Server script step by step.

SQL Server logo

Compress Database Backup in SQL Server for Once or Always

Basically, if you want to always backup database SQL Server with compression command, you can just 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'

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

Use "Backup Database" Script with Compression in SQL Server

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:


Then click Execute to start.

Backup Command with Compression

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\
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
FETCH NEXT FROM db_cursor INTO @name
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 ( 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 Easier Way for SQL Database Backup Compression

Backup database SQL Server script with compression 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.

Otherwise, 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: You can change the level of Compression. The higher the compression level, the smaller the backup file is.
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.
Backup Schedule: You can schedule an automatic backup task to back up your databases for settled periods.
Centralized Backup Functions: Besides SQL databases, it also supports files, partitions, disks and OS backup.

First, you can download the 30-day free trial to experience its functionality (also get an up-to 60% off discount here):

Download Free Trial All Windows Servers and PCs
Secure Download

How to Backup SQL Database with Compression Using Centralized Backupper

Download and install AOMEI Centralized Backupper Database on your computer. 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 BackupIn 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 Compressionyou 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


In this article I introduced how to backup database SQL Server script with compression, sincerely hope this could help you.

And of course, if you don’t like the tedious SQL script to backup database with compression, 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.

As an enterprise backup solution, it can manage backups all computers within LAN effortlessly, which is definitely worth a try.

Crystal · Editor
Crystal is an editor from AOMEI Technology. She mainly writes articles about virtual machine. She is a positive young lady likes to share articles with peolpe. Off work she loves travelling and cooking which is wonderful for life.