99 lines
2.5 KiB
Transact-SQL
99 lines
2.5 KiB
Transact-SQL
/*
|
|
Server: ssunb008vm01.sunstore.ch
|
|
Format: GCM
|
|
Business: TPPHAR
|
|
type: DEVE
|
|
Version: 23.1.10015.00066
|
|
|
|
31.10.2022, TSC
|
|
*/
|
|
SET XACT_ABORT ON;
|
|
SET NOCOUNT ON;
|
|
|
|
IF OBJECT_ID('tempdb..#bkps') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE #bkps;
|
|
END;
|
|
|
|
SELECT DISTINCT
|
|
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SERVER,
|
|
bs.[database_name],
|
|
bs.backup_start_date,
|
|
bs.backup_finish_date,
|
|
bs.backup_size,
|
|
bs.[name] AS backupset_name,
|
|
bs.[description],
|
|
CASE
|
|
WHEN bs.TYPE = 'D' THEN 'Full'
|
|
WHEN bs.TYPE = 'I' THEN 'Differential'
|
|
WHEN bs.TYPE = 'L' THEN 'Log'
|
|
WHEN bs.TYPE = 'F' THEN 'File or filegroup'
|
|
WHEN bs.TYPE = 'G' THEN 'Differential file'
|
|
WHEN bs.TYPE = 'P' THEN 'Partial'
|
|
WHEN bs.TYPE = 'Q' THEN 'Differential partial'
|
|
ELSE 'Unknown' END AS backup_type
|
|
,ROW_NUMBER()OVER(PARTITION BY bs.[database_name], bs.TYPE ORDER BY bs.backup_start_date) AS rid
|
|
INTO #bkps
|
|
FROM msdb.[dbo].backupset bs
|
|
WHERE (CONVERT(DATETIME, bs.backup_start_date, 102) >= DATEADD(DAY, -7, CURRENT_TIMESTAMP))
|
|
AND bs.[name] LIKE '%commvault%'
|
|
AND bs.[database_name] NOT IN ('model','msdb','master','tempdb','HCITools')
|
|
/*AND (
|
|
bs.database_name LIKE 'arizona%'
|
|
OR bs.database_name LIKE 'activepos%'
|
|
)
|
|
*/
|
|
;
|
|
|
|
WITH chkFull AS (
|
|
SELECT b.SERVER
|
|
,b.[database_name]
|
|
,b.backup_start_date
|
|
,b.backup_finish_date
|
|
,b.backup_size
|
|
,b.backupset_name
|
|
,b.[description]
|
|
,b.backup_type
|
|
,b.rid
|
|
,hiera = 1
|
|
,CAST(NULL AS INT) AS delta_min
|
|
FROM #bkps b
|
|
WHERE b.rid = 1
|
|
|
|
UNION ALL
|
|
|
|
SELECT b.SERVER
|
|
,b.[database_name]
|
|
,b.backup_start_date
|
|
,b.backup_finish_date
|
|
,b.backup_size
|
|
,b.backupset_name
|
|
,b.[description]
|
|
,b.backup_type
|
|
,b.rid
|
|
,p.hiera + 1 AS hiera
|
|
,DATEDIFF(MINUTE, p.backup_start_date, b.backup_start_date) AS delta_min
|
|
FROM chkFull p
|
|
INNER JOIN #bkps b
|
|
ON b.[database_name] = p.[database_name]
|
|
AND b.backup_type = p.backup_type
|
|
AND b.rid = p.rid + 1
|
|
)
|
|
|
|
SELECT
|
|
f.[database_name]
|
|
, f.backup_type
|
|
, SUM(CAST(CAST(f.delta_min AS NUMERIC(12,2)) / 60 AS NUMERIC(12,2))) / COUNT(1) AS mean_delta_hour
|
|
FROM chkFull f
|
|
WHERE 1=1
|
|
GROUP BY f.[database_name], f.backup_type
|
|
OPTION(MAXRECURSION 5000);
|
|
|
|
|
|
SELECT *
|
|
FROM #bkps b
|
|
WHERE b.backup_type = 'log'
|
|
AND b.[database_name]='arizona'
|
|
ORDER BY b.[database_name] ASC, b.rid ASC
|
|
;
|