Finding the last date when the LOG/FULL/DIFF/FILEGROUP backup was taken for all the databases

Hi Guys ,
While creating a few scripts , a requirement came where I had to find the last backups (all types) taken for all the databases (except tempdb) .
Please find the script below .Hope it helps you in your daily activities .If you want to automate it for all the instances in your environment , please let me know and I can send you some more files.



/*
Script : Last_bckp.sql
Author : Abhay Chaudhary,
Date : 26th JUL, 2010
Purpose : Collecting SQL Server 2000/2005/2008 last backup taken information.
Requirements : Do a CTRL+F and change the to the DB where you want to
create the object.
Suggestions : hi_abhay78@yahoo.co.in
Version : 1.0
*/


USE
set nocount on
if not exists (select * from ..sysobjects where name ='bckp_types' and type ='S')
begin
create table ..bckp_types (num int identity(1,1),type varchar(1),bkp_name varchar(20))
insert into ..bckp_types (type,bkp_name) values ('D','Full backup')
insert into ..bckp_types (type,bkp_name) values ('L','Log Backup')
insert into ..bckp_types (type,bkp_name) values ('F','Filegroup backup')
insert into ..bckp_types (type,bkp_name) values ('I','Differential backup')
end
go

Declare @loop int
select @loop= max(num) from bckp_types
While (@loop !=0)
begin
Select 'last ' + bkp_name +' taken details.' from bckp_types where num=@loop
declare @bk_type varchar(1)
select @bk_type = type from bckp_types where num=@loop

SELECT s.name 'database Name',
b.backup_finish_date 'last backup date',
bmf.physical_device_name 'location of backup'
FROM master..sysdatabases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name
INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
WHERE s.name <> 'tempdb'
AND b.backup_finish_date = (SELECT MAX(backup_finish_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = @bk_type)
ORDER BY s.name

set @loop=@loop-1
end
go
Drop table ..bckp_types


Happy Learning ...
Abhay