创建 Transact-SQL 快照备份

  • 项目

适用于： SQL Server 2022 (16.x)

本文说明使用 Transact-SQL 快照备份的主体、原因和方式。 Transact-SQL 快照备份是 SQL Server 2022 (16.x) 中的新增功能。


数据库日渐扩大。 传统上，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 命令。 将快照备份写入磁盘时，只会将连接到快照备份的元数据写入文件。 除标题和文件内容外，输出不包含任何数据库内容。 在执行快照备份过程中创建的 shell 文件应与实际快照 URI 一起使用以进行完整备份。 从该文件还原数据库需要用户在发出 RESTORE 命令之前将数据库文件从快照 URI 复制到安装点。 用户能够对此快照备份元数据文件运行所有传统 T-SQL 命令，例如 RESTORE HEADERONLY、RESTORE FILELISTONLY 以及 RESTORE DATABASE。 语法支持将快照备份元数据写入 DISK 或 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，因为暂停数据库以进行快照备份时已指定此选项。

标记备份集

可以在备份命令中使用 MEDIANAME 和 MEDIADESCRIPTION 选项来标记与快照关联的 URI。 这一用法允许备份文件携带基础快照信息以及数据库元数据。 还可以使用 NAME 和 DESCRIPTION 选项，通过单个备份集快照标记 URI。

SQL Server 不会以任何方式解释 LABEL 信息。 不过，它确实可以帮助用户使用 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';

还原中间列出的快照数据库

如果需要还原的数据库位于中间，请使用 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;

使用其他名称还原数据库

你可以使用其他名称还原数据库。 如果需要还原的数据库位于中间，请使用 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 命令拆分包含一个或服务器快照中的多个数据库的快照备份集。 这将为每个数据库生成一个备份集。

如果服务器快照的备份文件中包含 3 个数据库（包含单个备份集），执行以下命令将生成 3 个备份集，每个数据库一个。 它将为输出文件创建一个带有 <file_name_prefix>_<unique_time_stamp> 的目录。

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

使用 RESTORE BACKUPETONLY 在包含多个数据库的备份集中提取特定数据库

RESTORE BACKUPETONLY 支持 DBNAME 参数，适用于希望从备份集的 3 个数据库中输出一个数据库的用户。 它还支持 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

另请参阅