Here's a quick snippet to get a listing of the database backups that last occurred on a server. Most solutions provided a single backup listing, but not the brief summary of the last backup details I was looking for.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select | |
| sdb.name as DatabaseName | |
| ,coalesce(convert(varchar(12), x.backup_finish_date, 101), '-') as LastBackUpTime | |
| ,x.* | |
| from | |
| sys.sysdatabases as sdb | |
| cross apply ( | |
| select top (1) | |
| bus.type | |
| ,bus.backup_finish_date | |
| ,type_description = case bus.type | |
| when 'D' then 'Database' | |
| when 'I' then 'Differential database' | |
| when 'L' then 'Log' | |
| when 'F' then 'File or filegroup' | |
| when 'G' then 'Differential file' | |
| when 'P' then 'Partial' | |
| when 'Q' then 'Differential partial' | |
| else bus.type | |
| end | |
| ,bus.user_name | |
| ,bus.recovery_model | |
| ,bus.is_copy_only | |
| from | |
| msdb.dbo.backupset as bus | |
| where | |
| bus.database_name = sdb.name | |
| order by | |
| bus.backup_finish_date desc) as x | |
| order by | |
| LastBackUpTime desc |
by 
