October 5, 2010 at 4:15 pm
I want to get the database name, the first date it was backed up and the backup size on that date from MSDB...BACKUPSET but am struggling mightily.
select database_name, min(convert(char(10), backup_finish_date, 111), backup_size/1024/1024
from msdb..backupset
where type = 'D'
group by database_name, backup_size
does not work (obviously). Do I need a subquery to get the backup size out of the aggregation?
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
October 5, 2010 at 4:48 pm
yes basically you need a subquery.
the query I use for this is
select a.name, b.backup_finish_date
from master..sysdatabases a left join msdb..backupset b on a.name = b.database_name
where b.backup_finish_date = (select min(backup_finish_date) from msdb..backupset where database_name = a.name and type = 'D')
add the size column to that
will tell you databases without a backup as well, though obviously if you purge your backup history the answer is not strictly accurate.
---------------------------------------------------------------------
October 6, 2010 at 10:52 am
Thanks George. Was hoping to not have to join to another dataset, but this works. Obviously I don't understand the aggregate function groupings.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
October 6, 2010 at 3:56 pm
Thats OK, I have to think about it too.
Heres a URL with some examples using Coalesce. I haven't tried them.
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LastBackUpDate
---------------------------------------------------------------------
October 6, 2010 at 4:21 pm
and apologies it does not include dbs not backed up, those would be filtered out by the where clause.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply