| Author |
Message |
minimarch
Joined: 24 Apr 2006
Posts: 3
|
 Posted: Mon Apr 24, 2006 3:07 pm Post subject: How does SQL Backup get the backup compressed size ? |
 |
|
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 |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Mon Apr 24, 2006 6:49 pm Post subject: |
|
|
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'%' |
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
minimarch
Joined: 24 Apr 2006
Posts: 3
|
| Posted: Tue Apr 25, 2006 8:12 am Post subject: |
|
|
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. |
|
| Back to top |
|
|
petey
Joined: 24 Apr 2005
Posts: 2218
|
| Posted: Tue Apr 25, 2006 6:20 pm Post subject: |
|
|
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 |
|
|
minimarch
Joined: 24 Apr 2006
Posts: 3
|
| Posted: Wed Apr 26, 2006 8:14 am Post subject: |
|
|
| OK. Thanks a lot. I'm now able to progress... |
|
| Back to top |
|
|
mclifford
Joined: 02 Sep 2010
Posts: 4
|
| Posted: Thu Oct 21, 2010 3:29 pm Post subject: |
|
|
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 |
|
|
petey
Joined: 24 Apr 2005
Posts: 2218
|
| Posted: Thu Oct 21, 2010 4:08 pm Post subject: |
|
|
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 |
|
|
mclifford
Joined: 02 Sep 2010
Posts: 4
|
| Posted: Thu Oct 21, 2010 4:14 pm Post subject: |
|
|
| Great Information, I think this is close to what I am looking for!! |
|
| Back to top |
|
|
|
