SQL Server数据库快照妙用

【IT专家网独家】如果要在一个特定的时间分析数据库中的数据，你会怎么做?例如，你想要分析晚上12点的数据，你会采取什么样的措施?最经常用到的方法，创建一个计划任务，在晚上12点的时候执行备份，将当前数据库以一个新的名字备份到服务器上，然后再开始分析这个备份数据库中的数据。这样做的问题就在于，如果这个数据库很大，那么备份它就需要花费大量的时间和磁盘空间。如果你需要在数据访问高峰期做备份的话，它花费的资源足以让你的服务器宕机。然而，在SQL Server2005中，有一项新的功能，名为数据库快照，可以让你很方便的处理类似的问题。

除了上面提到的优点，使用数据库快照还有很多其它的好处，本文将集中讨论数据库快照的优点。数据库快照是一项不太起眼的功能，这意味着，许多DBA和开发者都没有注意到它的存在。

数据库快照是什么

数据库快照是当前数据库的只读静态视图，不包括那些还没有提交的事务。没有提交的事务被回滚了，这样才能保证数据库的事务一致性。

工作原理

通常，我们使用一项功能的时候并不需要知道它的工作原理。然而，知道其工作原理将会给我们的工作带来大大的好处。

一旦你创建数据库快照，快照数据库将被分配到一个空闲文件中。当原始数据页发生变化的时候，该页就会被移动到这个空闲文件。当你访问数据库快照的时候，你会访问到空闲文件，以及原始数据库上那些没有发生变化的数据页。我们可以从下图看出数据库快照是如何工作的。

图一(来源：SQL Server 2005 在线手册)

使用数据库快照

首先，你需要创建一个数据库快照。有两种方法可以创建数据库快照。

CREATE DATABASE ssAdventureWorks_dbss2230 ON

( NAME = AdventureWorks_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_2230.ss' )

AS SNAPSHOT OF AdventureWorks;

GO

在上面的例子中，AdventureWork_data_2230.ss是一个空闲文件。扩展名cc是一个任意值，不是默认也不是必须的。

访问数据库快照的方法和访问一般的数据库一样：

SELECT *

FROM [ssAdventureWorks_dbss2230].dbo.Employees

像访问数据库一样，我么也可以像删除一个一般数据库一样删除数据库快照。

DROP DATABASE [ssAdventureWorks_dbss2230]

你可以选择将数据库快照存储在当前数据库上，这样的话，数据库快照就成为了当前数据库的一个备份。

RESTORE DATABASE AdventureWorks from

DATABASE_SNAPSHOT = 'ssAdventureWorks_dbss2230';

GO

对于数据库快照，只有唯一的选项可用，这也就意味着，我们不可以对一个数据库快照进行备份或者将它们存储于数据库快照之上。

定时数据库快照

定时进行数据库快照非常重要，但在SQL Server管理工具中，数据库快照节点上是没有这个直接选项的。然而，我们可以利用SQL Server代理作业新建一个定时作业，用来创建一个数据库快照。

以下的脚本将分为两步来创建一个定时作业。第一步是删除数据库快照而第二步是重新创建一个数据库快照。这项作业将每隔一个小时创建一个数据库快照。

USE [msdb]
GO
/****** Object: Job [Database snapshot]    Script Date: 01/29/2008 16:39:31 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]]    Script Date: 01/29/2008 16:39:31 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Database snapshot',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=0,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'No description available.',
            @category_name=N'[Uncategorized (Local)]',
            @owner_login_name=N'Dinesh-Mob\dinesh', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [drop database snapshot]    Script Date: 01/29/2008 16:39:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'drop database snapshot',
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=3,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'DROP DATABASE [ssAdventureWork_dbss2230]',
            @database_name=N'master',
            @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Snapshot]    Script Date: 01/29/2008 16:39:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Snapshot',
            @step_id=2,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'CREATE DATABASE ssAdventureWorks_dbss2230 ON
( NAME = AdventureWorks_Data, FILENAME =
''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_2230.ss'' )
AS SNAPSHOT OF AdventureWorks;
',
            @database_name=N'master',
            @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule',
            @enabled=1,
            @freq_type=4,
            @freq_interval=1,
            @freq_subday_type=8,
            @freq_subday_interval=1,
            @freq_relative_interval=0,
            @freq_recurrence_factor=0,
            @active_start_date=20080129,
            @active_end_date=99991231,
            @active_start_time=0,
            @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

其它信息

很多时候我们都知道，可以从sys.databases的系统视图中看到数据库的清单列表。数据库快照也在这个清单中。在这个视图中，有两列与数据库快照相关。一列是source_database_id，它将指名源数据库ID或者快照的源数据库。另一列是is_read_only，当一个快照数据库是一个只读数据库时，该列的值为1.

SELECT NAME,

database_id,

source_database_id,

is_read_only

FROM sys.databases

优点

1.数据库快照最大的优点就在于它可以作为一个报告数据库。因为数据库快照是主数据库的一个只读副本，对一个数据库快照执行报告能够大大的减少加载时间。

2.数据库快照只需要几个特征值就可以恢复源数据库。

缺点

1.数据库快照最主要的缺点就是它只能在SQL Server企业版上使用。我们都知道，企业版的成本很好，因此不是每一个人都能够使用这一项功能。

2.数据库快照是依附于主数据库，因此不能单独使用。

3.数据库快照中不支持全文检索。

结论

数据库快照是SQL Server企业版中一个非常方便的功能。然而，需要强调的是，数据库快照不能替代数据库备份。如果你想很好的利用这项有用的功能，可以在数据报告中多使用它。

TAG:
SQLServer