1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
| --https://blog.developpez.com/elsuket/p10271/snippets/obtenir_historique_sauvegardes_bases_de_
;WITH
CTE AS
(
SELECT database_name
, last_backup_date_time
, DATEDIFF(minute, last_backup_date_time, GETDATE()) AS n
, backup_type
, backup_size
, compressed_backup_size
FROM (
SELECT database_name
, MAX(backup_finish_date) AS last_backup_date_time
, CASE type
WHEN 'D' THEN 'Full Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'Transaction Log'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END AS backup_type
, backup_size
, compressed_backup_size
FROM msdb.dbo.backupset AS S
GROUP BY database_name, type, backup_size, compressed_backup_size
) AS BH
)
SELECT D.name AS database_name
, C.last_backup_date_time
, CAST(C.n / 1440 AS varchar(3)) + ' jours '
+ CAST((C.n % 1440) / 60 AS varchar(2)) + ' heures '
+ CAST(C.n % 60 AS varchar(2)) + ' minutes' AS backup_taken_ago
, C.backup_type
, C.backup_size
, C.compressed_backup_size
FROM sys.databases AS D
LEFT JOIN CTE AS C ON D.name = C.database_name
WHERE 1 = 1
--AND D.name = 'SHP' and backup_type = 'Full Database'
ORDER BY DATEDIFF(minute, last_backup_date_time, GETDATE()) |
Partager