建立 Transact-SQL 快照集備份

  • 發行項

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

本文說明使用 Transact-SQL 快照集備份的內容、原因和方式。 SQL Server 2022 (16.x) 目前提供 Transact-SQL 快照集備份。


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

因為備份本身發生在硬體層級，所以不是單純的 SQL Server 解決方案。 SQL Server 必須先準備快照集的資料和記錄檔，確保檔案處於稍後可還原的狀態。 完成此動作後，SQL Server 會凍結 I/O 並交出控制，由備份應用程式完成快照集。 成功完成快照集後，應用程式必須將控制交還 SQL Server，然後 I/O 會繼續執行。 基於快照集作業期間必須凍結 I/O，快照集必須即時出現，所以伺服器上的工作負載不會長時間中斷。 使用者以往完成快照集備份，是依賴在 SQL 寫入器服務上組建的協力廠商解決方案。 SQL 寫入器服務相依於 Windows VSS (磁碟區陰影複製服務) 與 SQL Server VDI (虛擬裝置介面)，並執行 SQL Server 與磁碟層級快照集間的協調流程。 SQL 寫入器服務型的備份用戶端通常很複雜，而且只能在 Windows 上使用。 使用 T-SQL 快照集備份時，您可以使用一組 T-SQL 命令處理 SQL Server 端的協調流程。 這讓使用者可以建立自己的小型備份應用程式，並在 Windows 或 Linux 上執行，或者基礎儲存體支援指令碼介面起始快照集時，撰寫解決方案。

以下範例 PowerShell 指令碼 使用 SQL Server 2022 (16.x) (和更新版本) 採用的 T-SQL 快照集備份功能，示範備份和還原 Azure SQL IaaS 虛擬機器資料庫的端對端解決方案。

工作流程

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

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

注意

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

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

Diagram that shows process from suspend, to snapshot, to backup.

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

Diagram shows example of how the backup script can work with SQL Server to complete the backup process.

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

Diagram shows how the restore script can work with SQL Server to complete the restore task from a snapshot backup.

限制

此功能可以備份的資料庫數目上限為 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 複製至掛接點，才能從此檔案還原資料庫。 使用者可以在此快照集備份中繼資料檔案上，同時執行所有傳統 T-SQL 命令 (例如 RESTORE HEADERONLY、RESTORE FILELISTONLY) 與 RESTORE DATABASE。 語法支援將快照集備份中繼資料寫入磁碟或 URL。 另外，您也可以附加快照集備份組，如同串流備份組至單一檔案。

注意

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

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

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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));

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

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

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

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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

注意

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

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

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

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;

注意

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

執行僅限複製快照集備份

鑑於凍結前會清除差異點陣圖，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，因為暫止資料庫進行快照集備份時，已指定該選項。

標記 backupset

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

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

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

使用 RESTORE HEADERONLY 輸出快照集備份

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

RESTORE HEADERONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;

使用 RESTORE FILELISTONLY 輸出快照集備份

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

RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;

篩選 RESTORE FILELISTONLY 輸出至備份組

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

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

Screenshot of SSMS output to backups set from query.

篩選 RESTORE FILELISTONLY 輸出至資料庫

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

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

Screenshot of results of filtering RESTORE FILELISTONLY output to a database.

還原快照集資料庫

從快照集備份還原資料庫很類似附加資料庫。 如果資料庫需要附加而不復原，請執行不使用 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';

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

如果需要 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;

還原不同名稱的資料庫

您可以還原不同名稱的資料庫。 如果需要 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;

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

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

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

RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db1.bkm'
WITH METADATA_ONLY;

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

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

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

動態管理檢視 (DMV) 提供查看暫止狀態和取得的鎖定

sys.dm_server_suspend_status (db_id, db_name, suspend_session_id, suspend_time_ms, is_diffmap_cleared, is_writeio_frozen)
sys.dm_tran_locks (resource_type, resource_database_id, resource_lock_partition, request_mode, request_type, request_status, request_owner_type, request_session_id)

列出 T-SQL 快照備份的 backupset 詳細資料

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

伺服器和資料庫層級屬性提供檢查資料庫是否因快照集備份暫止

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

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

下列範例 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

另請參閱