T-SQL Script to Get Last Backup Dates for All Active DB’s

Script to list the last full backup time for all active user DB's.

Version: 2005+
with tmp as
select row_number() over (partition by BS.database_name order by BS.backup_set_id desc) as rowid, BS.database_name, BS.backup_finish_date
from msdb.dbo.backupset BS
inner join msdb.dbo.backupmediafamily BMF
on BS.media_set_id = BMF.media_set_id
where BS.type = 'D'
and BS.database_name not in ('master', 'msdb', 'tempdb', 'model')
select database_name, backup_finish_date as last_full_backup_date from tmp
where rowid = 1 and database_name in (select name from sys.databases where state_desc = 'online' )
order by backup_finish_date desc


About Nick Xu

Not a DBA anymore
This entry was posted in T-SQL Script and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s