TheSQLGuru
3/23/2007 2:48:00 PM
How's this:
select database_name, backup_start_date, backup_finish_date,
'Backup_Type' = CASE type
when 'D' then 'Full_DB_Backup'
when 'L' then 'Transaction_Log_Backup'
when 'I' then 'Diffrential_Backup'
when 'F' then 'File_Filegroup_Backup'
Else 'Unknown'
END
from dbo.backupset bs1 (nolock)
where database_name NOT IN('master','model','msdb')
and (bs1.backup_set_id = (select max(bs2.backup_set_id)
from dbo.backupset bs2 (nolock)
where bs2.database_name = bs1.database_name
and bs2.type = 'd')
OR
bs1.backup_set_id = (select max(bs3.backup_set_id)
from dbo.backupset bs3 (nolock)
where bs3.database_name = bs1.database_name
and bs3.type = 'l'))
ORDER BY database_name, backup_start_date DESC
NOTE: You should put an index on database_name if you have very many rows
in the backupset table to improve the efficiency of the query. I have some
similar logic in one of my client's maintenance sprocs and it helped
tremendously.
Also, note the use of NOLOCKS on the system tables. And remove the order by
if not absolutely necessary for your output.
--
TheSQLGuru
President
Indicium Resources, Inc.
"nj" <njvds.o11@gmail.com> wrote in message
news:1174655673.169982.294850@p15g2000hsd.googlegroups.com...
> Currently i am running below given query to get latest backup
> information from backupset table
>
> select TOP 25 database_name,MAX(backup_start_date) as
> Backup_Start_Date ,
> backup_finish_date,
> 'Backup_Type' = CASE type
> when 'D' then 'Full_DB_Backup'
> when 'L' then 'Transaction_Log_Backup'
> when 'I' then 'Diffrential_Backup'
> when 'F' then 'File_Filegroup_Backup'
> Else 'Unknown'
> END
> from dbo.backupset
> where database_name NOT IN('master','model','msdb')
> GROUP BY database_name , Backup_Finish_Date , type
> ORDER BY backup_start_date DESC
>
> It gives me 25 rows for all type of backup. Now i want to return only
> 2 rows. One for latest Full backup and second for latest transaction
> log backup. I can do that by writing seperated query for each type and
> running in one batch. But i know this can be done in one query. How..
>
> Please suggest.
>
> NJ
>