August 17, 2012 at 6:21 am
hi,
Can you please provide a quick query to get the below requirement :
Total backup size of backup of all databases in every month from backupset table in msdb
I need to prepare a sample report to analyze the database growth of databases!
Thanks.
August 17, 2012 at 6:35 am
What have you tried so far?
And does it need to be a total of all databases per month,
E.g
200GB July 2012
Or does it need to be broke down by DB
20GB Master July 2012
August 17, 2012 at 6:44 am
select distinct @@servername, convert(varchar,a.backup_start_date,101) 'Date', a.database_name 'DB Name',
convert(decimal(7,2),round(sum(b.file_size/1024/1024/1024),3)) as 'Database Size in(GB)'
frommsdb..backupset a
inner join msdb..backupfile b on a.backup_set_id = b.backup_set_id
whereconvert(varchar,a.backup_start_date,101) = convert(varchar,getdate(),101)
and a.database_name in ( select name from sys.databases where database_id not in(1,2,3,4))
and a.type = 'd'
and b.file_type = 'd' and is_snapshot = 0
group by a.backup_set_id, a.database_name, a.backup_start_date
order by 1--, physical_name
replace getdate with the date you want , this will list datewise, DB wise size and i have excluded sys databases.
Regards
Durai Nagarajan
August 20, 2012 at 1:06 am
select distinct @@servername 'servername', convert(varchar,a.backup_start_date,121) 'Date_time', a.database_name 'DB Name',
convert(decimal(7,2),round(sum(b.file_size/1024/1024),3)) as 'Database Size in(MB)',
s.physical_device_name
into #t1
frommsdb..backupset a
inner join msdb..backupfile b on a.backup_set_id = b.backup_set_id
left join backupmediafamily s
on a.media_set_id=s.media_set_id
where DATEDIFF(d, backup_start_date, GETDATE()) <= 180
--whereconvert(varchar,a.backup_start_date,101) = convert(varchar,getdate(),101)
--and a.database_name in ( select name from sys.databases where database_id not in(1,2,3,4))
and s.physical_device_name like 'D:\%'
and a.type = 'd'
and b.file_type = 'd' and is_snapshot = 0
--and s.physical_device_name like 'D:\%'
group by a.backup_set_id, a.database_name, a.backup_start_date, s.physical_device_name
order by 1--, physical_name
--sp_help backupmediafamily
select distinct [db name] from #t1
--drop table #t1
select date_time, sum([Database Size in(MB)]) 'DB_Size inMB', [DB Name]
from #t1
group by date_time,[DB Name]
order by date_time
## How to modify the last select statement (using dateadd function) to add in the total backup size of databases in a single day? Please suggest
Thanks.
August 20, 2012 at 2:00 am
select convert(varchar,date_time,101), sum([Database Size in(MB)]) 'DB_Size inMB', [DB Name]
from #t1
group by convert(varchar,date_time,101),[DB Name]
order by convert(varchar,date_time,101)
try this.
Regards
Durai Nagarajan
August 20, 2012 at 7:18 am
hi ,
This is for one week backup details ::
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
when 'I' THEN 'Differential database '
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
change the getdate() -30 , then u will get for month .
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply