Sql Help Urgent

[Bairagi From Bapatla]

SELECT 

s.database_name,

m.physical_device_name,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

s.backup_start_date,

s.backup_finish_date,

CASE s.[type]

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END AS BackupType,

s.server_name,

s.recovery_model

FROM msdb.dbo.backupset s

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

where type = 'D' and server_name = 'server' 

 

ORDER BY s.database_name,backup_start_date DESC, backup_finish_date

GO

 

dheeni result lo only naku max (backup_start_date) and max (backup_finish_date) aa records ravali... migathavi avasaram ledu so filter ela add cheyalo cheppandi please

 

[Bairagi From Bapatla]

ltt

[papacha]

Can you try writing the CASE function in where condition or remove if you actually don't need it ?

[Suhaas]

SELECT
max(s.backup_start_date),
max(s.backup_finish_date)
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
where type = 'Full' and server_name = 'server'
GO

[Bairagi From Bapatla]

naku result set lo okko db ki oka record ee undali but start sate and end date max ayi undali

 

[Bairagi From Bapatla]

SELECT
max(s.backup_start_date),
max(s.backup_finish_date)
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
where type = 'Full' and server_name = 'server'
GO

baa i need db name and physical device name also

[Osama Bin Gandhi]

idi try cheyyi oka sari

 

SELECT 

s.database_name,

m.physical_device_name,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

s.backup_start_date,

s.backup_finish_date,

CASE s.[type]

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END AS BackupType,

s.server_name,

s.recovery_model

FROM msdb.dbo.backupset s

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

where backup_start_date=max (backup_start_date) and backup_finish_date=max (backup_finish_date) 

Group By (all columns needed)

Having  type = 'D' and server_name = 'server' 

ORDER BY s.database_name,backup_start_date DESC, backup_finish_date

GO

[Suhaas]

SELECT

T.database_name,
T.physical_device_name,
T.backup_finish_date,
T.backup_finish_date

FROM (

 

SELECT
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
s.backup_start_date,
s.backup_finish_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model,
ROW_NUMBER() OVER (PARTITION BY s.database_name order by s.backup_start_date,s.backup_finish_date desc) AS RN
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
where type = 'D' and server_name = 'server'

)

T WHERE RN  = 1

[Bairagi From Bapatla]

SELECT

T.database_name,
T.physical_device_name,
T.backup_finish_date,
T.backup_finish_date

FROM (

 

SELECT
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
s.backup_start_date,
s.backup_finish_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model,
ROW_NUMBER() OVER (PARTITION BY s.database_name order by s.backup_start_date,s.backup_finish_date desc) AS RN
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
where type = 'D' and server_name = 'server'

)

T WHERE RN  = 1

thanks baa will try

[pandugadu999]

WITH  xyz as (

SELECT
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
s.backup_start_date,
s.backup_finish_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
where type = 'D' and server_name = 'server' )


select * from xyz where backup_start_date = (select max(backup_start_date) from xyz)

union

select * from xyz where backup_finish_date = (select max(backup_finish_date) from xyz)

 
ORDER BY database_name,backup_start_date DESC, backup_finish_date
GO

[Bairagi From Bapatla]

WITH  xyz as (

SELECT
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
s.backup_start_date,
s.backup_finish_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
where type = 'D' and server_name = 'server' )


select * from xyz where backup_start_date = (select max(backup_start_date) from xyz)

union

select * from xyz where backup_finish_date = (select max(backup_finish_date) from xyz)

 
ORDER BY database_name,backup_start_date DESC, backup_finish_date
GO

ila chesthe only okka record vasthadi..

[pandugadu999]

ila chesthe only okka record vasthadi..

 

akkada enni max values unte avanni vastay,,,both for start date and finish date

[Suhaas]

ila chesthe only okka record vasthadi..

 

Neenu last lo  ichindi work avvaledha bhayya?
 

[Bairagi From Bapatla]

akkada enni max values unte avanni vastay,,,both for start date and finish date

naku oke okka record vachindi bhayya... 
for example db01 - bkp date 04/02

db02 - 04/01

db02 - 03/31

db03 - 04/01

 

result lo db01, db02,db03 3 records ravali but nee query ki db01 okkate vasthundi

[Bairagi From Bapatla]

Neenu last lo  ichindi work avvaledha bhayya?
 

nee query work ayyindi baa.. got the expected results... thanq so much baa