SELECT
B.MACHINE_NAME,
B.SERVER_NAME,
B.DATABASE_NAME AS DBNAME,
B.BACKUP_START_DATE,
B.BACKUP_FINISH_DATE,
CASE
WHEN B.[TYPE] = 'D' THEN 'DATABASE'
WHEN B.[TYPE] = 'I' THEN 'DIFFERENTIAL DATABASE'
WHEN B.[TYPE] = 'L' THEN 'LOG'
WHEN B.[TYPE] = 'F' THEN 'FILE OR FILEGROUP'
WHEN B.[TYPE] = 'G' THEN 'DIFFERENTIAL FILE'
WHEN B.[TYPE] = 'P' THEN 'PARTIAL'
WHEN B.[TYPE] = 'Q' THEN 'DIFFERENTIAL PARTIAL'
ELSE B.[TYPE]
END BACKUP_TYPE,
B.EXPIRATION_DATE,
B.[USER_NAME],
DATEDIFF(MINUTE,B.BACKUP_START_DATE ,B.BACKUP_FINISH_DATE) AS TOTAL_TIME_IN_MINUTE,
B.RECOVERY_MODEL,
B.BACKUP_SIZE/(1024 * 1024 * 1024) AS TOTAL_SIZE_GB,
BF.PHYSICAL_DEVICE_NAME AS LOCATION
FROM
MSDB.DBO.BACKUPSET AS B
INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY AS BF
ON B.MEDIA_SET_ID=BF.MEDIA_SET_ID
ORDER BY
B.BACKUP_START_DATE DESC
GO
B.MACHINE_NAME,
B.SERVER_NAME,
B.DATABASE_NAME AS DBNAME,
B.BACKUP_START_DATE,
B.BACKUP_FINISH_DATE,
CASE
WHEN B.[TYPE] = 'D' THEN 'DATABASE'
WHEN B.[TYPE] = 'I' THEN 'DIFFERENTIAL DATABASE'
WHEN B.[TYPE] = 'L' THEN 'LOG'
WHEN B.[TYPE] = 'F' THEN 'FILE OR FILEGROUP'
WHEN B.[TYPE] = 'G' THEN 'DIFFERENTIAL FILE'
WHEN B.[TYPE] = 'P' THEN 'PARTIAL'
WHEN B.[TYPE] = 'Q' THEN 'DIFFERENTIAL PARTIAL'
ELSE B.[TYPE]
END BACKUP_TYPE,
B.EXPIRATION_DATE,
B.[USER_NAME],
DATEDIFF(MINUTE,B.BACKUP_START_DATE ,B.BACKUP_FINISH_DATE) AS TOTAL_TIME_IN_MINUTE,
B.RECOVERY_MODEL,
B.BACKUP_SIZE/(1024 * 1024 * 1024) AS TOTAL_SIZE_GB,
BF.PHYSICAL_DEVICE_NAME AS LOCATION
FROM
MSDB.DBO.BACKUPSET AS B
INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY AS BF
ON B.MEDIA_SET_ID=BF.MEDIA_SET_ID
ORDER BY
B.BACKUP_START_DATE DESC
GO
No comments:
Post a Comment