建立 Transact-SQL 快照集備份

文章
10/16/2024

適用於：SQL Server 2022 (16.x)

本文說明使用 Transact-SQL 快照集備份的內容、原因和方式。 SQL Server 2022 (16.x) 中引進了 Transact-SQL (T-SQL) 快照集備份。

資料庫日益變大。傳統上，SQL Server 備份是串流備份。串流備份因資料庫的大小而有不同。備份作業會取用資源 (CPU、記憶體、I/O、網路)，影響備份期間並行的 OLTP 工作負載輸送量。穩定備份效能的方式不是基於資料大小，而是使用基礎儲存體硬體或服務提供的機制，執行快照集備份。

因為備份本身發生在硬體層級，所以此功能不是單純的 SQL Server 解決方案。 SQL Server 必須先準備快照集的資料和記錄檔，確保檔案處於稍後可還原的狀態。完成此步驟後，SQL Server 會凍結寫入作業 (讀取要求仍被允許)，並交出控制，由備份應用程式完成快照集。成功完成快照集後，應用程式必須將控制交還 SQL Server，然後寫入作業會繼續執行。

基於快照集作業期間必須凍結寫入作業，快照集必須即時出現，所以伺服器上的工作負載不會長時間中斷。使用者以往完成快照集備份，是依賴在 SQL 寫入器服務上組建的非 Microsoft 解決方案。 SQL 寫入器服務相依於 Windows VSS (磁碟區陰影複製服務) 與 SQL Server VDI (虛擬裝置介面)，並執行 SQL Server 與磁碟層級快照集間的協調流程。

SQL 寫入器服務型的備份用戶端通常很複雜，而且只能在 Windows 上使用。使用 T-SQL 快照集備份時，您可以使用一組 T-SQL 命令處理 SQL Server 端的協調流程。此功能讓使用者可以建立自己的小型備份應用程式，並在 Windows 或 Linux 上執行，或者基礎儲存體支援指令碼介面起始快照集時，撰寫解決方案。

以下是 PowerShell 指令碼範例，示範了在 Azure SQL IaaS 虛擬機中備份和還原資料庫的端對端解決方案。此範例會使用 SQL Server 2022 (16.x) 中引進的 T-SQL 快照集備份功能。

工作流程

T-SQL 快照集備份語法會從暫止或備份作業，脫離廠商相依的快照集機制。使用此語法後，您可以：

使用 ALTER 命令凍結資料庫，換句話說，您可以執行基礎儲存體的快照集。之後，您可以解除凍結資料庫，並使用 BACKUP 命令記錄快照集。
同時使用新的 BACKUP GROUP 和 BACKUP SERVER 命令，執行多個資料庫的快照集。使用此選項時，您可以在基礎儲存體的快照集資料粒度執行快照集，而不必多次執行相同磁碟的快照集。
執行 FULL 備份和 COPY_ONLY FULL 備份。這些備份也會記錄在 msdb 中。
快照集 FULL 備份後，以一般串流方法取得並使用記錄備份，執行時間點復原。如有需要，也支援串流差異備份。

注意

使用 ALTER 命令暫止資料庫時，第一個階段是清除差異點陣圖。如果因為快照集失敗或任何其他理由，使用者決定解除凍結資料庫，不執行備份，差異點陣圖即無效。差異備份必須掃描整個資料庫，才能執行備份，導致任何後續的差異備份會更密集 I/O。快照成功備份後，差異點陣圖會重新生效。

下圖說明 T-SQL 快照集備份的高階工作流程：

中間快照集步驟會要求您在基礎儲存體上起始快照集。下圖顯示的範例是，如何搭配 SQL Server 使用備份指令碼，完成快照集備份流程：

同樣地，還原指令碼的運作方式如下所示：

限制

此功能可以備份的資料庫數目上限為 64。如果伺服器上的資料庫超過 64 個，您會看到下列錯誤：

Error message:
Msg 925, Level 19, State 1, Line 4
Maximum number of databases used for each query has been exceeded. The maximum allowed is 64.

範例

下列各節顯示不同 T-SQL 命令，是用來執行磁碟的快照集備份。快照集備份寫入磁碟後，只有連線快照集備份的中繼資料會寫入檔案。輸出不包含任何資料庫內容，但標頭和檔案內容除外。若要建立完整的備份，建立為執行快照集備份一部分的殼層檔案，最好搭配實際的快照集 URI 使用。發行 RESTORE 命令前，使用者必須將資料庫檔案從快照集 URI 複製至掛接點，才能從此檔案 RESTORE 資料庫。使用者可以在此快照集備份中繼資料檔案上，同時執行所有傳統 T-SQL 命令 (例如 RESTORE HEADERONLY 和 RESTORE FILELISTONLY) 與 RESTORE DATABASE。語法支援將快照集備份中繼資料寫入 DISK 或 URL。另外，您也可以附加快照集備份組，如同串流備份組至單一檔案。

注意

若要備份至 URL，雖然 Windows 上的 SQL Server 支援分頁 Blob，但建議使用區塊 Blob。如果是 Linux 上的 SQL Server 和容器，僅支援區塊 Blob。

A. 暫止單一使用者資料庫進行快照集備份，並記錄資料庫備份

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

B. 暫止多個使用者資料庫進行快照集備份

如果同個基礎磁碟上有多個資料庫，您可以使用下列命令暫止多個資料庫。

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));

BACKUP GROUP testdb1, testdb2
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

C. 暫止伺服器上所有使用者資料庫進行快照集備份

若要暫止伺服器上所有使用者資料庫，請使用下列命令。

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

BACKUP SERVER
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

注意

這些命令不支援暫止系統資料庫 (master、model 和 msdb) 進行快照集備份。

D. 使用單一命令暫止多個使用者資料庫

在單一備份組中記錄記錄伺服器上所有使用者資料庫的快照集記錄：

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));

BACKUP GROUP testdb1, testdb2
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

注意

根據預設，SUSPEND_FOR_SNAPSHOT_BACKUP 命令會清除差異點陣圖。如果您要執行只複製備份，請使用 COPY_ONLY 關鍵字，如下列範例所示。

E. 執行僅限複製快照集備份

由於差異點陣圖會在凍結之前清除，SUSPEND_FOR_SNAPSHOT_BACKUP 因此提供了在凍結之前不清除差異點陣圖的選項 (COPY_ONLY)。

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);

BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2), MODE = COPY_ONLY);

BACKUP GROUP testdb1, testdb2
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);

BACKUP SERVER
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

注意

BACKUP 命令不一定要使用 COPY_ONLY，因為暫止資料庫進行快照集備份時，已指定該選項。

F. 使用不同磁碟機上的資料和記錄檔備份資料庫

如果您的資料庫的資料檔案 (.mdf 和 .ndf) 跨多個磁碟機，而交易記錄檔 (.ldf) 位於不同的磁碟機上，則可以如下執行快照集備份：

暫止資料庫 (這會凍結資料檔和記錄檔上的寫入 I/O)。
```
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
```
製作資料庫資料和記錄檔所在的所有底層磁碟的快照集。此步驟取決於硬體。
使用 METADATA_ONLY 選項執行備份，可建立包含快照集備份中繼資料 (.bkm) 的輸出。
```
BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;
```

要在稍後階段還原此備份，請遵循下列步驟：

在要還原的 VM 上掛接或附加快照集磁碟。
當您執行資料庫還原時，請使用 .bkm 檔案 (上一個清單中的步驟 3)。
如果磁碟機在還原期間不同，請使用邏輯檔案的 MOVE 選項，將它們放在所需目的地。如需範例，請參閱範例 N。

G. 標記 backupset

您可以使用備份命令中的 MEDIANAME 和 MEDIADESCRIPTION 選項，標記快照集關聯的 URI。此用法讓備份檔案同時傳送基礎快照集資訊與資料庫中繼資料。您也可以使用 NAME 和 DESCRIPTION 選項，標記個別 backupset 快照集的 URI。

SQL Server 絕不以任何方式解譯 LABEL 資訊。但 SQL Server 會協助使用者使用 RESTORE LABELONLY 命令，檢視快照集備份關聯的 URI。

然後，您可以將位於 URI 的快照集磁碟附加至 VM，還原快照集。然後，儲存在 MEDIANAME 和 MEDIADESCRIPTION 中的快照集 URI 也可以在 msdb 資料庫資料表 dbo.backupmediaset 中檢視。

H. 使用 RESTORE HEADERONLY 輸出快照集備份

如果依序執行資料庫、群組和伺服器，並將三者的備份寫入相同的輸出檔案，使用 RESTORE HEADERONLY 輸出看起來如下列範例所示：

RESTORE HEADERONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

I. 使用 RESTORE FILELISTONLY 輸出快照集備份

RESTORE FILELISTONLY 輸出預設顯示第一個備份組：

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

J. 篩選 RESTORE FILELISTONLY 輸出至備份組

若要使用 RESTORE FILELISTONLY 從多個備份組明確選取特定的備份組，請在 RESTORE FILELISTONLY 上，使用已支援的 FILE 子句。

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;

SSMS 輸出至查詢備份組的螢幕擷取畫面。

K. 篩選 RESTORE FILELISTONLY 輸出至資料庫

若要使用 RESTORE FILELISTONLY 從選取備份組中的多個資料庫，進一步選取單一資料庫，請搭配新推出的 DBNAME 子句使用 FILE 子句。 DBNAME 子句只能用於快照集備份組。

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';

篩選 RESTORE FILELISTONLY 輸出至資料庫的結果螢幕擷取畫面。

L. 還原快照集資料庫

從快照集備份還原資料庫很類似附加資料庫。如果資料庫需要附加而不復原，請執行不使用 RECOVERY 選項的還原命令。根據預設，RESTORE 會選取快照集備份組中的第一個資料庫。下列範例還原 testdb1。如果伺服器上已有 testdb1，請包含 REPLACE 子句。執行 RESTORE 前，請務必掛接資料庫檔案。

RESTORE DATABASE testdb1
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, REPLACE, --> no DBNAME clause - restore first database in backup set
MOVE 'testdb1' TO 'D:\Temp\snap\testdb1.mdf',
MOVE 'testdb1_log' TO 'D:\Temp\snap\testdb1_log.ldf';

M. 還原中間所列的快照集資料庫

如果需要 RESTORED 的資料庫位於中間，請使用 DBNAME 子句指定要還原的資料庫。下列語法會還原 DBNAME 子句指定的資料庫。

RESTORE DATABASE testdb3
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> restores testdb3 database
MOVE 'testdb3' TO 'D:\Temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'D:\Temp\snap\testdb3_log.ldf',
NORECOVERY;

N. 還原不同名稱的資料庫

您可以還原不同名稱的資料庫。如果需要 RESTORED 的資料庫位於中間，請使用 DBNAME 子句指定要還原的資料庫。下列語法使用 DBNAME 子句，還原並重新命名指定的資料庫為 testdb33。

RESTORE DATABASE testdb33 --> renames the specified database testdb3 to testdb33.
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> original name specified here
MOVE 'testdb3' TO 'D:\Temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'D:\Temp\snap\testdb3_log.ldf',
NORECOVERY;

O. 使用 RESTORE BACKUPETONLY 從包含多個資料庫的備份組擷取資料庫

快照集備份組包含群組或伺服器快照集的多個資料庫時，您可以使用 RESTORE BACKUPSETONLY 命令，分割快照集備份組。此命令會為每個資料庫產生一個備份組。

如果伺服器快照集包含三個資料庫，而且是在包含單一備份組的備份檔案中，下列命令會產生三個備份組，每個資料庫各一個。此命令會針對輸出檔案建立包含 <file_name_prefix>_<unique_time_stamp> 的目錄。

RESTORE BACKUPSETONLY
FROM DISK = 'D:\Temp\db1.bkm'
WITH METADATA_ONLY;

P. 使用 RESTORE BACKUPETONLY 從包含多個資料庫的備份組，擷取特定的資料庫

如果使用者要輸出備份組中三個資料庫的其中一個，RESTORE BACKUPSETONLY 支援 DBNAME 參數。另外也支援 FILE 參數，可篩選備份檔案中的多個備份組。

RESTORE BACKUPSETONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';

問：監視暫止狀態和取得的鎖定

您可以使用下列動態管理檢視 (DMV)：

sys.dm_server_suspend_status (檢視暫止狀態)
sys.dm_tran_locks (檢視取得的鎖定)

R. 列出 backupset 詳細資料

下列範例指令碼會列出 Transact-SQL 快照集備份的 backupset 資訊。

SELECT database_name,
    type,
    backup_size,
    backup_start_date,
    backup_finish_date,
    is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;

S. 檢查資料庫是否已暫止快照集備份

下列範例指令碼會輸出針對快照集備份而暫止的資料庫的資料庫層級屬性。

SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');

T. T-SQL 疑難排解指令碼範例

下列範例指令碼會偵測伺服器上暫止的資料庫，並視需要取消暫止。

IF (SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') = 1)
BEGIN
    --full server suspended, requires server level thaw
    PRINT 'Full server is suspended, requires server level thaw'

    ALTER SERVER CONFIGURATION
    SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF
END
ELSE
BEGIN
    IF (SERVERPROPERTY('SuspendedDatabaseCount') > 0)
    BEGIN
        DECLARE @curdb SYSNAME
        DECLARE @sql NVARCHAR(500)

        DECLARE mycursor CURSOR FAST_FORWARD
        FOR
        SELECT db_name
        FROM sys.dm_server_suspend_status;

        OPEN mycursor

        FETCH NEXT
        FROM mycursor
        INTO @curdb

        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'unfreezing DB ' + @curdb

            SET @sql = 'ALTER DATABASE ' + @curdb + ' SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF'

            EXEC sp_executesql @SQL

            FETCH NEXT
            FROM mycursor
            INTO @curdb
        END

        PRINT 'All DB unfrozen'

        CLOSE mycursor;

        DEALLOCATE mycursor;
    END
    ELSE
        -- no suspended database, thus no user action needed.
        PRINT 'No database/server is suspended for snapshot backup'
END

分享方式：

建立 Transact-SQL 快照集備份

工作流程

限制

範例

A. 暫止單一使用者資料庫進行快照集備份，並記錄資料庫備份

B. 暫止多個使用者資料庫進行快照集備份

C. 暫止伺服器上所有使用者資料庫進行快照集備份

D. 使用單一命令暫止多個使用者資料庫

E. 執行僅限複製快照集備份

F. 使用不同磁碟機上的資料和記錄檔備份資料庫

G. 標記 backupset

H. 使用 RESTORE HEADERONLY 輸出快照集備份

I. 使用 RESTORE FILELISTONLY 輸出快照集備份

J. 篩選 RESTORE FILELISTONLY 輸出至備份組

K. 篩選 RESTORE FILELISTONLY 輸出至資料庫

L. 還原快照集資料庫

M. 還原中間所列的快照集資料庫

N. 還原不同名稱的資料庫

O. 使用 RESTORE BACKUPETONLY 從包含多個資料庫的備份組擷取資料庫

P. 使用 RESTORE BACKUPETONLY 從包含多個資料庫的備份組，擷取特定的資料庫

問：監視暫止狀態和取得的鎖定

R. 列出 backupset 詳細資料

S. 檢查資料庫是否已暫止快照集備份

T. T-SQL 疑難排解指令碼範例

意見反映

更多資源

分享方式：

建立 Transact-SQL 快照集備份

工作流程

限制

範例

A. 暫止單一使用者資料庫進行快照集備份，並記錄資料庫備份

B. 暫止多個使用者資料庫進行快照集備份

C. 暫止伺服器上所有使用者資料庫進行快照集備份

D. 使用單一命令暫止多個使用者資料庫

E. 執行僅限複製快照集備份

F. 使用不同磁碟機上的資料和記錄檔備份資料庫

G. 標記 backupset

H. 使用 RESTORE HEADERONLY 輸出快照集備份

I. 使用 RESTORE FILELISTONLY 輸出快照集備份

J. 篩選 RESTORE FILELISTONLY 輸出至備份組

K. 篩選 RESTORE FILELISTONLY 輸出至資料庫

L. 還原快照集資料庫

M. 還原中間所列的快照集資料庫

N. 還原不同名稱的資料庫

O. 使用 RESTORE BACKUPETONLY 從包含多個資料庫的備份組擷取資料庫

P. 使用 RESTORE BACKUPETONLY 從包含多個資料庫的備份組，擷取特定的資料庫

問： 監視暫止狀態和取得的鎖定

R. 列出 backupset 詳細資料

S. 檢查資料庫是否已暫止快照集備份

T. T-SQL 疑難排解指令碼範例

相關內容

意見反映

更多資源

問：監視暫止狀態和取得的鎖定