Find Last BackUp Date Of All Databases on your Server

Whenever you perform a backup, SQL Server 2005 updates the following tables: msdb.dbo.backupfile, msdb.dbo.backupmediafamily, msdb.dbo.backupmediaset and msdb.dbo.backupset.

You can use these tables to retrieve backup information about your database. In the following query, a join is made between sys.sysdatabase and msdb.dbo.backupset to fetch the database name, last backup date and the user who took the backup.

SQL Server 2005 Solution

SELECT 
T1.Name as DatabaseName, 
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as LastBackUpTaken,
COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),'NA') as UserName
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name

Note: The sys.sysdatabase contains one row for each database in that particular instance of Microsoft SQL Server 2005. When SQL Server is first installed, sysdatabases contains entries only for the master, model, msdb, and tempdb databases. You can read more about the sysdatabase over here

I would encourage you to take a look at the msdb.dbo.backupset table in particular, to find out other information like backup start date, backup end date, backup size and so on.
___________________________________________________________________________________________________________________

Page Created By: Suprotim Agarwal, April 14, 2008