创建 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 快照备份功能。

Workflow

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 命令。将快照备份写入磁盘时，只会将连接到快照备份的元数据写入文件。除标题和文件内容外，输出不包含任何数据库内容。在执行快照备份过程中创建的 shell 文件应与实际快照 URI 一起使用以进行完整备份。从该文件 RESTORE 数据库需要用户在发出 RESTORE 命令之前将数据库文件从快照 URI 复制到安装点。用户能够对此快照备份元数据文件运行所有传统 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;

注意

无需对 COPY_ONLY 命令使用 BACKUP，因为暂停数据库以进行快照备份时已指定此选项。

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. 标记备份集

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

SQL Server 不会以任何方式解释 LABEL 信息。不过，它确实可以帮助用户使用 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 从多个备份集中专门选择特定的备份集，请使用 FILE 上已支持的 RESTORE FILELISTONLY 子句。

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

从查询到备份集的 SSMS 输出的屏幕截图。

K. 筛选到数据库的 RESTORE FILELISTONLY 输出

要使用 RESTORE FILELISTONLY 从所选备份集中的多个数据库中进一步选择单个数据库，请使用包含 FILE 子句的 DBNAME 子句。 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 命令拆分包含一个或服务器快照中的多个数据库的快照备份集。此命令将为每个数据库生成一个备份集。

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

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

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

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

以下示例脚本列出了 Transact-SQL 快照备份的备份集信息。

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 快照备份

Workflow

限制

示例

A. 暂停单个用户数据库以进行快照备份，并记录数据库备份

B. 暂停多个用户数据库以进行快照备份

°C 暂停服务器上所有用户数据库以进行快照备份

D. 使用单个命令暂停多个用户数据库

E. 执行仅复制快照备份

F. 使用不同驱动器上的数据和日志文件备份数据库

G. 标记备份集

H. 使用 RESTORE HEADERONLY 的快照备份输出

I. 使用 RESTORE FILELISTONLY 的快照备份输出

J. 筛选到备份集的 RESTORE FILELISTONLY 输出

K. 筛选到数据库的 RESTORE FILELISTONLY 输出

L. 还原快照数据库

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

N. 使用其他名称还原数据库

O. 使用 RESTORE BACKUPETONLY 从包含多个数据库的备份集中提取数据库

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

问：监视暂停状态和获取的锁

R. 列出备份集详细信息

S. 检查是否已暂停数据库以进行快照备份

T. T-SQL 故障排除脚本示例

反馈

其他资源

通过

创建 Transact-SQL 快照备份

Workflow

限制

示例

A. 暂停单个用户数据库以进行快照备份，并记录数据库备份

B. 暂停多个用户数据库以进行快照备份

°C 暂停服务器上所有用户数据库以进行快照备份

D. 使用单个命令暂停多个用户数据库

E. 执行仅复制快照备份

F. 使用不同驱动器上的数据和日志文件备份数据库

G. 标记备份集

H. 使用 RESTORE HEADERONLY 的快照备份输出

I. 使用 RESTORE FILELISTONLY 的快照备份输出

J. 筛选到备份集的 RESTORE FILELISTONLY 输出

K. 筛选到数据库的 RESTORE FILELISTONLY 输出

L. 还原快照数据库

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

N. 使用其他名称还原数据库

O. 使用 RESTORE BACKUPETONLY 从包含多个数据库的备份集中提取数据库

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

问： 监视暂停状态和获取的锁

R. 列出备份集详细信息

S. 检查是否已暂停数据库以进行快照备份

T. T-SQL 故障排除脚本示例

相关内容

反馈

其他资源

问：监视暂停状态和获取的锁