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]

Leave a Reply