PRB: Backup Size Displayed Incorrectly in SQL Server Enterprise Manager When the Backup Size is Greater Than 2,147,483,647 Bytes (2 GB) (321670)

WORKAROUND

SEM runs the following query to display the backup size information:

select 
      backup_set_id, 
      backup_finish_date, 
      name 
  from 
      msdb..backupset 
  where 
      database_name = N'Your_Database_Name' 
      and type = 'D' 
  order by backup_start_date desc
				

Then, for each backup set SEM runs the following query:

select 
      backup_set_id,
      backup_start_date,
      backup_finish_date,
      name,
      type,
      backup_size,
      position 
  from 
      msdb..backupset 
  where 
      database_name = N'Your_Database_Name' 
      and type != 'F'
      and backup_start_date >= (
                                select 
                                      backup_start_date 
                                  from 
                                      msdb..backupset 
                                  where 
                                      backup_set_id = 'Your_Backup_Set_ID'
                               )
   order by backup_start_date
				

To work around this problem, you can run the first query from Query Analyzer if you replace Your_Database_Name with the name of the database for which you want to obtain the backup history information.

After you decide which backup set you want to obtain more details about (usually based on the values for the the backup_finish_date and the name columns) like backup type, start date, and backup size, get the value for the column backup_set_id from the row in which you are interested. Run the second query and replace Your_Database_Name with the same database name you used in the first query and replace Your_Backup_Set_ID with the backup set ID value you have from the previous query.

The column backup size will give you the actual backup size in bytes. When you divide the actual backup size by 1024, the result represents the correct backup size in kilobytes (KB).