SQL Server Backup – Compressed Backup Size Value is Incorrect in msdb

backupsql serversql-server-2012

I am not sure if in am checking this correctly.

But i can see the discrepancies in the value of compressed backup size from msdb.The value of compressed size is different what i see on the drives.

These backups are taken from Litespeed. e.g Litespeed backup compresses the backup of database to 1 TB for 8 TB and msdb shows that value in column as 4 TB.

Is there a way to get the correct information using SQL for third party backups?

Query used:

Declare @FromDate as datetime
-- Specify the from date value
set @FromDate = GETDATE() -10

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SQLServerName, 
   msdb.dbo.backupset.database_name,  
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database' 
       WHEN 'L' THEN 'Log' 
       WHEN 'I' THEN 'Differential' 
   END AS backup_type,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   DATEDIFF (SECOND, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) 'Backup Elapsed Time (sec)',
   msdb.dbo.backupset.compressed_backup_size AS 'Compressed Backup Size in KB',
  (msdb.dbo.backupset.compressed_backup_size/1024/1024) AS 'Compress Backup Size in MB',
  (msdb.dbo.backupset.compressed_backup_size/1024/1024/1024) AS 'Compress Backup Size in GB',
   CONVERT (NUMERIC (20,3), (CONVERT (FLOAT, msdb.dbo.backupset.backup_size) /CONVERT (FLOAT, msdb.dbo.backupset.compressed_backup_size))) 'Compression Ratio',
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database' 
       WHEN 'L' THEN 'Log' 
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE
CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= @FromDate
AND msdb.dbo.backupset.backup_size > 0 
ORDER BY 
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_finish_date

Best Answer

This is just conjecture, but it sounds like LiteSpeed is compressing the backup after SQL Server has performed it's part of the work. I doubt you can get accurate compression numbers from msdb in this scenario.

If Quest is using deduplication in their SQL Server backup product then SQL Server will certainly not be able to understand the real size of the backup, since the data stored in the backup will be opaque to SQL Server.

If you are trying to retain a history of backup sizes for growth tracking, you could use the numbers stored in MSDB as a rough guide to percentage growth over time. If you need exact sizes of the backups saved by Quest LiteSpeed, I would recommend contacting Quest's technical support to see if there is a way to do that.

Related Question