SQL Server資料庫快照集妙用。

如果要在一個特定的時間分析資料庫中的資料，你會怎麼做?例如，你想要分析晚上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企業版中一個非常方便的功能。然而，需要強調的是，資料庫快照集不能替代Database Backup。如果你想很好的利用這項有用的功能，可以在資料報告中多使用它。