–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 = sdb.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.

SELECT db.name,
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 db.name = b.database_name AND b.type = ‘D’
WHERE db.database_id NOT IN (2)
GROUP BY db.name
ORDER BY 2 DESC

——————————————————————————————-

— 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