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