For an instructor lead, in-depth look at learning SQL click below.
Managing your data storage effectively is a crucial aspect of any enterprise. In this post, we will explore SQL Server Backup Compression, an important, yet often overlooked feature that can help us store our backups more efficiently. Backup Compression allows us to reduce the size of our backup files, leading to greater storage space and potentially improved I/O performance.
Understanding SQL Server Backup Compression
First introduced in SQL Server 2008, Backup Compression offers a great solution to conserve disk space by reducing the backup size. Taking a backup with compression might often run faster than taking an uncompressed backup. The primary cost of compressing backups is that it requires additional CPU cycles.
Creating a Compressed Backup
|
1 2 3 4 5 6 |
-- Syntax for SQL Server and Azure SQL Database BACKUP DATABASE AdventureWorks TO DISK = 'Z:\SQLServerBackups\AdventureWorks.bak' WITH COMPRESSION; |
This script backs up the AdventureWorks database to a disk file and uses the WITH COMPRESSION option to compress the backup. It’s a simple, yet effective solution to reduce storage usage.
Checking compression ratios
The following query gives the compression ratio for each database backup stored in the backup history in the msdb database:
|
1 2 3 4 5 |
SELECT backup_size/compressed_backup_size AS 'Compression Ratio' FROM msdb..backupset WHERE database_name = 'AdventureWorks'; |
Configuring Server-level Backup Compression Settings
By default, SQL Server does not compress backups, but you can configure the server instance to compress backup, by default. This is achieved by using the two options of the sp_configure stored procedure, ‘backup compression default’ and ‘backup compression default’, to set the configuration value.
|
1 2 3 4 5 6 |
EXEC sp_configure 'show advanced options', '1'; RECONFIGURE; EXEC sp_configure 'backup compression default', '1'; RECONFIGURE; |
Now, by default every backup taken on this server will be compressed regardless of the WITH COMPRESSION or WITH NO_COMPRESSION clause.
Conclusion
Backup Compression in SQL Server offers a simple yet efficient way to manage your storage space. It might require additional processing time due to the compression process, but the trade-off in storage costs might make it a worthwhile option for many businesses.