The Backup Compression feature was introduced with SQL Server 2008. We can reduce our backup sizes by selecting this option when backing up. However, while reducing our backup size, it will increase CPU usage.
With Resource Governor, you can limit the cpu resource that the backup user uses. You can find details in the article “How To Limit a User’s Resource Usage Using Resource Governor“.
Beyond Backup, you can compress your tables in SQL Server. Of course we need to decide according to need. Because usually, the disk is a more easily expandable resource than cpu. You may want to read the article “SQL Server Compression“.
Compressed Backup Sizes
With the help of the query below, we can see the normal backup size and the compressed backup size.
|
1 2 3 |
SELECT name,backup_size/1024/1024 AS BackupSizeMB, compressed_backup_size/1024/1024 AS CompressedBackupSizeMB FROM msdb..backupset where name is not null; |
Set Backup Compression Default
If you want to enable Backup Compression on the instance, we can do this with the help of the following script.
|
1 2 3 4 |
EXEC sys.sp_configure 'backup compression default', '1' GO RECONFIGURE WITH OVERRIDE GO |
While backing up, we can select Compress backup as below.
Backup Compression with TSQL
You can add the WITH COMPRESSION statement to the end of the query as follows.
|
1 2 |
BACKUP DATABASE [AdventureWorks2014Yeni] TO DISK = N'C:\Backup\AdventureWorks2014Yeni.bak' WITH COMPRESSION, STATS = 10 |
If you receive an error like this when you execute the script above, you are trying to backup with compression on the backup you backed up without compression.
Msg 3098, Level 16, State 2, Line 1
The backup cannot be performed because ‘COMPRESSION’ was requested after the media was formatted with an incompatible structure. To append to this media set, either omit ‘COMPRESSION’ or specify ‘NO_COMPRESSION’. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
