MS SQL SERVER – Finding Backup status

–SQL SERVER – Finding Last Backup Time for All Database

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),’-‘) AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name =
GROUP BY sdb.Name


— The following T-SQL Statement lists all of the databases in the server and the last day the backup happened.

case when MAX(b.backup_finish_date) is NULL then ‘No Backup’ else convert(varchar(100),
MAX(b.backup_finish_date)) end AS last_backup_finish_date
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON = b.database_name AND b.type = ‘D’
WHERE db.database_id NOT IN (2)


— The following T-SQL statement gets all the information related to the current backup location from the msdb database.

SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily

Leave a Reply

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

You are commenting using your 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.