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

适用于:Azure SQL 托管实例

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

概述

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

备注

查询 msdb 系统表(如 dbo.backupmediasetdbo.backupset)时,会看到与加密相关的字段,指示备份文件未加密。 此状态仅反映引擎级加密。 所有自动备份 都是静态加密的

配置 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;

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

XEvent 输出的屏幕截图。

在本示例中,在 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 输出的屏幕截图。

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

显示差异备份的 XEvent 输出的屏幕截图。