Wednesday 5 June 2013

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

No comments:

Post a Comment