BACKUP compression in SQL Server 2008

After a long wait, SQL Server 2008 is going to be
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

/****** Object:  Database [MyDB]

	Script Date: 12/10/2007 01:08:14 ******/

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

DROP DATABASE [MyDB]

GO
USE [master]

GO

/****** Object:  Database [MyDB]

	Script Date: 12/10/2007 01:05:09 ******/

CREATE DATABASE [MyDB] ON  PRIMARY

( NAME = N’MyDB_Data’,

  FILENAME =

  N’F:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\

  	MSSQL\DATA\MyDB_Data.mdf’ ,

  SIZE = 2176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N’MyDB_log’,

  FILENAME =

  N’F:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\

  	MSSQL\DATA\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

/****** Object:  Table [dbo].[MyTable]

	Script Date: 12/10/2007 01:12:00 ******/

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
/****** Object:  Table [dbo].[MyTable]

	Script Date: 12/10/2007 01:12:26 ******/

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

begin

insert into MyTable select @myid, ‘A’+convert(varchar(10),@myid)

set @myid =@myid +1

end

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

use MyDB

go

Select * from MyTable

go

Create a
folder, D:\Backup, as shown below. [Refer 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

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

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

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

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

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

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

Conclusion

This article demonstrated how to take a Full backup,
Differential backup and transactional log backup with or without
compression and how to enable compression as a default.

»


See All Articles by Columnist MAK