SQL Server – Database Backup Compression

Introduction

Yearns ago, we deployed Quest LiteSpeed to get backup compression.

We had to use 3rd party software to get backup compression.

But, that was SQL Server v2005.

 

Built-In

Beginning with v2008 backup compression has been built in.

 

Metadata

msdb.dbo.backupset

Wanted to get a feeling as to savings, and here is what the code looks like.

 

code


declare @numberofDays int
declare @dateCurrent datetime
declare @dateGoBack datetime

set @dateCurrent = getdate()
set @numberofDays = 7
set @dateGoBack = dateadd(day, @numberofDays *-1, @dateCurrent)
 

select top 3000
		 
		  tblBS.[database_name]

		, [startTime]
			= convert(varchar(30), tblBS.[backup_start_date], 100)

		, [finishTime]
			= convert(varchar(30), tblBS.[backup_finish_date], 100)

		, [durationInMins]
			= datediff(minute
						,tblBS.[backup_start_date]
						, tblBS.[backup_finish_date]
					  ) 

		, [backupSizeInMB]
			 = ( tblBS.[backup_size] / ( 1024 * 1024 ) )

		, [compressBackupSizeInMB] 
			= ( tblBS.[compressed_backup_size] / ( 1024 * 1024 ) )

		, [ratio]
			= cast
				(
					( tblBS.[compressed_backup_size]
						/ tblBS.[backup_size]
                    ) 
					as decimal(10, 2)
				)

		, tblBS.[type]

		, tblBS.[flags]

		, [hasBackupChecksumUsingSpecificColumn]
			= case ( tblBS.[has_backup_checksums])
					when 0 then 'No'
					else 'Yes'
			  end
	
		, [hasBackupChecksumUsingColumnFlags]
			= case ( tblBS.[flags] & (2 ^16) ) 
					when 16 then 'Yes'
					else 'No'
			  end		
		--, tblBS.*

from   msdb.[dbo].backupset tblBS

where  tblBS.[type] in ( 'D' )

and    tblBS.[backup_start_date] >= @dateGoBack 

and     tblBS.[database_name] in ( 'xmaster', 'ppsivr')

order by
		  tblBS.[database_name] asc
		, tblBS.[backup_set_id] asc

 

 

Output

backupupset-20161210-1241am

 

Explanation

  1. With MS SQL Server
    • Default is set to Backup Compression
      • With backup compression our file size is 25%
    • Requested Backup Checksum
      • Getting backup checksum
  2. With 3rd party software
    • No space savings
    • No Checksum validation
  3. Because we are testing the 3rd party tool, we are logging two full backups each day
    • At 6:30 PM, SQL Server backup
    • At 8 PM, the 3rd party backup
  4. Time Taken
    • Backing up our 8 GB database is taken about 3 minutes with compression
    • Without compression it is taking 1 minute

 

msdb.dbo.backupset – Column – Flags

One of the more interesting columns in the msdb.dbo.backupset table is the Flags column.

Though it is now deprecated, it holds some useful nuggets.

BTW, the information it contains is now exposed in individual bit columns.

Nevertheless, here is what each bit represents

 Flag Bits Bit
 has_bulk_logged_data 0
 is_snapshot  1
 is_readonly  2
 is_single_user  3
 has_backup_checksums  4
 is_damaged
 begins_log_chain
 has_incomplete_metadata
 is_force_offline
 is_copy_only

 

 

Using bit arithmetic, has shown in the SQL code above, one is able to decipher the Flags column.

 

Checksum

Configuration

Trace Flag

  1. 3023
    • If you are using backup applications or utilities that do not expose these options, you can enable the CHECKSUM option by using Trace Flag 3023 on the instance of Microsoft SQL Server.
      If Trace Flag 3023 is turned on, the CHECKSUM option is automatically enabled for the BACKUP command.
      If you do this, you do not have to rewrite all the existing backup scripts.

Query


dbcc tracestatus with no_infomsgs

Instance

Starting with v2014, MSFT added an option through  sp_configure that can be used to setup the default checksum setting.

The option name is “backup checksum default”.

Query


exec sp_configure 'show advanced options', 1
reconfigure with override


exec sp_configure 'backup checksum default'

 

Instrumentation

To measure the impact of various backup offerings ( native versus third party) and options, we can use use the metrics listed below:

Perfmon

Courtesy of MSFT

  1. Windows I/O performance counters, such as the physical-disk counters
  2. The Device Throughput Bytes/sec counter of the SQLServer:Backup Device object
  3. The Backup/Restore Throughput/sec counter of the SQLServer:Databases object

 

 

References

Metadata

  1. Transact-SQL Reference (Database Engine) System Tables (Transact-SQL)  Backup and Restore Tables (Transact-SQL) backupset
    Link

 

MSDN

  1. Database Engine Features and Tasks>  Database Features  > BackUp and Restore of SQL Server Databases > Backup Compression
    Link

 

MSFT – Support

  1. How to enable the CHECKSUM option if backup utilities do not expose the option
    Link

 

Bloggers

  1. Erin Stellato
    • Trending Database Growth From Backups
      Link
  2. Xu James
    • trace flag for backup and restore operation
      Link
  3. Scott Caldwell, Remote DBA Experts
    • UNDOCUMENTED TRACE FLAGS: INSIDE THE RESTORE PROCESS
      Link
  4. Jugal Shah
    • T-SQL Script to Check the Native Compressed backup file size and Backup Compression Ratio
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s