backup file size

Question

Experts ,

Can you please share a script which can extract the database backup(Full and Log) size for all the backups showing in the backup history table date and timewise

Answer 1

You can the backup file information from system database "msdb" table backupfile, for example

SELECT *
FROM msdb.dbo.backupset as BS
     INNER JOIN
     msdb.dbo.backupfile AS BF
         ON BS.backup_set_id = BF.backup_set_id

Answer 2

Hi @Vishu ,

Please try below T-SQL;

--- database backup size and how long it took to do backup  
  
use msdb  
SELECT bs.database_name AS DatabaseName  
  
, CAST(bs.backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS BackupSizeMB  
  
,  

 CASE bs.type   
WHEN 'D' THEN 'Database'   
WHEN 'L' THEN 'Log'   
When 'I' THEN 'Differential database'  
END AS backup_type  


, bs.backup_start_date AS BackupStartDate  
  
, bs.backup_finish_date AS BackupEndDate  
  
, CAST(bs.backup_finish_date - bs.backup_start_date AS TIME) AS AmtTimeToBkup  
  
, bmf.physical_device_name AS BackupDeviceName  
  
FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf  
  
ON bs.media_set_id = bmf.media_set_id  
  
ORDER BY bs.database_name, bs.backup_start_date  

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Answer 3

If you are compressing the backups, then you should compressed_backup_size column to calculate the backup size.

--- database backup size and how long it took to do backup

use msdb
SELECT top 10 bs.database_name AS DatabaseName

, CAST(bs.backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS BackupSizeMB
, CAST(bs.compressed_backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS compressed_backup_size
,
CASE bs.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
When 'I' THEN 'Differential database'
END AS backup_type

, bs.backup_start_date AS BackupStartDate

, bs.backup_finish_date AS BackupEndDate

, CAST(bs.backup_finish_date - bs.backup_start_date AS TIME) AS AmtTimeToBkup

, bmf.physical_device_name AS BackupDeviceName
-- select top 100 *
FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

ORDER BY bs.database_name, bs.backup_start_date