[SQL Server] Afficher la liste des sauvegardes
SELECT database_name,
physical_drive,
physical_name,
physical_device_name,
logical_name,
CAST(SUM(file_size) / 1048576 AS NUMERIC (10, 2)) AS Taille,
name,
user_name,
database_creation_date,
backup_start_date,
backup_finish_date,
Duree,
Type,
server_name,
machine_name,
recovery_model,
is_snapshot,
is_damaged,
has_incomplete_metadata
FROM
(
SELECT F.physical_drive,
F.physical_name,
F.logical_name,
F.file_size,
S.name,
S.user_name,
S.database_creation_date,
S.backup_start_date,
S.backup_finish_date,
CASE LEN(CAST(DATEDIFF(second, S.backup_start_date, S.backup_finish_date) / 3600 AS VARCHAR(4)))
WHEN 1 THEN '0' + CAST(DATEDIFF(second, S.backup_start_date, S.backup_finish_date) / 3600 AS VARCHAR(4))
ELSE CAST(DATEDIFF(second, S.backup_start_date, S.backup_finish_date) / 3600 AS VARCHAR(4))
END + ':' +
CASE LEN(CAST(DATEDIFF(second, S.backup_start_date, S.backup_finish_date) / 60 % 60 AS VARCHAR(2)))
WHEN 1 THEN '0' + CAST(DATEDIFF(second, S.backup_start_date, S.backup_finish_date) / 60 % 60 AS VARCHAR(2))
ELSE CAST(DATEDIFF(second, S.backup_start_date, S.backup_finish_date) / 60 % 60 AS VARCHAR(2))
END + ':' +
CASE LEN(CAST(DATEDIFF(second, S.backup_start_date, S.backup_finish_date) % 60 AS VARCHAR(2)))
WHEN 1 THEN '0' + CAST(DATEDIFF(second, S.backup_start_date, S.backup_finish_date) % 60 AS VARCHAR(2))
ELSE CAST(DATEDIFF(second, S.backup_start_date, S.backup_finish_date) % 60 AS VARCHAR(2))
END Duree,
CASE S.type
WHEN 'D' THEN 'Base de données - Complet'
WHEN 'I' THEN 'Base de données - Différentiel'
WHEN 'L' THEN 'Journal de transactions'
WHEN 'F' THEN 'Fichier ou groupe de fichiers'
WHEN 'G' THEN 'Fichier - Différentiel'
WHEN 'P' THEN 'Partiel'
WHEN 'Q' THEN 'Partiel - Différentiel'
END Type,
S.database_name,
S.server_name,
S.machine_name,
S.recovery_model,
S.is_snapshot,
S.is_damaged,
S.has_incomplete_metadata,
FAM.physical_device_name
FROM msdb.dbo.backupfile AS F
JOIN msdb.dbo.backupset AS S
ON F.backup_set_id = S.backup_set_id
JOIN msdb.dbo.backupmediafamily AS FAM
ON FAM.media_set_id = S.media_set_id
WHERE S.backup_start_date BETWEEN CAST(FLOOR(CAST(GETDATE()- 2 AS FLOAT)) AS DATETIME) AND GETDATE()
) AS SUB
GROUP BY physical_drive,
physical_name,
physical_device_name,
logical_name,
name,
user_name,
database_creation_date,
backup_start_date,
backup_finish_date,
Duree,
Type,
database_name,
server_name,
machine_name,
recovery_model,
is_snapshot,
is_damaged,
has_incomplete_metadata
ORDER BY database_name, backup_start_date