PlateSpin备份时SQLServer的信息介绍

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;