在Grafana上绘制SQL Server备份集看板

SELECT CONVERT(VARCHAR, GETDATE(), 23) AS 'date'  
UNION ALL  
SELECT CONVERT(VARCHAR, DATEADD(MINUTE, -timeList.sj * 1460, GETDATE()), 23) AS 'datetime'  
FROM (  
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS sj  
    FROM sys.objects  
) AS timeList;

SELECT  count(bs.backup_finish_date)
FROM   msdb.dbo.backupmediafamily bf 
       INNER JOIN msdb.dbo.backupset bs 
               ON bf.media_set_id = bs.media_set_id 
               where 1=1 
               and bs.type='D' 
               and CONVERT(DATE, backup_finish_date) = '${day}' 

SELECT  
CAST(DATEDIFF(SECOND,  min(bs.Backup_Start_Date ), max(bs.backup_finish_date)) AS DECIMAL(18,1)) / 3600.0 as [备份耗时(小时)]
FROM   msdb.dbo.backupmediafamily bf 
       INNER JOIN msdb.dbo.backupset bs 
               ON bf.media_set_id = bs.media_set_id 
               where 1=1 
               and bs.type='D' 
               and CONVERT(DATE, backup_finish_date) = '${day}' 

SELECT sum( bs.backup_size)
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bf
    ON bs.[media_set_id] = bf.[media_set_id]
INNER JOIN msdb.dbo.backupmediaset bms
    ON bs.[media_set_id] = bms.[media_set_id]
WHERE CONVERT(DATE, backup_finish_date) = '${day}' and bs.type = 'D'

SELECT
TOP 60
CONVERT(DATE, backup_finish_date) as [备份日期] ,
-- datediff (MINUTE, min(bs.Backup_Start_Date ), max(bs.backup_finish_date)) as [备份耗时(分钟)],
CAST(DATEDIFF(SECOND,  min(bs.Backup_Start_Date ), max(bs.backup_finish_date)) AS DECIMAL(18,1)) / 3600.0 as [备份耗时(小时)],
count(bs.backup_size) as [备份的库数量],
sum(bs.backup_size)/1024/1024/1024/1024.0 as [备份文件总体积(TB)]
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bf
    ON bs.[media_set_id] = bf.[media_set_id]
INNER JOIN msdb.dbo.backupmediaset bms
    ON bs.[media_set_id] = bms.[media_set_id]
    where  bs.type = 'D'
group by  CONVERT(DATE, backup_finish_date) 
order by   CONVERT(DATE, backup_finish_date) desc

SELECT
CONVERT(CHAR(100), Serverproperty('Servername')) AS ServerName
      ,bs.database_name AS  Database_Name
      ,bs.recovery_model AS  Recovery_Model
      ,bs.is_damaged AS  Is_Damaged
      ,bs.backup_start_date AS  Backup_Start_Date
      ,bs.backup_finish_date AS  Backup_Finish_Date
      ,bs.expiration_date AS  Expiration_Date
      ,CASE bs.type 
         WHEN 'D' THEN 'Database' 
         WHEN 'L' THEN 'Log' 
       END AS Backup_Type
      ,bs.backup_size/1024/1024/1024 AS  [Backup_Size(GB)]
      ,bs.compressed_backup_size/1024/1024/1024 AS [Compressed_Backup_Size(GB)]
      ,bs.compressed_backup_size/bs.backup_size AS Compressed_Rate
--      ,bf.logical_device_name
 ,bf.physical_device_name
  --    ,bs.name AS Backupset_Name
  --    ,bs.description
FROM   msdb.dbo.backupmediafamily bf 
       INNER JOIN msdb.dbo.backupset bs 
               ON bf.media_set_id = bs.media_set_id 
               where 1=1 and  CONVERT(DATE, backup_finish_date) = '${day}'
ORDER  BY  bs.backup_finish_date desc;