SQL Server backup compression is a feature in SQL Server that enables you to compress backup files to save disk space and reduce backup times.
When you enable backup compression, SQL Server compresses the data in the backup file using a compression algorithm. This can significantly reduce the size of the backup file and make it faster to transfer or store on disk.
Software compression: If your storage device doesn't support hardware compression, you can use SQL Server's built-in software compression feature. You can enable software compression by specifying WITH COMPRESSION option when you create a backup.
When you create a compressed backup, SQL Server uses more CPU resources to compress the backup data, which can increase CPU usage during the backup process.
However, the disk I/O requirements are reduced due to the smaller backup file size. Therefore, you may need to monitor the CPU usage during the backup process to ensure that the compression process does not negatively impact the performance of other processes running on the server. Overall, backup compression can be a useful feature to reduce the storage space required for backups and reduce backup times, especially for large databases or when backups need to be transferred over a network.
Using SQL Server backup compression can have both advantages and disadvantages, depending on your specific needs and environment. Here are some of the pros and cons of compressing a backup:
Pros:
Reduced backup size: Backup compression can significantly reduce the size of backup files, which can be especially beneficial for large databases. This can result in less disk space required for backups and faster backup and restore times.
Faster backup and restore times: Smaller backup files can be backed up and restored faster, which can reduce the overall time required for backup and restore operations.
Reduced network bandwidth: If you need to transfer backups over a network, compressing them can reduce the amount of network bandwidth required, which can be particularly useful for remote backups or disaster recovery scenarios.
Cons:
Increased CPU usage: Compression requires additional CPU resources, which can increase CPU usage during the backup process. This may cause performance issues on the server, particularly if the server is already under heavy load.
Potential for slower backup times: While backup compression can reduce backup size and time, it can also slow down backup times, particularly if the server's CPU resources are already taxed.
Potential for slower restore times: Compressed backups may take longer to restore than uncompressed backups, particularly if the restore process is CPU-bound or if the server does not have enough memory available.
Check For Compression
You can check if backup compression is turned on by examining the backup file properties or the backup log. Here are two methods to check if backup compression is turned on:
Method 1: Check backup file properties
Right-click on the backup file and select Properties.
Select the General tab.
If the "Compression" field displays "Compressed", then backup compression was used.
Method 2: Check backup log
Open the SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
Navigate to the "Management" folder in the Object Explorer.
Right-click on the "Maintenance Plans" or "Maintenance Plan Wizard" and select "View History".
In the history window, locate the backup job you want to check and click on the hyperlink in the "Log File" column.
In the log file, search for the following message: "Backup compression successfully processed". If you see this message, then backup compression was used.
Method 3: Check backup log - T-SQL:
Open SQL Server Management Studio and connect to your SQL Server instance.
Open a new query window and execute the following T-SQL statement:
EXEC sp_configure 'backup compression default'Method 3: Check SSMS Properties
Backup With Compression
To backup a database with compression in SQL Server, you can use the BACKUP DATABASE statement with the WITH COMPRESSION option. Here's an example of how to do this:
BACKUP DATABASE [database_name]
TO DISK = 'D:\backup_file.bak' WITH COMPRESSION;
In this example, replace [database_name] with the name of the database you want to back up, and 'D:\backup_file.bak' with the path and file name where you want to save the backup file.
When you include the WITH COMPRESSION option, SQL Server will compress the backup file using the default backup compression setting. If the default backup compression is not set, SQL Server will not compress the backup file.
You can also specify the compression level using the COMPRESSION_LEVEL parameter. The COMPRESSION_LEVEL parameter accepts a value between 1 and 3, where 1 is the least compressed and 3 is the most compressed.
Here's an example:
BACKUP DATABASE [database_name]
TO DISK = 'D:\backup_file.bak' WITH COMPRESSION, COMPRESSION_LEVEL = 3;
In this example, the backup file will be compressed using the highest compression level.
Note that backup compression requires additional CPU resources, which can increase CPU usage during the backup process. Additionally, compressed backups may take longer to restore than uncompressed backups, particularly if the restore process is CPU-bound or if the server does not have enough memory available.
Do I have to Uncompress a backup to restore it
No, you do not need to Uncompress a compressed backup file to restore it. SQL Server can restore a compressed backup file just like an uncompressed backup file.
When restoring a compressed backup file, you can use the same T-SQL RESTORE DATABASE statement or the Restore Database Wizard in SQL Server Management Studio (SSMS) that you would use to restore an uncompressed backup file.
Here's an example of how to restore a compressed backup file using T-SQL:
USE master;
GO
RESTORE DATABASE [database_name]
FROM DISK = 'D:\backup_file.bak'WITH REPLACE, NORECOVERY;
GO
In this example, replace [database_name] with the name of the database you want to restore, and 'D:\backup_file.bak' with the path and file name of the compressed backup file.
Note that when restoring a compressed backup file, SQL Server will automatically decompress the data during the restore process. However, restoring a compressed backup file may take longer than restoring an uncompressed backup file, particularly if the restore process is CPU-bound or if the server does not have enough memory available.
Spacing Savings
The amount of space you can save with a compressed backup file will depend on the compressibility of the data in your database. Typically, compressing a backup file can reduce its size by up to 50-60%.
SQL Server provides a stored procedure named sp_estimate_data_compression_savings that you can use to estimate the space savings for a specific table or index. However, this stored procedure cannot estimate the space savings for a compressed backup file.
sp_estimate_data_compression_savings is a stored procedure in SQL Server that you can use to estimate the space savings of compressing a specific table or index in your database. Here's how you can use it:
Open SQL Server Management Studio (SSMS) and connect to your database server.
Open a new query window and execute the following T-SQL statement: sql
USE [your_database_name]; EXEC sp_estimate_data_compression_savings @schema_name = 'dbo', @object_name = 'your_table_name', @index_id = NULL, @partition_number = NULL, @data_compression = 'PAGE';
In this statement, replace [your_database_name] with the name of your database, and 'your_table_name' with the name of the table you want to estimate the space savings for. The @index_id and @partition_number parameters are optional and can be used to estimate the space savings for a specific index or partition within the table.
If you want to estimate the space savings for the entire table, you can leave these parameters as NULL.The @data_compression parameter specifies the type of compression you want to use. You can choose from three compression types: NONE, ROW, or PAGE. In most cases, PAGE compression provides the best space savings without significantly impacting performance.
After executing the statement, the stored procedure will return an estimate of the space savings for the specified table or index. The output will include the following columns:
object_name: The name of the table or index.
index_id: The ID of the index (NULL for a table).
partition_number: The number of the partition (NULL for a table or a non-partitioned index).
reserved_kb: The amount of space currently reserved for the table or index, in kilobytes (KB).
data_kb: The amount of data currently stored in the table or index, in KB.
index_size_kb: The size of the index, in KB.
unused_kb: The amount of unused space in the table or index, in KB.
compressed_size_kb: The estimated size of the table or index after compression, in KB.
compression_ratio: The estimated compression ratio for the table or index (expressed as a percentage).