June 26, 2017 at 9:50 am
Hi,
Dear friends,
I'm trying, to make a trending graph for a database, using the backupset, ideal result would be:
db1 22 1 2017 972587.82812500000 972587.82812500000 1.000000000000000000
db1 26 2 2017 1030774.12695312500 1030774.12695312500 1.000000000000000000
db1 26 3 2017 1078846.17968750000 1078846.17968750000 1.000000000000000000
db1 30 4 2017 1128924.55371093750 1128924.55371093750 1.000000000000000000
db1 28 5 2017 1180317.30468750000 1180317.30468750000 1.000000000000000000
;WITH
cte as (
SELECT
[database_name] AS "Database",
DATEPART(day,[backup_start_date]) as "day",
DATEPART(month,[backup_start_date]) AS "Month",
DATEPART(YEAR,[backup_start_date]) as "Year",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'db1'
AND [type] = 'D'
and DATEPART(YEAR,[backup_start_date]) = 2017
GROUP BY [database_name],DATEPART(mm,[backup_start_date]),DATEPART(YEAR,[backup_start_date]),DATEPART(day,[backup_start_date])
)
select * from cte
order by 3 desc
I'm stuck on cycling, any idea ???
Thanks a lot
June 26, 2017 at 10:19 am
I just ran your query without any problems so I'm not sure where you are stuck.
Maybe I'm being thick but I can't figure out what you mean by being stuck on cycling. ??
Sue
June 26, 2017 at 10:27 am
If you build this as a stored procedure, then you can go to Excel, create a connection to your database and execute the stored procedure and bring the data back to Excel and do the graphs and trending there.
June 26, 2017 at 11:18 am
Hi,
My actual resultset is attached, I am only interested in taking the last row per month.
In each month i cycle and get the next last day...
Thanks
db1 20 1 2017 972587.82812500000 972587.82812500000 1.000000000000000000
db1 21 1 2017 972587.82812500000 972587.82812500000 1.000000000000000000
db1 22 1 2017 972587.82812500000 972587.82812500000 1.000000000000000000
db1 24 2 2017 1030774.12695312500 1030774.12695312500 1.000000000000000000
db1 26 2 2017 1030774.12695312500 1030774.12695312500 1.000000000000000000
db1 15 3 2017 1078846.17968750000 1078846.17968750000 1.000000000000000000
db1 16 3 2017 1078846.17968750000 1078846.17968750000 1.000000000000000000
db1 26 3 2017 1078846.17968750000 1078846.17968750000 1.000000000000000000
db1 10 4 2017 1128924.55371093750 1128924.55371093750 1.000000000000000000
db1 20 4 2017 1128924.55371093750 1128924.55371093750 1.000000000000000000
db1 30 4 2017 1128924.55371093750 1128924.55371093750 1.000000000000000000
db1 03 5 2017 1128924.55371093750 1128924.55371093750 1.000000000000000000
db1 28 5 2017 1180317.30468750000 1180317.30468750000 1.000000000000000000
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply