How does SQL Backup get the backup compressed size ?

Comments

• Brian Donahue Posts: 6,590 Bronze 1

  April 24, 2006 1:49PM

  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'%'
  

  0 · Share on Twitter
• minimarch Posts: 4

  April 25, 2006 3:12AM

  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)... It's one reason why I thought that SQL Backup filled them itself.

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  April 25, 2006 1:20PM

  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

  0 · Share on Twitter
• minimarch Posts: 4

  April 26, 2006 3:14AM

  OK. Thanks a lot. I'm now able to progress...

  0 · Share on Twitter
• mclifford Posts: 5

  October 21, 2010 10:29AM

  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.

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  October 21, 2010 11:08AM

  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

  0 · Share on Twitter
• mclifford Posts: 5

  October 21, 2010 11:14AM

  Great Information, I think this is close to what I am looking for!!

  0 · Share on Twitter