July 11, 2012 at 1:20 am
Hi All
Is there a script I can use to display each database in my instance along with a coulmn for the last Full backup and a column for the last log backup.
I've been playing around with this but I don't seem to be getting anywhere
select database_name,
MAX(backup_finish_date)
from msdb.dbo.backupset
group by database_name
Thanks
July 11, 2012 at 1:39 am
SELECT database_name ,
MAX(CASE type
WHEN 'D' THEN backup_finish_date
ELSE NULL
END) AS DatabaseBackup ,
MAX(CASE type
WHEN 'L' THEN backup_finish_date
ELSE NULL
END) AS LogBackup
FROM msdb.dbo.backupset
GROUP BY database_name
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2012 at 1:43 am
GilaMonster (7/11/2012)
SELECT database_name ,MAX(CASE type
WHEN 'D' THEN backup_finish_date
ELSE NULL
END) AS DatabaseBackup ,
MAX(CASE type
WHEN 'L' THEN backup_finish_date
ELSE NULL
END) AS LogBackup
FROM msdb.dbo.backupset
GROUP BY database_name
Thank you!
July 12, 2012 at 7:01 am
GilaMonster (7/11/2012)
SELECT database_name ,MAX(CASE type
WHEN 'D' THEN backup_finish_date
ELSE NULL
END) AS DatabaseBackup ,
MAX(CASE type
WHEN 'L' THEN backup_finish_date
ELSE NULL
END) AS LogBackup
FROM msdb.dbo.backupset
GROUP BY database_name
Hi
Can you explain how a CASE statement used in this way works?
I'm used to using CASE statements as below:
backup_type = case when 'D' then 'FULL' end
I've never seen the then statement referencing another column
Thanks
July 12, 2012 at 7:46 am
What follows the THEN is just an expression, any combination of parameters, literals, columns, variables, just as a normal column in the SELECT.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply