Script: Last x Full Backup History

It's a very common task that we have to query backup timings and other bits of info from msdb. 

Now its pretty straight forward to select this data for a particular database or use the MAX function for example to return the last backup but how do we get the last 5 FULL backups per database? 

You might want to check for spikes in backup duration for example and for this particular bit of functionality the RANK and PARTITION functions in T-SQL come in really handy. 

SELECT database_name, backup_start_date, backup_finish_date, backup_duration 
    FROM (
        SELECT database_name, backup_start_date, backup_finish_date,
DATEDIFF(MINUTE, backup_start_date, backup_finish_date) AS backup_duration,
RANK() OVER (PARTITION BY database_name -- "group my data"
                ORDER BY backup_start_date DESC ) AS Rank -- sorting by backup start date descending
        FROM msdb.dbo.backupset WHERE [type] = 'D'  -- full backups
        ) rs 
WHERE RANK <= 3 -- controls the top x of returned records for each "group" of data

So now we can take the output of this script and include it in Excel or SSRS to provide some trend analysis charts on backup timings etc. Very useful part of your monitoring script collection.

Some things to bear in mind, the script will include backups taken with the COPY_ONLY option and if you perform maintenance on backup history removing data over x weeks old, make sure the script accommodates this.