You are here:Home » tsql » Backup Information

Backup Information

select bs.server_name,bs.database_name,bs.backup_start_date,
case bs.type when 'D' then 'Full Backup' when 'I' then 'Diff Backup' when 'L' then 'Log Backup' end as BackupType,
bmf.physical_device_name
from msdb..backupset bs
inner join msdb..backupmediafamily bmf
on (bs.media_set_id=bmf.media_set_id)
order by bs.backup_start_date desc



-------------------

--For without backup device

select bs.server_name,bs.database_name,bs.backup_start_date,
case bs.type when 'D' then 'Full Backup' when 'I' then 'Diff Backup' when 'L' then 'Log Backup' end as BackupType,
bmf.physical_device_name
from msdb..backupset bs
inner join msdb..backupmediafamily bmf
on (bs.media_set_id=bmf.media_set_id)
where device_type =2
order by bs.backup_start_date desc

----For with backup device

select bs.server_name,bs.database_name,bs.backup_start_date,
case bs.type when 'D' then 'Full Backup' when 'I' then 'Diff Backup' when 'L' then 'Log Backup' end as BackupType,
bmf.logical_device_name,bmf.physical_device_name,bs.position
from msdb..backupset bs
inner join msdb..backupmediafamily bmf
on (bs.media_set_id=bmf.media_set_id)
where bmf.device_type = 102
order by bs.backup_start_date desc