Overview
SQL Server 2008 has come up with inbuilt compression for backups. Until the release of SQL Server 2005, compression was only available via third party backup software such as SQL Lite Speed, SQL Zip, etc.
Exercise
Following is the simple example that demonstrates how to take Full, Differential and Transactional log backups with compression, without compression and how to enable compression as a default.
/*Create database*/
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CompressionDB')
DROP DATABASE [CompressionDB]
GO
CREATE DATABASE [CompressionDB] ON PRIMARY
( NAME = N'CompressionDB_Data', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CompressionDB_Data.mdf', SIZE = 2176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'CompressionDB_log', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CompressionDB_log.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [CompressionDB] SET RECOVERY FULL
GO
Create a table “Table_1” in the database “CompressionDB” as shown below.
USE [CompressionDB]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]')
AND type in (N'U'))
DROP TABLE [dbo].[Table_1]
GO
CREATE TABLE [dbo].[Table_1](
[ID] [int] NULL,
[Name] [char](100) NULL
) ON [PRIMARY]
GO
Add 10,000 rows of data to the table Table_1 as shown below.
USE [CompressionDB]
GO
Declare @MyId int
Set @MyId = 1
While @MyId <= 10000
Begin
Insert into Table_1 select @MyId, 'A' + convert (Varchar(10), @MyId)
Set @MyId = @MyId +1
End
GO
Add 10,000 rows of data to the table “Table_1” as shown below.
USE CompressionDB
GO
Select ID, Name from Table_1
GO
The result is as shown below.
Create a folder, C:\Backup, as shown below. [Refer figure 1]
Figure 1
Take a full backup as shown below. [Refer figure 2]
Backup Database CompressionDB to disk = 'C:\Backup\CompressionDB_Full.bak' with init
GO
Figure 2
Add some more data [1000 rows] to the table “Table_1” in the database “CompressionDB” as shown below
USE [CompressionDB]
GO
Declare @MyID int
Set @MyID = 1
While @MyID<=1000
Begin
Insert into Table_1 select @MyID, 'A' + Convert (Varchar (10), @MyID)
Set @MyID = @MyID + 1
End
GO
Now let us a take a transaction log backup, as shown below. [Refer figure 3]
Backup log CompressionDB to disk = 'C:\Backup\CompressionDB_TLog_1.bak' with init
GO
Figure 3
SQL Server does not compress the backups by default. We can compress the backups in two different ways.
- Change the default behavior of SQL Server to compress all of the backups.
- Add an optional keyword “With COMPRESSION” in the backup clause.
Full backup and transactional log backup taken of the database CompressionDB, were without compression. That is the default SQL Server behavior.
Take a full backup of the database with compression as shown below. [Refer figure 4]
Backup Database CompressionDB to disk = 'C:\Backup\CompressionDB_Full2.bak' with COMPRESSION
GO
Figure 4
From figure 4, we can see that the size of CompressionDB_Full2.bak is much smaller when compared to CompressionDB_Full.Bak and CompressionDB_Tlog_1.bak.
Add some more data [1000 rows] to the table “Table_1” in the database “CompressionDB” as shown below.
USE [CompressionDB]
GO
Declare @MyID int
Set @MyID = 1
While @MyID <= 1000
Begin
Insert into Table_1 select @MyID, 'A' + Convert (varchar (10), @MyID)
Set @MyID = @MyID + 1
End
Take a transaction log backup as shown below. [Refer figure 5]
Backup log CompressionDB to disk = 'C:\Backup\CompressionDB_TLog_new.bak' with COMPRESSION
GO
Figure 5
In figure 5, we can see that the size of CompressionDB_Tlog_new.bak is much smaller when compared to CompressionDB_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 figure 6]
Backup database CompressionDB to disk = 'C:\Backup\CompressionDB_Diff.bak' with differential
GO
Backup database CompressionDB to disk = 'C:\Backup\CompressionDB_Diff2.bak' with differential, COMPRESSION
GO
Figure 6
Figure 6 shows the compression ratio between CompressionDB_Diff.bak and CompressionDB_Diff2.bak.
Change the default behavior of SQL Server from uncompress backup to compress backup. 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 CompressionDB database, without the optional keyword “WITH COMPRESSION”. [Refer figure 7]
Backup Database CompressionDB to disk = 'C:\Backup\CompressionDB_Full3.bak'
GO
Figure 7
From figure 7, we can clearly see that the backup by default is compressed.
Conclusion
This document 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. In
Related Posts
- Automatic Backup Compression in SQL Server 2008
- Backup SQL Server Database using SSMS
- Backup SQL Server database using T-SQL
- Backup and Restore Transaction Log in SQL Server
- Split Backup Files while Backup and Restore SQL Server Database
Tags: SQL Server 2008, SQL Server 2008 Backup, sql server backup compression
