以前寫過一篇文章IO is frozen on database xxx, No user action is required“， 主要是介紹PlateSpin在伺服器層面做DR備份時，SQL Server日誌裡面有"I/O is frozen on database xxxx"以及“I/O wa... ...
以前寫過一篇文章IO is frozen on database xxx, No user action is required“， 主要是介紹PlateSpin在伺服器層面做DR備份時，SQL Server日誌裡面有"I/O is frozen on database xxxx"以及“I/O was resumed on database xxx"信息，詳細信息參考那篇博客，這篇主要是從側面一個佐證、補充！
其實後面發現，當PlateSpin作業運行時，你會發現資料庫會msdb.dbo.backupset中會會記錄一條資料庫完整備份，備份到Virtual Device的記錄。這個也是側面一個佐證！當然更細、更深入方面的原理、知識點，由於網上PlateSpine的資料不多，自己也沒有時間去深入瞭解。這裡只是我在採集備份信息時，發現的一個細節而已，當時很是納悶，那個時間點沒有做全備、也沒有備份到虛擬設備（Virtual Device）啊？後面查找原因，才發現這個跟PlateSpin有關係。特此記錄一下！
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS servername ,
bs.database_name ,
bs.backup_start_date ,
bs.backup_finish_date ,
DATEDIFF(MINUTE,bs.backup_start_date, bs.backup_finish_date) AS backup_consume_time,
bs.expiration_date ,
CASE bs.type
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Diff Backup'
WHEN 'L' THEN 'Log Bacup'
WHEN 'F' THEN 'File Backup'
WHEN 'G' THEN 'File Diff'
WHEN 'P' THEN 'Partial Backup'
WHEN 'Q' THEN 'Partial Diff Backup'
END AS backup_type ,
CASE bf.device_type
WHEN 2 THEN 'Disk'
WHEN 5 THEN 'Tape'
WHEN 7 THEN 'Virtual Device'
WHEN 105 THEN 'permanent backup device'
END AS backup_media,
bs.backup_size/1024/1024/1024 AS [backup_size(GB)] ,
bs.compressed_backup_size/1024/1024/1024 AS [compressed_backup_size(GB)],
bf.logical_device_name ,
bf.physical_device_name ,
bs.name AS backupset_name ,
bs.first_lsn,
bs.last_lsn,
bs.checkpoint_lsn,
bs.description
FROM msdb.dbo.backupmediafamily bf
INNER JOIN msdb.dbo.backupset bs ON bf.media_set_id = bs.media_set_id
WHERE ( CONVERT(DATETIME, bs.backup_start_date, 102) >= CAST('2017-10-18 21:00' AS DATETIME))
AND ( CONVERT(DATETIME, bs.backup_start_date, 102) <= CAST('2017-10-18 23:59' AS DATETIME))
AND
bs.type='D' AND bs.database_name='HistoryData'
ORDER BY bs.database_name ,
bs.backup_finish_date;
