For an instructor lead, in-depth look at learning SQL click below.
In today’s digital world, making the best use of storage is a priority for many developers and businesses. So, when it comes to SQL server database backup, the size of the backup file matters. The larger the file, the longer the time it takes to create and restore. SQL Server provides an excellent feature – Backup Compression – that helps in reducing the backup size, thereby saving disk space and allowing for faster backup and restore times.
What is Backup Compression?
Backup compression is a feature introduced in SQL Server 2008 Enterprise and later extended to other versions, which significantly reduces the size of database backup files. It does so by compressing the data inside the backup file, leading to savings in storage costs and also improving the performance of backup and restore operations.
How to Use Backup Compression?
To use backup compression, you need to modify the BACKUP DATABASE command to include the WITH COMPRESSION option. Here’s a simple example:
|
1 2 3 4 5 |
BACKUP DATABASE YourDatabaseName TO DISK = 'D:\YourDatabaseName.bak' WITH COMPRESSION; |
In the above SQL, ‘YourDatabaseName’ is the name of the database you want to backup and ‘D:\YourDatabaseName.bak’ is the location where you want to save the backup file.
Setting Backup Compression as Default
If you intend to use backup compression for all your future backups, you can set it as a server default using the following command:
|
1 2 3 4 |
EXEC sys.sp_configure N'backup compression default', N'1' RECONFIGURE WITH OVERRIDE |
Monitoring Backup Compression
SQL Server allows users to monitor the effectiveness of the compression using the compressed_backup_size and backup_size columns from the backupset history. Here’s how you can do that:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT backupset.database_name, backupset.backup_finish_date, backupset.expiration_date, (backupset.compressed_backup_size / 1048576) AS CompSizeMB, (backupset.backup_size / 1048576) AS BackupSizeMB, ((100 * (backupset.backup_size - backupset.compressed_backup_size)) / backupset.backup_size) AS PercentSaved FROM msdb.dbo.backupset; |
This query is extremely useful for tracking backup compression over time.
There are several other options and caveats to consider with backup compression in SQL Server. However, used smartly, it can prove to be a powerful tool in reducing your backup sizes and improving your backup and restore times.