none
How do I determine the actual backup file size in Sql Server 2008, without querying the file system?

    Question

  • In SQL Server 2000 and 2005, the backup_size field of msdb..backupset table contained the actual size of the backup file. In SQL Server 2008, the same field contains a value that is 7680 bytes less than the actual file size, and, as far as I can tell, is always a multiple of the ntfs cluster size ( 4096 in my case ). Is there any reliable formula to get the actual file size? I need this, because I have an automated backup solution which checks the file size, among other things, in order to determine whether the backup file is the original one created by the sql server, or it has been changed.
    Thursday, December 09, 2010 9:37 AM

Answers

  • Hi Iavor,

    Based on my tests, the query "SELECT backup_size FROM msdb.dbo.backupset" could give me correct size (in bytes) of the backup set. There is a column called backup_finish_date in the msdb.dbo.backupset table which could state the last time that backup file backed up to this backup set, and you can compare it to the modified date time in the file system. Here are more queries may help you, see http://www.mssqltips.com/tip.asp?tip=1601.

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by jasho_me Wednesday, December 22, 2010 2:36 PM
    Wednesday, December 22, 2010 1:54 AM
    Moderator

All replies

  • Take a look at backup_size column in below

    SELECT

     

    *

     

    FROM msdb..backupset


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 09, 2010 9:50 AM
  • Uri, thank you for your reply, but backup_size column showing incorrect size under SQL Server 2008 is the exact reason I have posted this topic, please read carefully my post.
    Thursday, December 09, 2010 2:31 PM
  • The below gave me almost exact size ..(less 2MB) . Is that critical? As alternative use .NET or

    CREATE TABLE #files
    
    ( fn VARCHAR(255) )
    
    INSERT #files
    
    EXEC master..xp_cmdshell
    
    'dir c:\*.bak'
    
    SELECT * FROM #files
    
    SELECT
    
    cast(convert(varchar,cast(backup_size/1024/1024 as money),1) 
    
    as decimal(18,3))
    
    FROM msdb..backupset
    
    WHERE database_name='dbname'
    
    


    --------------------------------------------------------------------------------
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, December 13, 2010 8:19 AM
  • Haven't been able to log on for several days, exuse me for my late reply.

    I need the exact size of the file as created by sql server. I compare it to the file size on disk as part of a quick check whether the file has been modified since the backup was made. I could read the backup file headers and compare the LSNs to the msdb records, but this seems an overkill for such a simple task, given that the backup file could contain more than 1 backup etc.

    Should I post this issue to Connect?

     

    Best Regards,

     

    Iavor Vajarov

    Tuesday, December 21, 2010 9:16 AM
  • Hi Iavor,

    Based on my tests, the query "SELECT backup_size FROM msdb.dbo.backupset" could give me correct size (in bytes) of the backup set. There is a column called backup_finish_date in the msdb.dbo.backupset table which could state the last time that backup file backed up to this backup set, and you can compare it to the modified date time in the file system. Here are more queries may help you, see http://www.mssqltips.com/tip.asp?tip=1601.

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by jasho_me Wednesday, December 22, 2010 2:36 PM
    Wednesday, December 22, 2010 1:54 AM
    Moderator
  • Hi Chunsong,

    Thank you for your help. Using file date instead of file size for the consistency check, will do fine for me. However, I am still puzzled at why the backup_size field shows a wrong value. I will post this as a Connect bug.

     

    Best regards,

    Iavor

     

     

     

     

     

     

     

     

     

     

     

    Wednesday, December 22, 2010 2:36 PM
  • The below code will give all recent backup size (in MB).

    DECLARE @counter SMALLINT
    DECLARE @dbname VARCHAR(100)

    CREATE TABLE #BackupDetailsDaily(
        [HostWithInstance] [nvarchar](50),
        [DBName] [nvarchar](100),
        [BkupStartTime] [datetime],
        [BkupFinishTime] [datetime],
        [BkupType] [varchar](1),
        [BkupLocation] [nvarchar](250),
        [BkupSize_MB] [DECIMAL](15,2)
    )

    --Loop through all DB's
    SELECT @counter = MAX(dbid) FROM master..sysdatabases WHERE name NOT IN ('tempdb')
    WHILE @counter > 0
    BEGIN

        SELECT @dbname = name FROM master..sysdatabases WHERE dbid = @counter

        INSERT INTO #BackupDetailsDaily (DBName, BkupStartTime, BkupFinishTime, BkupType, BkupSize_MB, BkupLocation)
        select top 1 b.database_name, b.backup_start_date, b.backup_finish_date, b.type, CAST(((b.compressed_backup_size)* 0.00000095367432) AS DECIMAL(15,2)), a.physical_device_name
        from msdb.dbo.backupmediafamily a join msdb.dbo.backupset b
        on (a.media_set_id=b.media_set_id)
        where database_name = @dbname and b.type = 'D'
        order by b.backup_start_date desc
       
        SET @counter = @counter - 1
    END

    UPDATE #BackupDetailsDaily SET HostWithInstance = @@SERVERNAME

    SELECT * FROM #BackupDetailsDaily
    DROP TABLE #BackupDetailsDaily


    • Edited by trr_sql Wednesday, June 22, 2011 9:19 AM variable name changed
    Wednesday, June 22, 2011 9:17 AM