監視 Azure SQL 受控執行個體的備份活動

適用於：Azure SQL 受控執行個體

本文說明如何查詢 msdb 資料庫或設定擴充事件 (XEvent) 工作階段，以監視 Azure SQL 受控執行個體的備份活動。

概觀

Azure SQL 受控執行個體會將備份資訊儲存在 msdb 資料庫中，並且會在備份活動進行期間發出事件 (也稱為擴充事件或 XEvent)，以供報告之用。 設定 XEvent 工作階段，以追蹤 msdb 資料庫內的備份狀態、備份類型、大小、時間和位置等資訊。 這項資訊可與備份監視軟體整合，也可用於企業稽核的用途。

企業稽核可能需要成功備份、備份時間，以及備份持續時間的證明。

查詢 msdb 資料庫

若要檢視備份活動，請從使用者定義的資料庫執行下列查詢：

SELECT TOP (30) bs.machine_name, bs.server_name, DB_NAME(DB_ID(bs.database_name)) AS [Database Name], bs.recovery_model,
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), (CONVERT (FLOAT, bs.backup_size) /
CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio], bs.has_backup_checksums, bs.is_copy_only, bs.encryptor_type,
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_device_name AS [Backup Location], 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 DB_ID(bs.database_name) = DB_ID()
AND bs.[type] = 'D' 
ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);

設定 XEvent 工作階段

使用擴充事件 backup_restore_progress_trace 來記錄 SQL 受控執行個體備份的進度。 視需要修改 XEvent 工作階段，以追蹤您對業務感興趣的資訊。 這些 T SQL 程式碼片段會將 XEvent 工作階段儲存在信號緩衝區中，但也可以寫入 Azure Blob 儲存體。 在信號緩衝區中儲存資料的 XEvent 工作階段限制大約為　1000 則訊息，因此只能用來追蹤最近的活動。 此外，在容錯移轉時，信號緩衝區資料會遺失。 因此，針對備份的歷程記錄，請改為寫入事件檔。

簡單追蹤

設定簡單的 XEvent 工作階段，以擷取完整備份的簡單事件。 此指令碼會收集資料庫的名稱、處理的總位元組數，以及備份完成的時間。

使用 Transact-SQL (T-SQL) 來設定簡單的 XEvent 工作階段：

CREATE EVENT SESSION [Simple 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 [Simple backup trace] ON SERVER
STATE = start;

詳細資訊追蹤

設定詳細的 XEvent 工作階段，以追蹤更多有關備份活動的詳細資料。 此指令碼會同時擷取完整、差異和記錄備份的開始和完成。 由於此指令碼較詳細，其填滿信號緩衝區的速度會更快，因此，項目的回收速度可能比簡單的指令碼更快。

使用 Transact-SQL (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 工作階段之後，您可以使用 SQL transact-sql (T SQL) 來查詢信號緩衝區結果，並監視備份的進度。 XEvent 啟動之後，會收集所有備份事件，因此大約每隔 5-10 分鐘就會將項目新增至工作階段。

簡單追蹤

下列 Transact-SQL程式碼會查詢簡單的 XEvent 工作階段，並傳回資料庫的名稱、處理的位元組總數，以及備份完成的時間：

WITH
a AS (SELECT xed = CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet
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') d(n)
LEFT JOIN master.sys.databases db
ON db.physical_database_name = d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)'))
SELECT * FROM b

下列螢幕擷取畫面顯示上述查詢的輸出範例：

在此範例中，系統會在 2 小時和 30 分鐘的期間自動備份五個資料庫，且在 XEvent 工作階段中有 130 個項目。

詳細資訊追蹤

下列 Transact-SQL (T-SQL) 程式碼會查詢詳細 XEvent 工作階段，並傳回資料庫的名稱，以及完整、差異和記錄備份的開始和完成。

WITH
a AS (SELECT xed = CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet
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') d(n)
LEFT JOIN master.sys.databases db
ON db.physical_database_name = d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)'))
SELECT * FROM b

下列螢幕擷取畫面顯示 XEvent 工作階段中完整備份的範例：

下列螢幕擷取畫面顯示 XEvent 工作階段中差異備份的輸出範例：

下一步

備份完成後，您就可以還原至某個時間點，或設定長期保留原則。

若要深入了解，請參閱自動備份。