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