创建 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 上的写入操作将暂停(仍然允许读取请求),控制权将移交给备份应用程序以完成快照。 快照成功完成后,应用程序必须将控制权返回给 SQL Server,随后在那里恢复写入操作。 由于我们必须在快照操作期间冻结写入操作,因此快照必须快速发生,这样服务器上的工作负载才不会长时间中断。 在过去,用户依赖于基于 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 虚拟机中备份和还原数据库的端到端解决方案。
Workflow
T-SQL 快照备份语法将依赖于供应商的快照机制从挂起和备份操作中分离。 使用此语法,可以:
- 使用 ALTER 命令冻结数据库 - 为你提供执行基础存储快照的机会。 之后,你可以使用 BACKUP 命令解冻数据库并记录快照。
- 使用新的 BACKUP GROUP 和 BACKUP SERVER 命令同时执行多个数据库的快照。 通过此选项,可以以基础存储的快照粒度执行快照,而无需多次对同一磁盘执行快照。
- 执行完整备份和 COPY_ONLY 完整备份。 这些备份也会记录在
msdb
中。 - 使用快照执行完整备份之后,采用正常流式处理方法执行的日志备份执行时间点还原。 如果需要,也支持流式传输差异备份。
注意
差异位图在使用 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 命令之前将数据库文件从快照 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;
筛选到数据库的 RESTORE FILELISTONLY 输出
筛选以使用 RESTORE FILELISTONLY 从所选备份集中的多个数据库中进一步选择单个数据库,请使用包含新引入的 DBNAME 子句的 FILE 子句。 DBNAME 子句只能在快照备份集上使用。
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';
还原快照数据库
从快照备份还原数据库类似于附加数据库。 如果需要附加数据库而不进行还原,请运行不带 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