Backup History

If you want to see more than just when the last backup occurred. Here is a script for backup history with two bonus features.

  • Backup compression ratio – CompressionRatio
  • Backup speed – Backup_MiB_S
--http://jongurgul.com/blog/backup-history/
SELECT
 bus.[database_name] [DatabaseName]
,bus.[type] [Type]
,bus.[backup_start_date] [BackupStartDate]
,bus.[backup_finish_date] [BackupFinishDate]

,CONVERT(DECIMAL(15,3),(bus.[backup_size]/1048576)) [Size_MiB]
,CONVERT(DECIMAL(15,3),(bus.[compressed_backup_size]/1048576)) [CompressedSize_MiB] --SQL2008
,LTRIM(STR((bus.[backup_size])/(bus.[compressed_backup_size]),38,3))+':1' [CompressionRatio] --SQL2008

,DATEDIFF(SECOND,bus.[backup_start_date],bus.[backup_finish_date]) [Duration_S]
,CONVERT(DECIMAL(15,3),(bus.[backup_size]/COALESCE(NULLIF(DATEDIFF(SECOND,bus.[backup_start_date],bus.[backup_finish_date]),0),1))/1048576) [Backup_MiB_S]
,bumf.[physical_device_name]

,bus.[first_lsn] [FirstLSN]
,bus.[last_lsn] [LastLSN]
,bus.[checkpoint_lsn] [CheckpointLSN]
,bus.[database_backup_lsn] [DatabaseBackupLSN]
,bus.[is_copy_only] [IsCopyOnly]
,bus.[differential_base_guid] [DifferentialBaseGUID]
,bus.[differential_base_lsn] [DifferentialBaseLSN]
,bus.[first_recovery_fork_guid] [FirstRecoveryForkID]
,bus.[last_recovery_fork_guid] [LastRecoveryForkID]
,bus.[fork_point_lsn] [ForkPointLSN]

,bus.[user_name] [UserName]
,bus.[compatibility_level] [CompatibilityLevel]
,bus.[database_version] [DatabaseVersion]
,bus.[collation_name] [CollationName]
--SELECT *
FROM [msdb].[dbo].[backupset] bus
INNER JOIN [msdb].[dbo].[backupmediafamily] bumf 
ON bus.[media_set_id] = bumf .[media_set_id]
ORDER BY bus.[backup_start_date] DESC

Last Backup Occurred

This script will show when the last backup occurred as well as the backup type for each database. This covers all the backup types available and pivots the output into a simple and easily readable format.
It will state the reason; if any, for log reuse wait and the database recovery model in use.
Depending on your requirements you may also wish to alter the script to exclude copy only backups. This can be done by adding the following:

SELECT [database_name],[backup_finish_date],[type]
FROM msdb..backupset
WHERE [is_copy_only] <> 1

--http://jongurgul.com/blog/last-backup-occurred/
SELECT
 QUOTENAME(d.[name]) [DatabaseName]
,SUSER_SNAME(d.[owner_sid]) [DatabaseOwner]
,d.[compatibility_level] [Compatibility]
,d.[collation_name] [CollationName]
,d.[is_read_only] [IsReadOnly]
,d.[is_auto_close_on] [IsAutoClose]
,d.[is_auto_shrink_on] [IsAutoShrink]
,d.[recovery_model_desc] [RecoveryModel]
,d.[page_verify_option_desc] [PageVerify]
,d.[state_desc] [State]
,d.[log_reuse_wait_desc] [LogReuse]
,pivbus.[D] [Database]
,pivbus.[I] [DifferentialDatabase]
,pivbus.[L] [Log]
,pivbus.[F] [FileOrFilegroup]
,pivbus.[G] [DifferentialFile]
,pivbus.[P] [Partial]
,pivbus.[Q] [DifferentialPartial]
FROM sys.databases d
LEFT OUTER JOIN
(
SELECT
piv.[database_name],[D],[I],[L],[F],[G],[P],[Q]
FROM
(
SELECT [database_name],[backup_finish_date],[type]
FROM msdb..backupset
) bus PIVOT
(MAX([backup_finish_date]) FOR [type] IN ([D],[I],[L],[F],[G],[P],[Q])) piv
) pivbus ON d.[name] = pivbus.[database_name]