Total backup size of backup of all databases in every month from backupset table in msdb

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Total backup size of backup of all databases...

Total backup size of backup of all databases in every month from backupset table in msdb

Rate Topic

Display Mode

Topic Options

Author

Message

SQL-DBA-01

Posted Friday, August 17, 2012 6:21 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 1:33 AM
Points: 738, Visits: 1,126

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

anthony.green

Posted Friday, August 17, 2012 6:35 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

durai nagarajan

Posted Friday, August 17, 2012 6:44 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:05 AM
Points: 856, Visits: 2,115

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

SQL-DBA-01

Posted Monday, August 20, 2012 1:06 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 1:33 AM
Points: 738, Visits: 1,126

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

durai nagarajan

Posted Monday, August 20, 2012 2:00 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:05 AM
Points: 856, Visits: 2,115

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

Lavanyasri

Posted Monday, August 20, 2012 7:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 5:58 AM
Points: 199, Visits: 741

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 »

Permissions