建立 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 使用。 從這個檔案還原資料庫需要使用者先從快照集 URI 將資料庫檔案複製到掛接點,再發出 RESTORE 命令。 使用者可以在此快照集備份中繼資料檔案上,同時執行所有傳統 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;

注意

這些命令不支援暫止系統資料庫:mastermodelmsdb 進行快照集備份。

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

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

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,因為暫止資料庫進行快照集備份時,已指定該選項。

標記備份組

您可以使用備份命令中的 MEDIANAME 和 MEDIADESCRIPTION 選項,標記快照集關聯的 URI。 此用法讓備份檔案同時傳送基礎快照集資訊與資料庫中繼資料。 您也可以使用 NAME 和 DESCRIPTION 選項,標記個別備份組快照集的 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 快照集備份的備份組詳細資料

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

另請參閱