How to check backup size in SQL Server

By | Published | No Comments

It is quite easy to check backup size in SQL Server. You can locate the backup location and check the size of backup file by right click on the file and click properties. a small window will pop up with different details along with the size of that particular backup file.

Another way to check backup size is to execute the following TSQL query in SQL Server management studio.

WITH LastBackUp AS


SELECT  bs.database_name,




        Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )

FROM  msdb.dbo.backupmediafamily bmf

JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id

JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id

WHERE   bs.[type] = ‘D’

AND bs.is_copy_only = 0


SELECT AS [Database],

        CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],

        backup_start_date AS [Last Full DB Backup Date],

        physical_device_name AS [Backup File Location]

FROM sys.databases AS sd

LEFT JOIN LastBackUp AS lb

    ON = lb.database_name

    AND Position = 1

ORDER BY [Database];

Check backup size

Leave a Reply

Your email address will not be published. Required fields are marked *