Microsoft KB Archive/321670

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

Article ID: 321670

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q321670





SYMPTOMS
When you restore a backup from SQL Server Enterprise Manager (SEM), the column named Size in the list box that enumerates the backup sets, displays an incorrect value when the size of your backup is more than 2,147,483,647 bytes (2 GB).



CAUSE
SEM uses an internal variable to temporarily store the value from the backup_size column of the msdb.dbo.backupset table and display it in the Restore database dialog box. The data type of this variable can only represent values that range from -2,147,483,648 to 2,147,483,647 bytes. Any backup that is larger than 2,147,483,647 bytes will display either as a negative value or as a positive value that does not represent the actual backup size.



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).



STATUS
Microsoft is researching this problem and will post more information in this article when the information becomes available.

Additional query words: SEM SQLEM

Keywords: kbprb kbpending KB321670

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.