监视 Azure SQL 托管实例的备份活动

适用于：Azure SQL 托管实例

本文介绍如何通过查询 msdb 数据库或配置扩展事件 (XEvent) 会话来监视 Azure SQL 托管实例的备份活动。

概述

Azure SQL 托管实例在 msdb 数据库中 存储备份信息，并在备份活动期间发出事件（也称为 扩展事件或 XEvents），可用于报告。 配置 XEvent 会话可以跟踪 msdb 数据库中的备份状态、备份类型、大小、时间和位置等信息。 此信息可与备份监视软件集成，也可用于企业审核。

企业审核可能需要证明备份成功、备份时间和备份持续时间。

查询 msdb 数据库

若要查看备份活动，请从用户定义的数据库运行以下查询：

SELECT TOP (100)
    DB_NAME(DB_ID(bs.database_name)) AS [Database Name],
    CONVERT (BIGINT, bs.backup_size / 1048576) AS [Uncompressed Backup Size (MB)],
    CONVERT (BIGINT, bs.compressed_backup_size / 1048576) AS [Compressed Backup Size (MB)],
    CONVERT (NUMERIC (20, 2),
    CASE
        WHEN bs.compressed_backup_size > 0
        THEN CONVERT (FLOAT, bs.backup_size) / CONVERT (FLOAT, bs.compressed_backup_size)
        ELSE NULL
    END
    ) AS [Compression Ratio],
    bs.is_copy_only,
    -- bs.user_name, -- Applicable only for user-initiated COPY ONLY backups.
    bs.has_backup_checksums,
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],
    bs.backup_finish_date AS [Backup Finish Date],
    bmf.physical_block_size
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
     INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
         ON bs.media_set_id = bmf.media_set_id
WHERE bs.[type] = 'D'
    -- AND bs.[is_copy_only] = 1  -- If you want to filter out for user initiated COPY ONLY backups.
ORDER BY bs.backup_finish_date DESC
OPTION (RECOMPILE); -- Optimize for ad hoc execution

配置 XEvent 会话

使用扩展事件 backup_restore_progress_trace 记录 SQL 托管实例备份的进度。 根据需要修改 XEvent 会话，以跟踪你感兴趣的业务信息。 这些 T-SQL 代码片段将 XEvent 会话存储在环形缓冲区中，但也可以写入 Azure Blob 存储。 在环形缓冲区中存储数据的 XEvent 会话限制约为 1,000 条消息，因此应仅用于跟踪最近的活动。 此外，环形缓冲区数据在故障转移时也会丢失。 因此，对于备份的历史记录，请改为写入事件文件。

基本跟踪

配置基本 XEvent 会话以捕获有关完整备份的事件。 此脚本收集数据库的名称、已处理的总字节数以及备份完成时间。

使用 Transact-SQL （T-SQL） 配置基本 XEvent 会话：

CREATE EVENT SESSION [Basic backup trace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
(
        WHERE operation_type = 0
        AND trace_message LIKE '%100 percent%'
)
ADD TARGET package0.ring_buffer WITH (STARTUP_STATE = ON);
GO

ALTER EVENT SESSION [Basic backup trace] ON SERVER
STATE = start;

详细跟踪

配置一个详细的 XEvent 会话，以跟踪有关备份活动的更多详细信息。 这个脚本可以捕获完整备份、差异备份和日志备份的启动和完成。 由于此脚本更详细，因此它会更快地填充环形缓冲区，因此条目的回收速度可能比使用基本脚本更快。

使用 T-SQL 配置冗长 XEvent 会话：

CREATE EVENT SESSION [Verbose backup trace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace(
    WHERE (
              [operation_type]=(0) AND (
              [trace_message] like '%100 percent%' OR
              [trace_message] like '%BACKUP DATABASE%' OR [trace_message] like '%BACKUP LOG%'))
       )
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
       MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
       TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

ALTER EVENT SESSION [Verbose backup trace] ON SERVER
STATE = start;

监视备份进度

创建 XEvent 会话后，可以使用 T-SQL 查询环形缓冲区结果并监视备份的进度。 启动 XEvent 后，它将收集所有备份事件，因此条目大约每 5-10 分钟就会被添加到会话中一次。

基本跟踪

以下 T-SQL 代码查询基本 XEvent 会话，并返回数据库的名称、已处理的字节总数以及备份完成时间：

WITH
a AS (SELECT CAST (xet.target_data AS XML) AS xed
    FROM sys.dm_xe_session_targets AS xet
         INNER JOIN sys.dm_xe_sessions AS xe
             ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'Backup trace'),
b AS (SELECT d.n.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
           ISNULL(db.name, d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)')) AS database_name,
           d.n.value('(data[@name="trace_message"]/value)[1]', 'varchar(4000)') AS trace_message
    FROM a
CROSS APPLY xed.nodes('/RingBufferTarget/event') AS d(n)
         LEFT OUTER JOIN master.sys.databases AS db
             ON db.physical_database_name = d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)'))
SELECT * FROM b;

以下屏幕截图显示了上一个查询的输出示例：

在本示例中，在 2 小时 30 分钟内自动备份了 5 个数据库，XEvent 会话中有 130 个条目。

详细跟踪

以下 T-SQL 代码查询详细的 XEvent 会话并返回数据库的名称，以及完整备份、差异备份和日志备份的启动和完成。

WITH
a AS (SELECT CAST (xet.target_data AS XML) AS xed
    FROM sys.dm_xe_session_targets AS xet
         INNER JOIN sys.dm_xe_sessions AS xe
             ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'Verbose backup trace'),
b AS (SELECT d.n.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
           ISNULL(db.name, d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)')) AS database_name,
           d.n.value('(data[@name="trace_message"]/value)[1]', 'varchar(4000)') AS trace_message
    FROM a
CROSS APPLY xed.nodes('/RingBufferTarget/event') AS d(n)
         LEFT OUTER JOIN master.sys.databases AS db
             ON db.physical_database_name = d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)'))
SELECT * FROM b;

下面的屏幕截图展示了 XEvent 会话中的一个完整备份示例：

下面的屏幕截图展示了 XEvent 会话中差异备份的一个输出示例：

相关内容

• 将 Azure SQL 托管实例中的数据库还原到之前的某个时间点
• 管理 Azure SQL 托管实例长期备份保留
• Azure SQL 托管实例中的自动备份