SQL - T-SQL How long does the DB backup take

Found this useful query with regards looking for how long the SQL backup takes. Baselining the backups to spot when you started having problems.

USE <DBNAME>
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_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 s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

SQL - T-SQL for last Full and Log Backup

I wanted a script to be able to list when the last backup occured on the SQL Databases that we had to check first thing as part of morning checks.

I found a number of scripts but this one met my needs:

SELECT a.name AS [Database Name],a.recovery_model_Desc AS [Recovery Model],
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = 'D' and a.name=b.database_name) AS [Full Backup],
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = 'L' and a.name=b.database_name) AS [Log Backup]
FROM master.sys.databases a

As you can see from the screen shot below of a brand spanking new SQL Express install the output also gives the Recovery Model and well as Full and Log backups.