您当前的位置: 首页 » MySQL/MSSQL编程 » SQL Server 2008之备份压缩
SQL Server 2008之备份压缩
论坛链接
经过漫长的等待之后，即将发布的SQL Server 2008数据库终于内置了压缩备份技术。在此之前的SQL Server 2005,等版本都只能通过第三方备份软件(如SQLZip等)进行压缩。
本文将以SQL Server 2008 的11月CTP测试版为例，为大家详细介绍如何分别在压缩和不压缩的情况下进行全备份(Full)、差异备份(Differential)和事务日志备份(Transactional log)，以及怎样在默认设置下启动压缩功能。
首先让我们创建一个名为“MyDB”的数据库，如下所示：
以下是引用片段：
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
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
接下来，在“MyDB”数据库中创建一个名为“MyTable”的表，如下所示：
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
然后在“MyTable”表中添加10000行数据，如下所示：
本文将以SQL Server 2008 的11月CTP测试版为例，为大家详细介绍如何分别在压缩和不压缩的情况下进行全备份(Full)、差异备份(Differential)和事务日志备份(Transactional log)，以及怎样在默认设置下启动压缩功能。
首先让我们创建一个名为“MyDB”的数据库，如下所示：
以下是引用片段：
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
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
接下来，在“MyDB”数据库中创建一个名为“MyTable”的表，如下所示：
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
然后在“MyTable”表中添加10000行数据，如下所示：
#2楼发布者：superadmin时间：2007-12-24 14:17:27
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
使用下面的T-SQL指令选择数据(详见图一)：
以下是引用片段：
use MyDB
go
Select * from MyTable
go
图一
在D盘创建一个Backup文件夹，如图二所示：
图二
接下来让我们进行一次全备份，如下所示(结果详见图三)：
Backup Database MyDB to disk ='d:\Backup\MyDB_Full.bak' with init
图三
在“MyDB”数据库的“MyTable”表中再添加更多的数据行(例如1000行)，如下所示：
以下是引用片段：
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
接下来，进行一次事务日志备份，如下所示(结果详见图四)：
Backup log MyDB to disk ='d:\Backup\MyDB_TLog_1.bak' with init
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
使用下面的T-SQL指令选择数据(详见图一)：
以下是引用片段：
use MyDB
go
Select * from MyTable
go
图一
在D盘创建一个Backup文件夹，如图二所示：
图二
接下来让我们进行一次全备份，如下所示(结果详见图三)：
Backup Database MyDB to disk ='d:\Backup\MyDB_Full.bak' with init
图三
在“MyDB”数据库的“MyTable”表中再添加更多的数据行(例如1000行)，如下所示：
以下是引用片段：
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
接下来，进行一次事务日志备份，如下所示(结果详见图四)：
Backup log MyDB to disk ='d:\Backup\MyDB_TLog_1.bak' with init
#3楼发布者：superadmin时间：2007-12-24 14:26:27
图四
默认状态下，SQL Server是不会压缩备份的。我们可以通过以下两个途径对备份进行压缩：
1)改变SQL Server的默认设置，使其对所有备份执行压缩操作;
2)在备份语句中加入“With COMPRESSION“的关键词选项。
本文此前对MyDB数据库所执行的全备份、事务日志备份都是没有进行压缩。这是SQL Server的默认设置。
下面我们来对数据库进行一次压缩全备份，如下所示(结果详见图五)：
Backup Database MyDB to disk ='d:\Backup\MyDB_Full2.bak' with COMPRESSION
图五
从图五中，你可以看到MyDB_Full2.bak文件比MyDB_Full.Bak和MyDB_Tlog_1.bak都要小得多。
接下来，在“MyDB”数据库中的“MyTable”表中再添加更多数据(本例中为1000行)，如下所示：
以下是引用片段：
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
紧接着进行一次事务日志备份，如下所示(结果详见图六)：
Backup log MyDB to disk ='d:\Backup\MyDB_TLog_new.bak' with COMPRESSION
图六
在图六中，我们看到MyDB_Tlog_new.bak文件要比MyDB_Tlog_1.bak文件小得多。
下面我们来执行一次不带压缩的差异备份，并和带压缩的差异备份进行比较。
执行以下的指令(结果详见图七)：
#4楼发布者：superadmin时间：2007-12-24 14:27:34
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
图七
从图七可以看出MyDB_Diff2.bak文件远比MyDB_Diff.bak文件要小得多。
下面我们要把SQL Server的设置从默认的“非压缩”转变为“压缩”，执行以下的SP_CONGIFURE可以达到这个目的：
以下是引用片段：
USE master
GO
EXEC sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
最后，我们来对“MyDB”数据库执行一次全备份，但在指令中不加入“WITH COMPRESSION”的关键词选项，如下所示(结果详见图八)：
Backup Database MyDB to disk ='d:\Backup\MyDB_Full3.bak'
图八
从图八我们可以清楚地看到，MyDB_Full3.bak和MyDB_Full2.bak的大小相似，都远比MyDB_Full.bak文件要小的多;可见默认设置已经更改为压缩备份。
backup database MyDB to disk ='d:\Backup\MyDB_Diff2.bak' with differential, COMPRESSION
图七
从图七可以看出MyDB_Diff2.bak文件远比MyDB_Diff.bak文件要小得多。
下面我们要把SQL Server的设置从默认的“非压缩”转变为“压缩”，执行以下的SP_CONGIFURE可以达到这个目的：
以下是引用片段：
USE master
GO
EXEC sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
最后，我们来对“MyDB”数据库执行一次全备份，但在指令中不加入“WITH COMPRESSION”的关键词选项，如下所示(结果详见图八)：
Backup Database MyDB to disk ='d:\Backup\MyDB_Full3.bak'
图八
从图八我们可以清楚地看到，MyDB_Full3.bak和MyDB_Full2.bak的大小相似，都远比MyDB_Full.bak文件要小的多;可见默认设置已经更改为压缩备份。