Complete a Server Assessment – Backup Information

Below you will find the code to gather information about the database backups.  Simply select the text and copy to the Clipboard.

SELECT
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ‘ ‘ + ‘MB’ AS ‘BackupSize’,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ‘ ‘ + ‘Seconds’  AS ‘TimeTaken’,
s.backup_start_date,
s.backup_finish_date,
CAST(s.first_lsn AS VARCHAR(50)) AS ‘first_lsn’,
CAST(s.last_lsn AS VARCHAR(50)) AS ‘last_lsn’,
CASE s.[type]
WHEN ‘D’ THEN ‘Full’
WHEN ‘I’ THEN ‘Differential’
WHEN ‘L’ THEN ‘Transaction Log’
END AS ‘BackupType’,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE backup_start_date > (Getdate() – 45)
ORDER BY backup_start_date DESC, backup_finish_date

This code was written using the Microsoft SQL Server documentation.  If you find that this code was copied from else where, please let me know so I can give proper credit.