SELECT destination_database_name AS 'Database Name',

restore_date AS 'Date Restored',

CASE restore_type

WHEN 'D' THEN 'Full Backup'

WHEN 'I' THEN 'Diffrential backup'

WHEN 'L' THEN 'Log Backup'

END AS 'Restored From',

CASE recovery

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

END AS 'With Recovery',

backup_finish_date AS 'Backup taken',

Cast(backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)' ,

physical_device_name AS 'Backup Location'

FROM msdb..restorehistory,

msdb..backupset,

msdb..backupmediafamily

WHERE msdb..restorehistory.backup_set_id= msdb..backupset.backup_set_id

AND msdb..backupset.media_set_id=msdb..backupmediafamily.media_set_id

AND database_name='SGCTI'

ORDER BY destination_database_name,

restore_date DESC