SQL Server 数据库快照妙用.doc

SQL Server 数据库快照妙用如果要在一个特定的时间分析数据库中的数据，你会怎么做?例如，你想要分析晚上12点的数据，你会采取什么样的措施?最经常用到的方法，创建一个计划任务，在晚上12点的时候执行备份，将当前数据库以一个新的名字备份到服务器上，然后再开始分析这个备份数据库中的数据。这样做的问题就在于，如果这个数据库很大，那么备份它就需要花费大量的时间和磁盘空间。如果你需要在数据访问高峰期做备份的话，它花费的资源足以让你的服务器宕机。然而，在SQL Server2005中，有一项新的功能，名为数据库快照，可以让你很方便的处理类似的问题。 除了上面提到的优点，使用数据库快照还有很多其它的好处，本文将集中讨论数据库快照的优点。数据库快照是一项不太起眼的功能，这意味着，许多DBA和开发者都没有注意到它的存在。数据库快照是什么数据库快照是当前数据库的只读静态视图，不包括那些还没有提交的事务。没有提交的事务被回滚了，这样才能保证数据库的事务一致性。工作原理通常，我们使用一项功能的时候并不需要知道它的工作原理。然而，知道其工作原理将会给我们的工作带来大大的好处。一旦你创建数据库快照，快照数据库将被分配到一个空闲文件中。当原始数据页发生变化的时候，该页就会被移动到这个空闲文件。当你访问数据库快照的时候，你会访问到空闲文件，以及原始数据库上那些没有发生变化的数据页。我们可以从下图看出数据库快照是如何工作的。图一(来源：SQL Server 2005 在线手册)使用数据库快照首先，你需要创建一个数据库快照。有两种方法可以创建数据库快照。CREATEDATABASEssAdventureWorks_dbss2230ON(NAME=AdventureWorks_Data,FILENAME=C:ProgramFilesMicrosoftSQLServerMSSQL.1MSSQLDataAdventureWorks_data_2230.ss)ASSNAPSHOTOFAdventureWorks;GO在上面的例子中，AdventureWork_data_2230.ss是一个空闲文件。扩展名cc是一个任意值，不是默认也不是必须的。访问数据库快照的方法和访问一般的数据库一样：SELECT*FROMssAdventureWorks_dbss2230.dbo.Employees像访问数据库一样，我么也可以像删除一个一般数据库一样删除数据库快照。DROP DATABASE ssAdventureWorks_dbss2230你可以选择将数据库快照存储在当前数据库上，这样的话，数据库快照就成为了当前数据库的一个备份。RESTOREDATABASEAdventureWorksfromDATABASE_SNAPSHOT=ssAdventureWorks_dbss2230;GO对于数据库快照，只有唯一的选项可用，这也就意味着，我们不可以对一个数据库快照进行备份或者将它们存储于数据库快照之上。定时数据库快照定时进行数据库快照非常重要，但在SQL Server管理工具中，数据库快照节点上是没有这个直接选项的。然而，我们可以利用SQL Server代理作业新建一个定时作业，用来创建一个数据库快照。以下的脚本将分为两步来创建一个定时作业。第一步是删除数据库快照而第二步是重新创建一个数据库快照。这项作业将每隔一个小时创建一个数据库快照。USE msdbGO/* Object:Job Database snapshotScript Date: 01/29/2008 16:39:31 */BEGIN TRANSACTIONDECLARE ReturnCode INTSELECT 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=NUncategorized (Local) AND category_class=1)BEGINEXEC ReturnCode = msdb.dbo.sp_add_category class=NJOB, type=NLOCAL, name=NUncategorized (Local)IF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollbackENDDECLARE jobId BINARY(16)EXEC ReturnCode =msdb.dbo.sp_add_job job_name=NDatabase snapshot,enabled=1,notify_level_eventlog=0,notify_level_email=0,notify_level_netsend=0,notify_level_page=0,delete_level=0,description=NNo description available.,category_name=NUncategorized (Local),owner_login_name=NDinesh-Mobdinesh, job_id = jobId OUTPUTIF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollback/* Object:Step drop database snapshotScript Date: 01/29/2008 16:39:32 */EXEC ReturnCode = msdb.dbo.sp_add_jobstep job_id=jobId, step_name=Ndrop 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=NTSQL,command=NDROP DATABASE ssAdventureWork_dbss2230,database_name=Nmaster,flags=0IF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollback/* Object:Step Create SnapshotScript Date: 01/29/2008 16:39:32 */EXEC ReturnCode = msdb.dbo.sp_add_jobstep job_id=jobId, step_name=NCreate 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=NTSQL,command=NCREATE DATABASE ssAdventureWorks_dbss2230 ON( NAME = AdventureWorks_Data, FILENAME =C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_2230.ss )AS SNAPSHOT OF AdventureWorks;,database_name=Nmaster,flags=0IF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollbackEXEC ReturnCode = msdb.dbo.sp_update_job job_id = jobId, start_step_id = 2IF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollbackEXEC ReturnCode = msdb.dbo.sp_add_jobschedule job_id=jobId, name=NSchedule,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=235959IF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollbackEXEC ReturnCode = msdb.dbo.sp_add_jobserver job_id = jobId, server_name = N(local)IF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:IF (TRANCOUNT 0) ROLLBACK TRANSACTIONEndSave: 其它信息很多时候我们都知道，可以从sys.databases的系统视图中看到数据库的清单列表。数据库快照也在这个清单中。在这个视图中，有两列与数据库快照相关。一列是source_database_id，它将指名源数据库ID或者快照的源数据库。另一列是is_read_only，当一个快照数据库是一个只读数据库时，该列的值为1.SELECTNAME,database_id,source_database_id,is_read_onlyFROMsys.databases优点1.数据库快照最大的优点就在于它可以作为一个报告数据库。因为数据库快照是主数据库的一个只读副本，对一个数据库快照执行报告能够大大的减少加