Wednesday , May 29 2024

Backup Compression in SQL Server

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.

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.

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.

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.


About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *