question

Vishu-2318 avatar image
0 Votes"
Vishu-2318 asked HafeezUddin-8965 answered

backup file size

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HafeezUddin-8965 avatar image
0 Votes"
HafeezUddin-8965 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @Vishu-2318,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.