本文介绍如何通过查询 数据库或配置扩展事件 (XEvent) 会话来监视 msdb
的备份活动。
Azure SQL 托管实例在 msdb 数据库中 存储备份信息,并在备份活动期间发出事件(也称为 扩展事件或 XEvents),可用于报告。 配置 XEvent 会话可以跟踪 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.backupmediaset
或 dbo.backupset
)时,会看到与加密相关的字段,指示备份文件未加密。 此状态仅反映引擎级加密。 所有自动备份 都是静态加密的。
使用扩展事件 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 会话中差异备份的一个输出示例: