Need to get current DB Backup Set up list in PIVOT STYLE

Question

Post reply

Need to get current DB Backup Set up list in PIVOT STYLE

poratips

Hall of Fame

Points: 3994
More actions
December 1, 2014 at 5:51 pm

#295519

Hi,
I need to list the current DB Backup Set up list in PIVOT STYLE.
I need following way:
Database_NameFULL - DDIFF - ILOG - L
DB1DL
DB2D
modelDL
DB3DIL
msdbD

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

poratips Hall of Fame Points: 3994 More actions · Answer 1

I got it, Thanks.

;WIth mycte as (SELECT

s.database_name,

s.[type]

,row_number() Over(Partition by s.[type] Order by s.backup_finish_date DESC ) RN

FROM msdb.dbo.backupset s

INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id)

Select database_name, max(case when [type]='D' Then 'D' Else '' End) 'FULL--D'

, max(case when [type]='I' Then 'I' Else '' End) 'DIFF - I'

, max(case when [type]='L' Then 'L' Else '' End) 'LOG - L'

from mycte

--Where rn=1

Group by database_name

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 2

poratips (12/1/2014)
I got it, Thanks.
;WIth mycte as (SELECT
s.database_name,
s.[type]
,row_number() Over(Partition by s.[type] Order by s.backup_finish_date DESC ) RN
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id)
Select database_name, max(case when [type]='D' Then 'D' Else '' End) 'FULL--D'
, max(case when [type]='I' Then 'I' Else '' End) 'DIFF - I'
, max(case when [type]='L' Then 'L' Else '' End) 'LOG - L'
from mycte
--Where rn=1
Group by database_name

I'm not sure that you do have it. Let's say that your FULL backup ran yesterday, that your first DIF ran today, and the last LOG file backup ran a week ago but are supposed to run once per hour, what do you think that the query above will show? I'm thinking that it's going to look like everything is fine when it's not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)