Best Way to Backup SQL Server Database With Compression

SQL Server database backups can take considerable storage space, especially when the database grows over time and multiple backup copies are retained. A compressed backup can help reduce the size of the resulting backup file, making it easier to store and transfer.

For organisations that create backups regularly, compression can also be included as part of an automated backup routine. In this guide, we will explain how to backup SQL Server database with compression using different approaches.

Benefits of SQL Server Database Backup Compression

There are several advantages of compressing SQL Server backups. Some of them are:

• Reduce Backup File Size: Compression makes SQL Server backup file smaller, so they require less storage space.
• Save Backup Storage: Smaller backup files allow you to retain more backup copies without increasing storage capacity.
• Speeds Up Backup Transfers: A smaller file contains less data to transfer, which can be helpful when moving backups to NAS, SFTP or cloud storage.
• Make Offsite Backup Easier: Compressed backups are more convenient to copy or transfer to a remote location for additional protection.
• Helps Manage Large Databases: As SQL Server databases grow, compression can help keep the resulting backup files more manageable.

Can SQL Server Backup be Compressed?

Yes. SQL Server supports compression for database backups. When compression is enabled, SQL Server processes the backup data to create a smaller backup file instead of storing it in its uncompressed form.

You can enable compression while creating a backup through SSMS or use the WITH COMPRESSION option in a T-SQL command.

You can also use a professional solution to create automate SQL Server backup with compression. It can automatically take backup SQL Server database with compression when you enable once.

Real World Scenario

While researching SQL Server database backup compression, we found a user on Reddit who noticed an unexpected situation during a compressed full backup. The user had a database backup that temporarily approached 100 GB, but after the compression process completed, the final .bak file was around 35 GB. This raised a practical concern about whether compressed backups actually save storage of a large amount of space is needed while the backup is being created.

Let’s move ahead and discuss all methods in detail.

How to Backup SQL Server Database with Compression Using SSMS?

SQL Server Management Studio provides a graphical way to create a compressed database backup. You do not need to write a SQL command to do it.

1. Launch SQL Server Management Studio and connect to the SQL Server instance containing the database you want to backup.
2. From Object Explorer, choose Databases.
3. Right-click the required database and navigate to Tasks > Backup. This opens the Backup Database window.
4. Under Backup type, select the type of backup you want to create.
5. Choose Full for a complete copy of the database. You can also select Differential or Transaction Log.
6. Under the Destination section, select the location where you want to save the backup.
7. Open the Media Options section and locate the backup compression setting.
8. Select the option to compress the backup.
9. Review the backup configuration and click OK.

SQL Server will create the database backup and apply compression during the process. Once the process is complete, you can check the destination folder to see the generated backup file.

How to Create a Compressed SQL Server Backup Using T-SQL?

T-SQL provides a quicker option when you prefer to create backups from a query window or use commands as part of an administrative process.

Use the following command to backup SQL Server database with compression:

BACKUP DATABASE [SalesDB]
TO DISK = N'D:\Backups\SalesDB_Full.bak'
WITH COMPRESSION,
     STATS = 10;

Replace saleDB with the name of your database and change the backup path according to your environment.

Here, WITH COMPRESSION tells SQL Server to compress the backup while creating it.

How to Reduce SQL Server Backup Size Automatically?

Creating a compressed backup once is useful, but the database continues to change after the backup is completed. If you want every scheduled backup to use compression, the compression setting needs to be included in the recurring backup process.

Instead of manually enabling compression each time, you can configure a backup job to create compressed copies automatically at the selected intervals. This keeps the backup process consistent while helping control the amount of storage used by repeated backup files.

A dedicated backup software can make this process easier by keeping the compression setting, schedule, retention and destination together in one backup job.

This is where Prapl SQL Backup Tool can be useful to backup SQL Server database with compression. Just enter the login details of your SQL Server database, select the required storage destination, enable compression and set a schedule. After the job is configured, each scheduled backup follows the same compression setting without requiring manually create and compress every backup.

Steps to Automate Compress SQL Server Database Backups

1. Run the tool on your system and click on + New Backup.

2. Choose SQL Server as a database engine and press Continue.

3. Enter SQL Server login details and choose the database that you want to back up.

4. Select the location where you want to save backup files.

5. Choose the backup type, set schedule and enable compression. After that click the Save and Run Now button.

Now the software will create a compressed backup of SQL Server database automatically on every run.

Conclusion

SQL Server database backup compression provide a straightforward way to reduce the size of database backup files. In this post, we have explained three different ways to backup SQL Server database with compression. You can manually compress backups from SQL Server Management Studio or T-SQL command.

If SQL Server database needs to be backup regularly, automation can remove the need for repeated manual operations. The software can combine schedule backup with compression, retention and multiple storage destination gives you a more convenient way to manage backup files.