July 7, 2012 at 7:57 am
I have a query from msdb..backupset that displays Database Name and Compression Ratio, but it displays multiple records for every database/backup. I just want one record for latest backup of the database, as in:
Database1 CompressionRation
Database2 CompressionRation
Database3 CompressionRation
so on...
If I specify Max(b.backup_finish_date) I have to group by Compression Ratio, that aggregates Compression Ratio number and is not what I want.
SELECT
b.database_name 'Database Name',
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) /
CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio'
FROM
msdb.dbo.backupset b
WHERE
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0
AND b.backup_size > 0
and b.type = 'd'
ORDER BY
b.database_name
July 17, 2012 at 1:20 pm
BikeDude -
I figure you have already figured this out and moved on but just in cae you didn't here is a query that works for me:
SELECT
b.database_name 'Database Name'
,b.backup_size
,b.compressed_backup_size
,CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) / CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio'
,b.backup_finish_date
FROM
msdb.dbo.backupset b
WHERE
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0 AND
b.backup_size > 0 AND b.type = 'd'
ORDER BY
b.database_name
Let me know if this works ... thanks
Mark Copley
July 17, 2012 at 1:21 pm
Sorry forgot to add the DISTINCT to the SQL
SELECT DISTINCT
b.database_name 'Database Name'
,b.backup_size
,b.compressed_backup_size
,CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) / CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio'
,b.backup_finish_date
FROM
msdb.dbo.backupset b
WHERE
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0 AND
b.backup_size > 0 AND b.type = 'd'
ORDER BY
b.database_name
July 17, 2012 at 3:20 pm
SELECT
[Database Name],
[Compression Ratio]
FROM (
SELECT
b.database_name 'Database Name',
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) /
CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio',
ROW_NUMBER() OVER (PARTITION BY b.database_name ORDER BY backup_finish_date DESC) AS row_num
FROM
msdb.dbo.backupset b
WHERE
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0
AND b.backup_size > 0
AND b.type = 'd'
) AS derived
WHERE
row_num = 1
ORDER BY
[Database Name]
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply