SQL Server 2008 Backup Compression

By:   |   Updated: 2010-03-15   |   Comments (18)   |   Related: > Compression


Problem

Databases keep getting larger, and the time we are given for backups to complete seldom keeps pace. With SQL 2008 you have the ability to compress your database backups, saving disk space but more importantly saving time.

Solution

Enabling database backups to use compression can be done in one of two ways. You can either configure all backups for the instance to be compressed (it is disabled by default), or you can issue the WITH COMPRESSION command for a specific database backup.

To enabling backup compression at the server level, right-click on the instance name inside of server explorer in SSMS to bring up the Server Properties screen. Go to the 'Database Settings' and you should see the following:

With SQL 2008 you have the ability to compress your database backups, saving disk space but more importantly saving time.

In my opinion, this setting is buried on a very busy page, and is quite easy to miss. Fortunately you have a second option to configure this setting by using some T-SQL: 

EXEC sys.sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

If you did not want to enable compression for the server instance, you also have the option of enabling backup compression for individual backups:

BACKUP DATABASE [AdventureWorks] 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.BACONMSSQL\Backup\AW_compress.bak' 
WITH COMPRESSION
GO

The actual amount of compression you will get depends primarily on the type of data you have in your database. If you are storing .jpg files in your database, for example, then do not expect much benefits from compression. Another factor to consider is encryption. If your data has been encrypted previously, then your compression ratio will be close to 1:1. Lastly you need to consider if your database already has data compression enabled. If you are already compressing data then you will not see much benefit from backup compression.

To calculate the compression ratio for your backups you can utilize two columns in the backupset table inside of the msdb database. This simple T-SQL statement will show you the ratio for all of the backups currently stored in the backupset table.

SELECT backup_size/compressed_backup_size 
FROM msdb..backupset
GO
Next Steps
  • Enable backup compression for your instance or for individual backups by following the steps listed above.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2010-03-15

Comments For This Article




Wednesday, September 26, 2018 - 11:09:09 PM - Abhishek Back To Top (77738)

 Can we further compress backups while using native backups beyong the point sql engine compresses the backup using the compression setting ?

Monday, April 9, 2018 - 11:07:51 AM - Sheik Ahmed SM Back To Top (75647)

Hi Chris,

your point is valid. The main purpose of taking backup is when ever required. we want to restore.

 

disadvantages of rar backup

In rar backup we can't restore when ever need.

for compressing large data rar takes more time.

 

 

Thanks,

Sheikvara

 

Tuesday, November 3, 2015 - 6:27:50 AM - Chris Montora Back To Top (39009)

The only real downsize of the compressed backup with SQL is that the file cannot be optimally compressed further with other utilities. For example a "normal" database (no jpgs, etc, just basic data types) with a backup of 17 GB:

- SQL compression backup is 2.9GB and archiving as RAR (for example) you get a 2.6 GB file

- SQL normal backup of 17 GB archived directly as RAR result in a 1.6GB file

So it's an important difference, more than 60% in my case (and I think with other utilites like ZIP can be even bigger) if you need to store those backups on a long term.

Monday, May 25, 2015 - 1:15:52 PM - VARMA Back To Top (37274)

Thank you for your valuable explanation

I just want to know,

How to check the compression ratio before going to perform Backup with Compression option?

Can we set the ratio(value) to backup compression?

Thanks&Regards 

VARMA

 

 

 

Wednesday, February 18, 2015 - 4:54:18 AM - Mohammad Shahnawaz Back To Top (36272) 
declare@DateTimeSuffix varchar(20)declare@BackupLoction varchar(1000)declare@fullNameWithLocation varchar(1000)declare@dbname varchar(127)select@dbname ='Adventureworks'select@BackupLoction ='c:\' select @DateTimeSuffix = convert(varchar(10),GETDATE(),112) + '_' + replace(CONVERT(varchar(10),getdate(),108),':','') select @fullNameWithLocation = @BackupLoction + @dbname + '_' + @DateTimeSuffix + '.Bak'BACKUPDATABASE Adventureworks TODISK=@fullNameWithLocation WITH INIT, COMPRESSION

Monday, February 10, 2014 - 1:23:18 PM - KIRAN Back To Top (29394)

I have one Backup file say MYDB.Bak in E:\backup folder . How do I check, whether this backup is compressed backup or without compression ? 

If it is compressed backup then how can i change it to normal backup?

Wednesday, January 22, 2014 - 8:01:22 AM - Sudhir Kumar Tiwari Back To Top (28172)

Please tell me which will take more time in compress backup and normal backup.

Regards,

Sudhir Tiwari

 

Tuesday, September 3, 2013 - 10:41:29 PM - Cao Duy Tam Back To Top (26600)

My Name Tam from Viet Nam.

Can you use scrip backup and compress database in SQL server 2008 as below :

declare @BackupFileName nvarchar(300)

declare @FullPath  nvarchar(300)

declare @cmdlinetxt nvarchar(300)

declare @DataBase1 varchar(80)

 

set @DataBase1='NAME DATABASE'

 

set @BackupFileName=@DataBase1+ convert(varchar(10), getdate(), 120)

set @FullPath= 'G:\backup\DB\'+@BackupFileName 

BACKUP DATABASE @DataBase1 TO DISK= @FullPath WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT

set @cmdlinetxt='C:\Progra~1\WinRAR\RAR  a -df  G:\backup\DB\'+@BackupFileName+'.rar  '+@FullPath

exec master..xp_cmdshell @cmdlinetxt

 

 

 

Friday, June 14, 2013 - 3:17:00 AM - berto Back To Top (25433)

 

If you need compressed SQL Server backup you can use SQLgzip.

Free fully functional version (32-bit) supports compression and TSM support. Additionally you can specify compression rate as standard gzip or zip protocol are used.

 

Here you have small tutorial from command line:

 

SQLgzip v.1.4.0 - Backup or Restore SQL server database with compression
Copyright (C) 2009-2013 A. Revo

Usage:
 SQLgzip <command> [<database>] <file> ["<with_options>"] [<key_options>]

Where: <command> - RESTORE, BACKUP, "RESTORE LOG", "BACKUP LOG", COPYONLY,
                   EXTRACT BACKUPFILE|FILELISTONLY|INFOONLY
       <with options> - any valid backup/restore WITH options
       <key_options> SERVER=name[;copyname] - required for cluster and instance
                     MAXTHREADS=n - defaults: #CPU/2 or 1 for gz/zip archive
                     COMPRESSION=n - 1-9; default 1 (fastest)
                     LOGINTIMEOUT=nn in sec., 0-disabled
                     FOREACHBACKUP=comamnd to be executed after each backup
                     OPTFILE=path to file with above options
        (All options can be specified in SQLgzip.opt file.)

Use .GZ extension for Gzip format, .ZIP - for zip64, any other - special
Use special format for fast multithreading compression.
For COPYONLY specify two database names and WITH option for destination.

Examples:
 SQLgzip Backup master "f:\mssql\master.bar" "WITH DIFFERENTIAL" MAXTHREADS=3
 SQLgzip EXTRACT INFOONLY f:\mssql\mydbname.bak.gz
 SQLgzip EXTRACT BACKUPFILE f:\mssql\mydbname.bak.gz f:\mssql\master.bak
 SQLgzip RESTORE mydbname f:\mssql\mydbname.bak.zip "WITH MOVE 'DataFile' TO 'f:\mssql\data\userdb.mdf', MOVE 'LogFile' TO 'G:\mssql\datalog\userdb.ldf'" SERVER=MYSERVER\SQL1
 SQLgzip COPYONLY Mydb MydbCopy "WITH MOVE 'DataFile' TO 'f:\mssql\data\userdb.mdf', MOVE 'LogFile' TO 'G:\mssql\datalog\userdb.ldf'" SERVER=MYSERVER\MYCOPY

Wednesday, November 16, 2011 - 2:53:07 PM - John Fox Back To Top (15138)
We have been using compressed backups for some time, and are very careful to schedule them around periods of database inactivity because of the significant increase in CPU usage on the server itself. Of course, most sites should already be doing this as database backups already have some impact on the server. The compression is often 5:1 or higher, and is well worth it in terms of reduced disk usage with multiple copies and offsite storage usage.

Wednesday, March 16, 2011 - 11:16:05 AM - Shawn Melton Back To Top (13233)

That I know of it is not configurable. I'm not to sure why you would want to.

Wednesday, March 16, 2011 - 10:06:43 AM - Joe Back To Top (13230)

With SQL Server's backup compression, is there a way to set or control the compression ratio or is that totally automatic?  I have been searching for any information on this topic but have not found any.  I have found plenty on how to calculate what this compression ratio is for any give backup set but nothing on whether or not I can set that ratio.

Thanks much,

Joe

Tuesday, March 16, 2010 - 8:49:09 AM - tosc Back To Top (5059)

 Why curious? The elapsed time required to make compressed backups can be significantly less because the size of compressed backups are smaller and there are fewer writes to the backup media. However, overall CPU is significantly higher because of the compression overhead. Microsoft warns that backup compression can significantly increase CPU utilization and that it may have an adverse impact on concurrent operations.

 I wish you a nice day,

tosc


Tuesday, March 16, 2010 - 8:32:38 AM - timothyrcullen Back To Top (5058)

Has anyone evaluated the time difference between performing a regular backup versus a backup that is compressed?  Just curious...

Monday, March 15, 2010 - 12:12:55 PM - wshawnmelton Back To Top (5054)

http://www.microsoft.com/sqlserver/2008/en/us/R2-editions.aspx

http://www.microsoft.com/sqlserver/2008/en/us/compression.aspx

 Effective in SQL Server 2008 R2 you will be able to do backup compression in Standard Edition.

Monday, March 15, 2010 - 11:43:20 AM - tosc Back To Top (5053)

Hi,

a simple script for a little bit more information for compression:

SELECT bs.backup_size AS 'Backup Size in KB',
		 bs.compressed_backup_size AS 'Compressed Backup Size in KB',
		  CONVERT (NUMERIC (20,3), (CONVERT (FLOAT, bs.backup_size) /
		  CONVERT (FLOAT, bs.compressed_backup_size))) 'Compression Ratio',
		  DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) 'Backup Elapsed Time (sec)'
		FROM msdb.dbo.backupset bs 
			INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id     
		WHERE DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) > 0
		AND bs.backup_size > 0
ORDER BY
  bs.backup_finish_date DESC

I wish you a nice day,

tosc


Monday, March 15, 2010 - 11:19:56 AM - aprato Back To Top (5052)

http://technet.microsoft.com/en-us/library/bb964719.aspx<

 " Creating compressed backups is supported only in SQL Server 2008 Enterprise and later, but beginning in SQL Server 2008, every edition can restore a compressed backup."

Monday, March 15, 2010 - 5:29:10 AM - namraja Back To Top (5051)

Does this tip apply to all editions of SQL 2008?

 

 Andy














get free sql tips
agree to terms