sql server 每次在备份的时候都会把相关信息记录到msdb库下面的表里面,为了更直观的查看备份的情况,我们可以在grafana上配置相关图表进行展示。
效果如下:
用到的sql如下:
生成日期序列(左上角)
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;
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。