get database, earliest date and backup size

  • 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
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • 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.

    ---------------------------------------------------------------------

  • 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
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • 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

    ---------------------------------------------------------------------

  • 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