How to Effectively Use Backup Compression in SQL Server

SQL Server’s backup compression allows you to save disk space by compressing backup files. Many SQL Server database administrators forget to enable this feature, thereby allowing backup files to occupy a lot of disk space. In this article, we will learn how to quickly enable the feature “Compression” on all your SQL Servers.

Scan using registered servers:

Compress Backup In SQL ServerFrom your SQL Server management studio, click on the View and then select Registered Servers. Create a new server group and add all your SQL Server instances to this newly created group. To add sever to the group, right click on the server group and select the option “New Server Registration”. A form will now open and under the tab “General”, mention your server name and click on “Save”. You can also test the connectivity to the server before saving it to the group. After adding all servers to the group, execute this script from a new query window. This script will connect to each server in the Registered server group and identifies the SQL Server edition. If the edition is Enterprise, the script will then identify if the feature “Backup compression” is enabled. If that feature is not enabled, the script will display that server name on the result window.

declare @check as int
set @check = (select charindex('enterprise',@@version))
declare @check2 as int
set @check2 = cast((SELECT value FROM sys.configurations WHERE name = 'backup compression default') as int)
if @check <> 0 and @check2 = 0 
begin
Select @@servername + ' is using Enterprise edition but is not using Backup compression'
end

Enable backup compression

Executing the below script will enable the backup compression on a SQL server instance.

declare @check as int
set @check = (select charindex('enterprise',@@version))
declare @check2 as int
set @check2 = cast((SELECT value FROM sys.configurations WHERE name = 'backup compression default') as int)
if @check <> 0 and @check2 = 0 
begin
EXEC sp_configure 'backup compression default', 1 ; 
RECONFIGURE WITH OVERRIDE ; 
Select @@servername + ' is now using Backup compression'
end

Less space:

SQL Server Backup CompressionThough the amount of data is same, a backup file that is compressed occupies less space on a disk than a backup file that is not compressed. Backup compression has following benefits

  1. Time taken to complete the backup reduces significantly
  2. Disk input-output is less

Using the backupset table from the MSDB database, compare compressed_backup_size and backup_size columns, we can easily identify the compression ratio.

The size of a compressed backup file depends on how much the data in the database can be compressed. The SQL Server database engine uses a special algorithm and allocates a percentage of database’s size for the backup file. During the backup process, if more space is needed for the backup file, SQL Server database engine grows it automatically.

If the SQL Server database is corrupt when you perform the backup, then the backup will fail for most of the time. So it is very important to use a powerful sql recovery tool to repair the corrupt database before backup.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix pst and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.