How to Backup SQL Server Database with Compression (Script)

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.

• Compress Database Backup in SQL Server for Once or Always
• One Easier Way for SQL Database Backup Compression

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'
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 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:

BACKUP DATABASE databasename TO DISK = 'filepath' WITH COMPRESSION

Then click Execute to start.

★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.

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:

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

into:

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

*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):

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.

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.

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.

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

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.

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.

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.

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.

Summary

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.