Monday, May 27, 2013

Compress Backup Information in Backup and Restore Events

In SSMS, there is report called , “Backup and Restore Events” report.

image

if you consider latest backups backup size are almost similar. (~  339 MB) However, if you really see the physical file size, it is 79 MB.

So the report and physical file size is different.

You can measure the file size by running a query.

SELECT 
bs.media_set_id,
bs.backup_finish_date,
bs.type,
bs.backup_size / 1024 backup_size_KB,
bs.compressed_backup_size / 1024 compressed_backup_size_KB,
mf.physical_device_name
FROM dbo.backupset AS bs
INNER JOIN dbo.backupmediafamily AS mf
ON bs.media_set_id = mf.media_set_id
WHERE database_name = 'AdventureWorks2012'
ORDER BY backup_finish_date DESC;
GO

image


So , here you can see the correct value can be obtained you query compressed_backup_size column rather than querying backup_size column. so it seems like, SSMS report is using backup_size column rather than compressed_backup_size column which seems to be BUG!

No comments:

Post a Comment