Script to get database backup details

• Use below TSQL script to get a complete history of single database backup history, you will need to change the backup type from D to I  and L to check Differential and Log backup history respectively. 

select @@servername [Server Name],backup_start_date, backup_finish_date, database_name,type,name,user_name,description

,(backup_size /1024.00 /1024.00 /1024.00) as backup_size_GB

,(compressed_backup_size /1024.00 /1024.00 /1024.00) as Compressed_backup_size_GB

from msdb.dbo.backupset 

where type='D' ----- I for diff, D for full and L for log

and database_name='msdb'  

order by backup_start_date desc 

• Below script can be used when want to see whole 7 days database backup history of whole week for all the databases of SQL instance.

SELECT 

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 

msdb.dbo.backupset.database_name, 

msdb.dbo.backupset.backup_start_date, 

msdb.dbo.backupset.backup_finish_date, 

msdb.dbo.backupset.expiration_date, 

CASE msdb..backupset.type 

WHEN 'D' THEN 'Database' 

WHEN 'L' THEN 'Log' 

END AS backup_type, 

msdb.dbo.backupset.backup_size, 

msdb.dbo.backupmediafamily.logical_device_name, 

msdb.dbo.backupmediafamily.physical_device_name, 

msdb.dbo.backupset.name AS backupset_name, 

msdb.dbo.backupset.description 

FROM msdb.dbo.backupmediafamily 

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 

WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 

ORDER BY 

msdb.dbo.backupset.database_name, 

msdb.dbo.backupset.backup_finish_date 

• Below Script can be used to get database backup history of one database for 40 days,

select backup_start_date, backup_finish_date, database_name,type,name,user_name,description

,(backup_size /1024.00 /1024.00 /1024.00) as backup_size_GB

,(compressed_backup_size /1024.00 /1024.00 /1024.00) as Compressed_backup_size_GB

from msdb.dbo.backupset 

where database_name='msdb'  

and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 40) 

order by backup_start_date desc