How does SQL Backup get the backup compressed size ?

Forum for users of Red Gate SQL Backup tool

Moderator: Chris Auckland

How does SQL Backup get the backup compressed size ?

Postby minimarch » Mon Apr 24, 2006 3:07 pm

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'
minimarch
 
Posts: 3
Joined: Mon Apr 24, 2006 3:00 pm

Postby Brian Donahue » Mon Apr 24, 2006 6:49 pm

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.
Code: Select all
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'%'
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby minimarch » Tue Apr 25, 2006 8:12 am

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.
minimarch
 
Posts: 3
Joined: Mon Apr 24, 2006 3:00 pm

Postby petey » Tue Apr 25, 2006 6:20 pm

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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby minimarch » Wed Apr 26, 2006 8:14 am

OK. Thanks a lot. I'm now able to progress...
minimarch
 
Posts: 3
Joined: Mon Apr 24, 2006 3:00 pm

Postby mclifford » Thu Oct 21, 2010 3:29 pm

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.
mclifford
 
Posts: 4
Joined: Thu Sep 02, 2010 8:24 pm

Postby petey » Thu Oct 21, 2010 4:08 pm

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.

Code: Select all
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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby mclifford » Thu Oct 21, 2010 4:14 pm

Great Information, I think this is close to what I am looking for!!
mclifford
 
Posts: 4
Joined: Thu Sep 02, 2010 8:24 pm


Return to SQL Backup Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests