How does SQL Backup get the backup compressed size ?

minimarchminimarch Posts: 4
edited October 21, 2010 11:14AM in SQL Backup Previous Versions
Hello everyone,

I'm trying to develop a Web interface which will be used by my DBA. This interface will have to sum up different information (database size, database user...) whose the backup compressed size. I thought that this information was stocked in SQL Server's tables, but I had not find it.

Does SQL Backup get the backup compressed size directly from server's hard disk or get it from SQL Server ?

Thanks
Mini'

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello,

    It's stored in one of the bit fields of the backup history table in the MSDB database.I'll have to get more specifics later. When you select a database in SQL Backup and run SQL Profiler, the following query is sent, but this doesn't seem to accurately reflect the compression in my particular history. I have to find out why that is.
    exec sp_executesql N'DECLARE @servername nvarchar(256)
    SET @servername = CAST(SERVERPROPERTY(''ServerName'') AS nvarchar(256))
    
    SELECT 
    
      restore_history_id ID,
      CASE WHEN logical_device_name LIKE ''Red Gate SQL Backup%'' THEN 14 ELSE -1 END SQB, 
      -1 ''ENC'',
      18 ''ACT'',
      date, 
      CAST(''RESTORE'' AS nvarchar(8)) Action, 
      CASE type
      WHEN ''RD'' THEN CAST(''Full'' AS nvarchar(32))
      WHEN ''RF'' THEN CAST(''File'' AS nvarchar(32))
      WHEN ''RG'' THEN CAST(''Filegroup'' AS nvarchar(32))
      WHEN ''RL'' THEN CAST(''Log'' AS nvarchar(32))
      WHEN ''RV'' THEN CAST(''Verifyonly'' AS nvarchar(32))
      WHEN ''RI'' THEN CAST(''Differential'' AS nvarchar(32))
      ELSE CAST(type AS nvarchar(32)) END type,
      database_name, 
      0.00 ''Compression Ratio'',
      CAST('''' AS nvarchar(16)) ''Duration'',
      CAST('''' AS nvarchar(32)) Size,
      CAST('''' AS nvarchar(32)) ''Compressed Size'',
      CAST('''' AS nvarchar(16)) ''Compression Speed'',
      CAST(NULL AS int) ''Compression Level'',
      CAST(NULL AS int) ''Files'',
      user_name,
      CAST(0 AS bigint) valsize,
      CAST(0 AS bigint) valcompressed,
      0.0 valspeed,
      0 valtime,
      logical_device_name
    FROM
      (SELECT TOP 200 a.restore_history_id, a.restore_date date, a.destination_database_name database_name, a.user_name, ''R'' + a.restore_type type, c.logical_device_name
       FROM msdb..restorehistory a
    	 LEFT OUTER JOIN msdb..backupset b ON a.backup_set_id = b.backup_set_id
    	 LEFT OUTER JOIN msdb..backupmediafamily c ON b.media_set_id = c.media_set_id
       WHERE a.destination_database_name LIKE @P1
         AND c.family_sequence_number = 1
       ORDER BY a.restore_history_id DESC) x
    UNION ALL
    SELECT 
      backup_set_id,
      CASE WHEN logical_device_name LIKE ''Red Gate SQL Backup%'' THEN 14 ELSE -1 END SQB, 
      -1,
      17,
      date, 
      CAST(''BACKUP'' AS nvarchar(8)), 
      CASE type 
      WHEN ''BD'' THEN CAST(''Full'' AS nvarchar(32))
      WHEN ''BI'' THEN CAST(''Full differential'' AS nvarchar(32))
      WHEN ''BL'' THEN CAST(''Log'' AS nvarchar(32))
      WHEN ''BF'' THEN CAST(''File/filegroup'' AS nvarchar(32))
      WHEN ''BG'' THEN CAST(''File differential'' AS nvarchar(32))
      WHEN ''BP'' THEN CAST(''Partial'' AS nvarchar(32))
      WHEN ''BQ'' THEN CAST(''Partial differential'' AS nvarchar(32))
      ELSE CAST(type AS nvarchar(32)) END type,
      database_name,
      0.00,
      '''',
      '''',
      '''',
      '''',
      NULL,
      NULL,
      user_name,
      0,
      0,
      0, 
      0,
      logical_device_name
    FROM
      (SELECT TOP 200 a.backup_set_id, a.backup_start_date date, a.database_name, a.user_name, ''B'' + a.type type, b.logical_device_name
       FROM msdb..backupset a
       LEFT OUTER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
       WHERE a.database_name LIKE @P2
         AND UPPER(a.server_name) = @servername
         AND b.family_sequence_number = 1
       ORDER BY a.backup_set_id DESC) y
    
    ORDER BY date DESC', N'@P1 nvarchar(1),@P2 nvarchar(1)', N'%', N'%'
    
  • In my search, I explored this way too, but, in my case, this request returns no data concerning the compression (all these fields are blanks)... :cry: It's one reason why I thought that SQL Backup filled them itself.
  • peteypetey Posts: 2,358 New member
    In the logical_file_name field in the msdb..backupmediafamily table, you will see values similar to this for backups made with SQL Backup:

    Red Gate SQL Backup (4.2.0.425): 00000000001F00000000000000029600000015CD000000010101

    The first 16 characters after the ':' is the hex value for the uncompressed data size, while the following 16 characters is the hex value for the compressed data size. Thus, for the value above, the uncompressed data size is 2031616 bytes (00000000001F0000) and the compressed data size is 169472 bytes (0000000000029600).
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • OK. Thanks a lot. I'm now able to progress...
  • Does anyone have an update to this procedure?

    Compression Ratio,
    Duration,
    Size,
    Compressed Size,
    Compressed Speed
    ect...

    It looks like most of it is hard coded.
  • peteypetey Posts: 2,358 New member
    You need to decode the values for the uncompressed and compressed sizes from the logical_device_name column.

    If you're using SQL Backup 5 or newer, another source of the information is in the SQL Server Compact database used by SQL Backup (see the last entry in this post for details). You'll need to query the backuphistory table e.g.
    EXEC master..sqbdata 'SELECT * FROM backuphistory
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Great Information, I think this is close to what I am looking for!!
Sign In or Register to comment.