Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2008 Backup Compression


By:   |   Last Updated: 2010-03-15   |   Comments (18)   |   Related Tips: More > 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.


Last Updated: 2010-03-15


next webcast button


next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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


Monday, April 09, 2018 - 11:07:51 AM - Sheik Ahmed SM Back To Top

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 03, 2015 - 6:27:50 AM - Chris Montora Back To Top

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

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
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

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

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

Regards,

Sudhir Tiwari

 


Tuesday, September 03, 2013 - 10:41:29 PM - Cao Duy Tam Back To Top

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 @[email protected]+ convert(varchar(10), getdate(), 120)

set @FullPath= 'G:\backup\DB\'[email protected] 

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\'[email protected]+'.rar  '[email protected]

exec master..xp_cmdshell @cmdlinetxt

 

 

 


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

 

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
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

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

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

 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

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

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

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

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

Does this tip apply to all editions of SQL 2008?

 

 Andy


Learn more about SQL Server tools