Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Total backup size of backup of all databases in every month from backupset table in msdb Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 6:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:25 AM
Points: 860, Visits: 1,402
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.
Post #1346500
Posted Friday, August 17, 2012 6:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:49 AM
Points: 5,216, Visits: 5,110
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1346504
Posted Friday, August 17, 2012 6:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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)'
from msdb..backupset a
inner join msdb..backupfile b on a.backup_set_id = b.backup_set_id

where convert(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
Post #1346511
Posted Monday, August 20, 2012 1:06 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:25 AM
Points: 860, Visits: 1,402


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
from msdb..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
--where convert(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.
Post #1347006
Posted Monday, August 20, 2012 2:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1347020
Posted Monday, August 20, 2012 7:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 1:31 AM
Points: 204, Visits: 767
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 .


Post #1347151
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse