SQL : SQL Server db backup related information

SQL Server db backup related information

SQL Server holds all db backup related information into the following tables:-

1. SELECT * FROM msdb.dbo. backupset

2. SELECT * FROM msdb.dbo.backupmediafamily

We may use the following query, to find the important backup related information

---- SQL statement start

select top 5 a.server_name, a.database_name, backup_finish_date, a.backup_size,

CASE a.[type] -- Let's decode the three main types of backup here

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

ELSE a.[type]

END as BackupType

,b.physical_device_name

from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b

on a.media_set_id = b.media_set_id

where a.database_name Like 'Paylite4Alubaf%' -- databasename

order by a.backup_finish_date desc

--- SQL statement end

SQL