Search Wiki:

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
Last edited Apr 14 2008 at 8:11 PM  by SuprotimAgarwal, version 2
Comments
VenkatesanPrabu wrote  Apr 22 2008 at 11:25 AM  
Sorry, if i am wrong. But we can achieve the same by using below query,
[code]
select Database_name,
COALESCE(Convert(varchar(20), MAX(backup_finish_date), 101),'Backup Not Taken') as
LastBackUpTakenDate,
COALESCE(Convert(varchar(20), MAX(user_name), 101),'NA') as BackupTakenUser
from msdb.dbo.backupset
GROUP BY Database_name
[/code]

Regards,
Venkatesan Prabu. J

http://venkattechnicalblog.blogspot.com

SuprotimAgarwal wrote  Apr 23 2008 at 6:38 AM  
Hi,
Thanks for your comments Venkatesan.

That query of yours will return only those databases whose back up has been taken. How about displaying those whose back up has not been taken. Also the sys.sysdatabase contains some useful info like the filename which can be fetched by joining the two tables.

Updating...
Page view tracker