Pete Schott,

I agree with you. I got a number of emails to include Backup type in this script.

Below is the revised script to take care of this issue.

Select a.name,backup_type, Backup_Date from master.dbo.sysdatabases a
left join
 ( select database_name, Backup_Type = Case type  when 'D' then  'Database'
       When 'I' then  'Database Differential'
       When 'L' then  'Log'
       When 'F' then  'File or Filegroup'
       Else
       'Error'
          End,
   max(backup_finish_date) backup_date
  from msdb.dbo.backupset where backup_finish_date <= getdate()
  group by database_name,Type
)
B
on a.name=b.database_name

I have problems with your script.

I cant find the the cmax and backup_date columns.

My SQL 2000 Database hasn't this objects.

I dont know what I shoud do.

I am  not still a newbee but i'am new in this forum.

Kind Regards

Michael

All,

Regarding CMAX function, this is actualy a typoerror... please read it MAX function.

please use this script

Select a.name,backup_type, Backup_Date from master.dbo.sysdatabases a
left join
 ( select database_name, Backup_Type = Case type  when 'D' then  'Database'
       When 'I' then  'Database Differential'
       When 'L' then  'Log'
       When 'F' then  'File or Filegroup'
       Else
       'Error'
          End,
   max(backup_finish_date) backup_date
  from msdb.dbo.backupset where backup_finish_date <= getdate()
  group by database_name,Type
)
B
on a.name=b.database_name