As we all know, backup of SQL database helps to reduce the risk of data loss. Besides, the compression of SQL backups that contains restrictions, performance trade-off of compressing backups, configuration of backup compression, and compression ratio. In addition, Backup compression will support SQL Server 2017 versions, i.e., Enterprise, Standard, and Developer. Any version of SQL Server 2008 and above can restore compressed backup. Here, we are going to describe the backup compression in SQL Server.
Benefits of Backup Compression
There is a need to create backup compression on SQL server because the compressed backup is smaller than uncompressed backup of same data. Typically, backup compression requires less I/O devices and therefore the speed of backup increases significantly.
Impact of Compressing Backups with SQL Server
By default, the compression has increased CPU usage, and a new CPU consumed by compression process may impact on concurrent operation. Therefore, users have to create low-priority backups whose CPU utilization is limited byResource Governor. To obtain the backup I/O performance, users can isolate I/O backup to or from any device by evaluating the following actions:
- Windows disk I/O performance counters, i.e., physical-disk counters
- In SQL Server, the device Throughput Bytes/sec counter
- The Backup/Restore Throughput/sec counter of SQL Server
Design Compression Ratio of Compressed Backup
To compute the compression ratio of backups, use different values for backup in backup_size and compressed_backup_size columns of backupset history table, as listed below:
backup_size: compressed_backup_size
For this, you can use the following Transact-SQL statement:
The compression ratio of compressing backup depends on entire data that has been compressed. The number of reasons may impact the compression ratio obtained. Some of the factors include:
- Data type Character data will compresse different data types.
- The consistency of data among rows on page Basically, if a page involves several rows where field contains same value, important compression might occur for that specific value. Otherwise, for database that consists random data or contains a single large row per page, a compressed backup will tend to be almost as large as uncompressed backup.
- Whether encrypted data Encrypted data can be compressed less than equal to unencrypted data. If transparent data encryption is designed to encrypt complete database, compressing backups may not reduce large size in any way.
- Whether compressed database If a database is compressed, the backup compression may not reduce size so much, if at all.
Allocation of Disk Space for Backup Files
For backup compression, the size of final backup file that depends on how much the data is compressible, which is unknown before the backup copy operation finishes. Thus, the default backup compression, and Database Engine uses pre-allocation algorithm for a backup file. This algorithm that pre-allocate a specific percentage of the database size for backup file. If you need more space during the backup operation, Database Engine can grow that file. If a final size is smaller than allocated space, at the end of backup operation, Database Engine shrinks the size of files to actual size of backup.
The backup file will only grow as needed to reach its maximum size, use the trace flag 3042. This trace may cause backup operation to bypass backup compression pre-allocation algorithm by default. It is useful if you want to save space by allocating the actual size for compressed backup. But, this trace flag can have an impact on performance (increases in duration of backup operation).
Methods to Compressed Backup in SQL Server
- Use SQL Server Management Studio
- Under the Object Explorer, right-click on the server and choose Properties
- Then, click on Database settings node
- In the Backup and restore, Compress backup will show the current setting of backups compression default option. By default, it will determine the server-level for compressed backups, as listed below:
- If Compressed backup box is blank, then a new backup is uncompressed by default.
- If you check the Compress backup box, a new backup are compressed by default.
If you are also become a member of sysadmin or server admin fixed server, then you can change the default setting by selecting Compress backup box.
- Use T-SQL (Transactional SQL)
- To view or configure a backup compression default option
- First, connect to Database Engine.
- Under the Standard bar, click on the New Query.
- Copy & paste the below example into query window and then, choose Execute.
- To configure backup compression default option
- Connect to Database Engine
- In the Standard bar, click the New Query
- Copy & paste the following example into query screen and press Execute.
Time to Conclude
In this write-up, we have discussed a manual procedure to backup compression in SQL Server. Along with this, we have explained the impact of compressing backups in SQL Server. Make sure follow each and every step very carefully.
EmoticonEmoticon