BACKUP compression in SQL Server 2008

SQL Server 2008 is released with inbuilt compression for backups. Until the release of SQL Server 2005, compression was only available via third party backup software such as  SQL LiteSpeed, SQLZip, etc.

This article demonstrates how to take Full, Differential and Transactional log backups with compression, without compression and how to enable compression as a default.

Let us create a database “MyDB” as shown below.

USE [master] GO  

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB') 

DROP DATABASE [MyDB]

GO

USE [master] 

GO  

CREATE DATABASE [MyDB] ON   

PRIMARY ( NAME = N'MyDB_Data', FILENAME = N'D:\BPA SQL Project\SQL BP\TestDB\MyDB_Data.mdf' ,SIZE = 2176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  

LOG ON ( NAME = N'MyDB_log',FILENAME = N'D:\BPA SQL Project\SQL BP\TestDB\MyDB_log.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)  

GO 

ALTER DATABASE [MyDB] SET RECOVERY FULL  

GO

Now let us create a table “MyTable” in the database “MyDB” as shown below

USE [MyDB]

GO

IF  EXISTS (SELECT * FROM sys.objects 

 WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') 

 AND type in (N'U'))

DROP TABLE [dbo].[MyTable]

GO

USE [MyDB]

GO

SET ANSI_NULLS ON

GO 

SET QUOTED_IDENTIFIER ON

GO 

SET ANSI_PADDING ON

GO 

CREATE TABLE [dbo].[MyTable](

          [id] [int] NULL,

          [name] [char](100) NULL

) ON [PRIMARY] 

GO

SET ANSI_PADDING OFF

Go

Let’s add 10000 rows of data to the table “MyTable” as shown below.

USE [MyDB] GO   declare @myid int set @myid=1 while @myid<=1000 begin insert into MyTable select @myid, 'A'+convert(varchar(10),@myid) set @myid =@myid +1 end 

you can use the system stored procedure sp_spaceused to see how large my database is.
EXECUTE sp_spaceused

Select the data using the following T-SQL command. [Refer Fig 1.0]

use MyDB go Select * from MyTable go 

 
Fig 1.0

Create a folder, D:\Backup, as shown below. [Refer Fig 1.1]

 
Fig 1.1

Now let us take a full backup as shown below. [Refer Fig 1.2]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full.bak' with init

 
Fig 1.2

Let’s add some more data [1000 rows] to the table “MyTable” in the database “MyDB” as shown below.

USE [MyDB] GO   declare @myid int set @myid=1 while @myid<=1000 begin insert into MyTable select @myid, 'A'+convert(varchar(10),@myid) set @myid =@myid +1 end 

Now let us a take a transaction log backup, as shown below. [Refer Fig 1.3]

Backup log  MyDB to disk ='d:\Backup\MyDB_TLog_1.bak' with init

 
Fig 1.3

By default, SQL Server does not compress the backups. We can compress the backups in two different ways.

a.      Change the default behavior of SQL Server to compress all of the backups.

b.      Add an optional keyword “With COMPRESSION” in the backup clause.

The database MyDB and the Full backup, Transactional log backup that we took were without compression. That is the default SQL Server behavior.

Now let’s take a full backup of the database with compression as shown below. [Refer Fig 1.4]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full2.bak' with COMPRESSION

 
Fig 1.4

From figure 1.4, you can see that the size of MyDB_Full2.bak is much smaller when compared to MyDB_Full.Bak and MyDB_Tlog_1.bak.

Add some more data [1000 rows] to the table “MyTable” in the database “MyDB” as shown below.

USE [MyDB] GO   declare @myid int set @myid=1 while @myid<=1000 begin insert into MyTable select @myid, 'A'+convert(varchar(10),@myid) set @myid =@myid +1 end 

Now let’s take a transaction log backup as shown below. [Refer Fig 1.5]

Backup log  MyDB to disk ='d:\Backup\MyDB_TLog_new.bak' with COMPRESSION

 
Fig 1.5

In figure 1.5, you can see that the size of MyDB_Tlog_new.bak is much smaller when compared to MyDB_Tlog_1.bak.

Let’s take a differential backup without compression and compare it with the differential backup with compression.

Execute the following commands as shown below. [Refer Fig 1.6]

backup database MyDB to disk ='d:\Backup\MyDB_Diff.bak' with differential   backup database MyDB to disk ='d:\Backup\MyDB_Diff2.bak' with differential, COMPRESSION 

 
Fig 1.6

Fig 1.6 shows the compression ratio between MyDB_Diff.bak and MyDB_Diff2.bak.

Let’s change the default behavior of SQL Server from uncompressed backup to compressed. This can be done using the SP_CONGIFURE command.

Execute the command as shown below.

USE master GO EXEC sp_configure 'backup compression default', '1' GO RECONFIGURE WITH OVERRIDE GO 

Now let’s take a full backup of the MyDB database, without the optional keyword “WITH COMPRESSION”. [Refer Fig 1.7]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full3.bak'

 
Fig 1.7

From figure 1.7, we can clearly see that the backup by default is compressed.

Conclusion

We can make backups with different pattern.

Reference taken from:-Muthusamy Anantha Kumar aka The MAK (http://www.databasejournal.com)