Database

How to check backup size in SQL Server

By muhkam01 | Published March 19, 2023 | 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,

        bs.backup_size,

        bs.backup_start_date,

        bmf.physical_device_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

        sd.name 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 sd.name = lb.database_name

    AND Position = 1

ORDER BY [Database];

Latest posts