您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 資料庫快照運作原理與實戰應用

編輯：關於SqlServer

什麼是資料庫快照？

資料庫快照是唯讀、靜態的資料庫檢視，捕捉建立那一刻的資料狀態。它不複製完整資料，而是透過稀疏檔案技術，僅儲存建立快照後被修改的原始資料頁。

核心特性

建立近乎瞬間完成（僅建立中繼資料與空稀疏檔）
不佔用額外磁碟空間（初始大小接近 0，隨來源資料異動成長）
查詢快照完全無鎖定，不影響來源資料庫效能
支援報表、稽核、災難復原多種場景

運作機制：Copy-on-Write

建立快照時，SQL Server 在 NTFS 卷上建立稀疏檔，初始不配配實體空間。
來源資料庫執行 首次修改 某資料頁（8 KB）時，資料庫引擎將修改前的原始頁複製到稀疏檔對應位置。
後續同一頁再修改，不再複製（已有原始版本）。
查詢快照時：
• 未修改頁 → 直接讀取來源資料檔
• 已修改頁 → 讀取稀疏檔中的原始版本

此機制確保快照永遠反映建立時刻的資料狀態。

建立快照語法

CREATE DATABASE SourceDatabase_Snapshot_20240115
ON (
    NAME = SourceDatabase_Data,
    FILENAME = 'C:\SQLServer\SourceDatabase_Data_Snapshot.ss'
)
AS SNAPSHOT OF SourceDatabase;

檔案副檔名慣用 .ss，但非強制。
必須在 NTFS 檔案系統上。
快照屬於同一執行個體，不可跨伺服器。

實戰應用場景

1. 報表與分析查詢

長時間執行的報表查詢導向快照，避免鎖定衝突與阻塞線上交易。

2. 災難復原：快速還原誤刪資料

-- 從快照還原單一資料表
INSERT INTO SourceDatabase.dbo.Orders
SELECT * FROM SourceDatabase_Snapshot_20240115.dbo.Orders
WHERE OrderID IN (SELECT OrderID FROM DeletedOrdersLog);

3. 歷史資料稽核

定時建立每日快照，保留 30 天，滿足合規查詢需求，不佔用主資料庫空間。

完整範例腳本

USE master;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SourceDatabase')
    DROP DATABASE SourceDatabase;
GO

CREATE DATABASE SourceDatabase
ON PRIMARY (
    NAME = SourceDatabase_Data,
    FILENAME = 'C:\SQLServer\SourceDatabase_Data.mdf',
    SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 5MB
)
LOG ON (
    NAME = SourceDatabase_Log,
    FILENAME = 'C:\SQLServer\SourceDatabase_Log.ldf',
    SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
);
GO

-- 建立測試資料
USE SourceDatabase;
CREATE TABLE SalesHistory (
    SaleID INT IDENTITY PRIMARY KEY,
    ProductName CHAR(150),  -- 故意加大列寬，明顯觀察檔案成長
    SaleDate DATETIME DEFAULT GETDATE(),
    Amount MONEY
);
INSERT INTO SalesHistory (ProductName, Amount)
VALUES ('Product A', 100.00), ('Product B', 200.00);
GO

-- 建立快照
CREATE DATABASE SourceDatabase_Snap_01
ON (NAME = SourceDatabase_Data, FILENAME = 'C:\SQLServer\SourceDatabase_Data_Snap01.ss')
AS SNAPSHOT OF SourceDatabase;
GO

-- 修改來源資料
UPDATE SalesHistory SET Amount = 150.00 WHERE SaleID = 1;
-- 此時稀疏檔會寫入 SaleID=1 的原始頁

-- 查詢快照（仍見 100.00）
SELECT * FROM SourceDatabase_Snap_01.dbo.SalesHistory WHERE SaleID = 1;
-- 查詢來源（見 150.00）
SELECT * FROM SourceDatabase.dbo.SalesHistory WHERE SaleID = 1;