sql server 2005資料庫快照

使用create database命令來為資料庫建立快照，當建立一個快照時必須在create database命令中包含源資料庫中的每一個資料檔案。包括原來的邏輯名稱和一個新的物理名稱。不能夠指定其它的檔案屬性，也沒有使用日誌檔案。

這是為Archive資料庫建立一個快照的操作如下:
1.首先檢視Archive資料庫中的資料檔案的邏輯名稱

C:\Users\Administrator>sqlcmd  -S WINS7-2014DITHH\JY
1> use Archive
2> GO
已將資料庫上下文更改為 'Archive'。
1> select name,physical_name from sys.database_files;
2> GO
name                                                 physical_name
------------------------------------------------ -------------------------------------------------------------------------
Arch1                                                 C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\archdat1.mdf
Archlog1                                              C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\archlog1.ldf
Arch2                                                 C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\archdat2.ndf
(3 行受影響)

2.建立資料庫快照Archive_snapshot

1> create database Archive_snapshot on
2> (
3> name='Arch1',--資料檔案的邏輯名稱
4> filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Archive_snapshot_1.mdf' --快照檔案
5> ),
6> (
7> name='Arch2',
8> filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Archive_snapshot_2.mdf'
9> )
10> as snapshot of Archive;
11> GO

下面在源資料庫中向表t1插入一行記錄
1.先檢視錶t1中的記錄

1>use Archive;
2>GO
已將資料庫上下文更改為 'Archive'。
1>select * from t1;
2>GO
t_id        t_date
----------- --------------------
1           2019-04-25
(1 行受影響)

2.向表t1中插入一行記錄

1>insert into t1 values(2,'2019-04-26');
2>GO;
(1 行受影響)

3.再次查詢表t1中的記錄

1>select * from t1;
2>GO
t_id        t_date
----------- --------------------
1           2019-04-25
2           2019-04-26
(2 行受影響)

在快照資料庫中查詢表t1的記錄

1>use Archive_snapshot;
2>GO
已將資料庫上下文更改為 'Archive_snapshot'。
1>select * from t1;
2>GO
t_id        t_date
----------- --------------------
1           2019-04-25
(1 行受影響)

可以看到快照資料庫中的t1的記錄仍然只有一條，快照資料庫只包含快照建立時源資料庫中已有的資料。

快照中的每一個檔案都被建立為稀疏檔案，這是NTFS檔案系統的一個特性。開始時，一個稀疏檔案不包含使用者資料，也沒有分配到用來儲存使用者資料的磁碟空間。當資料被寫入稀疏檔案時，NTFS逐漸地為其分配磁碟空間。一個稀疏檔案有可能增長得很大。稀疏檔案以64KB為單位增量增長，因此磁碟上的稀疏檔案的大小總是64KB的倍數。

快照檔案只包含源資料庫中發生了變化的資料。對每一個檔案，SQL Server建立了一個儲存在快取記憶體中的位元圖，檔案的每一個頁面對應一個位元位，表示那個頁面是否已經被複制到快照中。每次當源資料庫中有一個頁面被更新時，SQL Server會檢視位元圖來檢查該頁面是否已經被複制了，如果還沒有被複制，那麼馬上將其複製到快照中。這種操作被稱為寫入時複製操作。

前面提到，位元圖儲存在調整快取裡，而不是檔案自身，所以它總是可供隨時使用。當SQL Server關閉或資料庫關閉時，位元圖會丟失並且需要在資料庫啟動時進行重建軍。當SQL Server被訪問時它會判斷讀每一個頁面是否存在稀疏檔案中，然後將這些資訊儲存在比物圖中供將業使用。

快照反映了發出create database命令的時間點---也就是在建立操作開始的那一刻。SQL Server對源資料庫進行檢查點操作並將一個同步日誌序列號(Log Sequence Number,LSN)記錄在源資料庫的日誌檔案裡。LSN是一種確定資料庫中某一特定時間點的方式。SQL Server然後在源資料庫上執行恢復，以便任何未提交事務能夠在快照中被回滾。所以雖然快照的稀疏檔案開始時是空白的，但是那並不能維護很久。如果當快照被建立時有事務正在進行，恢復程式將會在資料庫快照可用前撤消未提交的事務，所以該快照將會包含修改後資料的源資料庫所有頁面的原始版本。

快照只能在NTFS格式的捲上建立，因為該格式是唯一支援稀疏檔案技術的檔案格式。如果我們嘗試在FAT或FAT32捲上建立一個快照，那麼會收到如下類似的錯誤:

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5119, Level 16, State 1, Line 1
Cannot make the file "E:\AW_snapshot.MDF" a sparse file. Make sure the file system supports
sparse files.

第一個錯誤基本上是一個普通的錯誤資訊，第二個錯誤信提供了關於該操作為什麼失敗的更詳細資訊。

可以透過檢視動態管理函式sys.dm_io_virtual_file_stats來發現資料庫快照的每個稀疏檔案中正在使用的磁碟上的位元組資料是多少，該函式透過size_on_disk_bytes列來返回一個檔案中的當前位元組資料。該函式將database_id和file_id作為引數。快照資料庫的資料庫id和每一個稀疏檔案的檔案ID被顯示在目錄檢視sys.master_files中。還可以透過Windows資源管理器來檢視其大小。

1>select name,database_id from sys.databases;
2>GO
name                   database_id 
---------------------- ----------- 
master                 1           
tempdb                 2           
model                  3           
msdb                   4           
AdventureWorksDW       5           
AdventureWorks         6           
resource_COPY          7           
Archive                8           
Archive_snapshot       9           
                                   
(9 行受影響)                       
1>select  database_id,file_id,name,physical_name from sys.master_files;
2>GO
database_id file_id     name                      physical_name                                                                                                                                         
----------- ----------- ------------------------- -----------------------------------------------------------------------------------------                                                                                                                                                                            
1           1           master                    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf                                                                                   
1           2           mastlog                   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf                     
2           1           tempdev                   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf                      
2           2           templog                   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf                     
3           1           modeldev                  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf                       
3           2           modellog                  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf                    
4           1           MSDBData                  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf                    
4           2           MSDBLog                   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf                     
5           1           AdventureWorksDW_Data     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf       
5           2           AdventureWorksDW_Log      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Log.ldf        
6           1           AdventureWorks_Data       C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf         
6           2           AdventureWorks_Log        C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf          
7           1           data                      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource_copy.mdf    
7           2           log                       C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource_copy.ldf    
8           1           Arch1                     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\archdat1.mdf                    
8           2           Archlog1                  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\archlog1.ldf                    
8           3           Arch2                     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\archdat2.ndf                    
9           1           Arch1                     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Archive_snapshot_1.mdf          
9           3           Arch2                     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Archive_snapshot_2.mdf          
                                                                                                                                           
(19 行受影響)         
1>select * from  sys.dm_io_virtual_file_stats (9,NULL) AS vfs;
2>GO
database_id file_id sample_ms   num_of_reads         num_of_bytes_read    io_stall_read_ms     num_of_writes        num_of_bytes_written io_stall_write_ms    io_stall             size_on_disk_bytes   file_handle
----------- ------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------
9           1       1902593092  60                   491520               133                  5                    40960                4                    137                  262144               0x000000000000080C
9           3       1902593092  4                    32768                11                   1                    8192                 0                    11                   65536                0x0000000000000840
(2 行受影響)

因為同一個資料庫有可能擁有多個快照，所以我們必須確保有足夠的可用磁碟空間。快照開始時會相對較小，當隨著源資料庫的更新，每個快照都會增長。稀疏檔案的空間按照稱為區域的片斷進行分配，單位為64KB。當一個區域被分配時，除了已經更改過的一個頁面，所有的頁面都會被清零。這時該區域中還供7個更改過的頁面使用的空間，並且在這7個頁面空間被使用完之前，不會分配新的區域。

有可能會過度申請儲存空間。這意味著在通常情況下，我們能夠擁有的是實際物理儲存空間很多倍的多個快照，但一旦快照增長，物理卷就會被耗盡(當執行線上dbcc checkdb命令或相關命令時有可能會發生這種情況，因為我們無法控制這些命令所使用的內部快照存放的物理位置---它會被存放在父資料庫檔案所在的同一個鄭上。dbcc在這種情況下檢查將會失敗)。一旦物理卷空間耗盡，對源資料庫的寫操作就無法將寫之前的頁面影像複製到稀疏檔案。無法寫入頁面的快照會被記為置疑(suspect)並且無法使用，但是源資料庫仍然可以繼續執行。不可能修復一個被置疑的快照，必須將這種快照資料庫刪除。

管理快照
如果一個源資料庫中存在快照，那麼就無法刪除，分離或還原該源資料庫。如果把握一個資料庫切換到離線(offline)狀態，那麼快照將會被自動刪除。另外，我們基本上可以透過將源資料回覆(Revert)到快照建立時的狀態，並把源資料庫替換為它的一個快照。命令如下:

1> use master;
2> GO
已將資料庫上下文更改為 'master'。
1> restore database Archive from database_snapshot='Archive_snapshot';
2> GO
1> use Archive;
2> GO
已將資料庫上下文更改為 'Archive'。
1> select * from t1;
2> GO
t_id        t_date
----------- --------------------
          1 2019-04-25
(1 行受影響)

在將源資料庫Archive回覆到快照建立時的狀態後，表t1中只有一條記錄。

在回覆(Revert)操作期間，快照和源資料庫是可用的並會被標記為“還原中”。如果在回覆操作期間出現錯誤，該操作將會在資料庫重新啟動時嘗試完成回覆操作。當存在多個快照時是不能回覆到其中任何一個快照的，所以首先我們應該把除了希望回覆的快照以外的所有快照刪除。刪除一個快照的操作與drop database操作非常相似。在刪除快照的同時，所有的NTFS稀疏檔案也都被刪除了。

需要注意下面的這些與資料庫快照有關的附加註意事項:
.不能在model,master和tempdb資料庫上建立快照(SQL Server內部可以為了對master資料庫執行線上DBCC檢查而為其建立一些快照，但是這些快照是不能顯式建立的)。

1> drop database Archive_snapshot;
2> GO

.一個快照會從它的源資料庫中繼承安全約束，且由於快照是隻讀的，所以我們無法改變其許可權。

.如果從源資料庫中刪除一個使用者，該使用者會繼續留在快照中。

.不能備份或還原快照，但是能夠正常備份源資料庫；它並不受資料庫快照的影響。

.不能附加或分離快照。

.資料庫快照並不支援全文索引，全文目錄不會從源資料庫傳播到快照中。