Database backup history

We can also use the following script:

1) if you want to get the database file storage usage:

SELECT d.name ,f.type_desc ,f.size [Size MB],f.state_desc
FROM [sys].[master_files] f
JOIN [sys].[databases]  d ON d.database_id =f.database_id

2) if you want to get the database backup storage usage:

SELECT SUBSTRING(a.database_name,1,35) AS 'Database',
DATEPART(YEAR,a.backup_start_date) AS 'year',
DATEPART(MONTH,a.backup_start_date) AS 'month' ,
AVG(CAST((a.backup_size /1073741824) AS DECIMAL (9,2)))AS 'Avg Gig' ,
AVG(CAST((a.backup_size /1048576) AS DECIMAL (9,2)))AS 'Avg MB' 
--,a.type
FROM  msdb.dbo.backupset a
JOIN  msdb.dbo.backupset b on a.server_name = b.server_name 
      AND a.database_name = b.database_name 
WHERE a.type = 'D'  
and b.type = 'D'         
  and a.database_name = 'MyDB_Test'
  --and a.backup_size > 1073741824   --   > 1 Gig
GROUP BY a.database_name,DATEPART(YEAR,a.backup_start_date)
  ,DATEPART(MONTH,a.backup_start_date)--,a.type
ORDER BY a.database_name,DATEPART(YEAR,a.backup_start_date) DESC
  ,DATEPART(MONTH,a.backup_start_date) DESC

 
3) if you want to get the latest backup only use the following command:

SELECT sdb.Name AS DatabaseName,
 COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') 
 AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

reference: http://blog.sqlauthority.com/2010/11/04/sql-server-finding-last-backup-time-for-all-database/

4) if you want to get the backup history for all databases in the last seven days:

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

reference: http://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/