Friday, October 18, 2013

Query to get the latest FULL backup details

 

SELECT  
cast ((backup_finish_date - backup_start_date) AS TIME(0)) as DURATION,
SERVER_NAME,b.DATABASE_NAME,[TYPE],backup_set_id,  
  b.backup_start_date,b.backup_finish_date,
Round(b.backup_size/(1024*1024),2) as Size_n_MB,
Physical_device_name
  FROM msdb.dbo.backupset b,
  ( select a.DATABASE_NAME, MAX(a.backup_set_id) id
  from msdb.dbo.backupset a 
WHERE a.TYPE = 'D' and 
a.DATABASE_NAME = DATABASE_NAME 
group by a.DATABASE_NAME ) cc,
  msdb.dbo.backupmediafamily f
WHERE TYPE = 'D'
AND b.backup_set_id = cc.id
  AND b.media_set_id = f.media_set_id

Find time difference between two dates

 

SELECT
b.backup_finish_date,
b.backup_start_date,
CAST ((b.backup_finish_date - b.backup_start_date) AS TIME(0))
FROM backupset b
WHERE convert(varchar(12),b.backup_finish_date,101) = '10/13/2013'