Red Gate forums :: View topic - How does SQL Backup get the backup compressed size ?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup Previous Versions
SQL Backup Previous Versions forum

How does SQL Backup get the backup compressed size ?

Search in SQL Backup Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
minimarch



Joined: 24 Apr 2006
Posts: 3

PostPosted: Mon Apr 24, 2006 3:07 pm    Post subject: How does SQL Backup get the backup compressed size ? Reply with quote

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'
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6675

PostPosted: Mon Apr 24, 2006 6:49 pm    Post subject: Reply with quote

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:

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'%'
Back to top
View user's profile Send private message
minimarch



Joined: 24 Apr 2006
Posts: 3

PostPosted: Tue Apr 25, 2006 8:12 am    Post subject: Reply with quote

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)... Crying or Very sad It's one reason why I thought that SQL Backup filled them itself.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Tue Apr 25, 2006 6:20 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
minimarch



Joined: 24 Apr 2006
Posts: 3

PostPosted: Wed Apr 26, 2006 8:14 am    Post subject: Reply with quote

OK. Thanks a lot. I'm now able to progress...
Back to top
View user's profile Send private message
mclifford



Joined: 02 Sep 2010
Posts: 4

PostPosted: Thu Oct 21, 2010 3:29 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Thu Oct 21, 2010 4:08 pm    Post subject: Reply with quote

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:
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
Back to top
View user's profile Send private message Send e-mail
mclifford



Joined: 02 Sep 2010
Posts: 4

PostPosted: Thu Oct 21, 2010 4:14 pm    Post subject: Reply with quote

Great Information, I think this is close to what I am looking for!!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group