none
latest backup size - max(backup_finish_date) RRS feed

  • Question

  • Why does this query return multiple rows per database?

    select database_name, backup_size, max(backup_finish_date)
    from msdb.dbo.backupset
    where database_name = 'Ditest' and type='D'
    group by database_name, backup_size
    order by backup_finish_date desc

    SQL Server 2008r2, enterprise edition


    Dianne

    Monday, March 10, 2014 9:32 PM

Answers

  • Try:

    ;with cte as (select database_name,

    backup_size, backup_finish_date,

    row_number() over (partition by Database_name order by backup_finish_date DESC) as Rn from msdb.dbo.backupset where database_name = 'Ditest' and type='D') select * from cte where Rn = 1

    If you're looking for particular DB data, then just

    select top (1) * from ... 

    order by backup_finish_date DESC



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Pantelis44999 Tuesday, March 11, 2014 12:42 AM
    • Marked as answer by deescott Tuesday, March 11, 2014 12:56 PM
    Monday, March 10, 2014 10:46 PM
    Moderator

All replies

  • You're grouping by backup_size. If the backup_size of the database changed, then this query is supposed to return as many rows as you have different backup sizes.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, March 10, 2014 10:22 PM
    Moderator
  • I  have to add backup_size to the group by due to the max(backup_finish_date).  It is required.

    Do you know a different way or have a suggestion?  How do I  get the size of the latest backup?


    Dianne


    • Edited by deescott Monday, March 10, 2014 10:42 PM
    Monday, March 10, 2014 10:41 PM
  • Try:

    ;with cte as (select database_name,

    backup_size, backup_finish_date,

    row_number() over (partition by Database_name order by backup_finish_date DESC) as Rn from msdb.dbo.backupset where database_name = 'Ditest' and type='D') select * from cte where Rn = 1

    If you're looking for particular DB data, then just

    select top (1) * from ... 

    order by backup_finish_date DESC



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Pantelis44999 Tuesday, March 11, 2014 12:42 AM
    • Marked as answer by deescott Tuesday, March 11, 2014 12:56 PM
    Monday, March 10, 2014 10:46 PM
    Moderator
  • What is it what you want? Max backup size for a particular database, or the backup size for the most recent backup (again, for a particular database)?


    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, March 10, 2014 11:24 PM
    Moderator
  • How do I  get the size of the latest backup? - is her question. My query above answers it.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, March 10, 2014 11:32 PM
    Moderator
  • That worked beautifully - thank you.  But seems very complex for a simple query.  Definitely will save this one!

    Dianne


    Dianne

    Tuesday, March 11, 2014 12:56 PM
  • Well, it worked beautifully in the local instance.  When I tried to run it in our central repository I got errors on anything not 2012 version.  I need to run this query on the entire farm.  Anything that will work on 2005 forward?  Suggestions?


    Dianne

    Tuesday, March 11, 2014 1:05 PM
  • Adding max to the backup_size worked as well, just grouping by database

    Dianne

    Tuesday, March 11, 2014 1:17 PM
  • If you want a simple query, that will work on way-back-versions of SQL Server and only for one single database, you can use:

    SELECT database_name, backup_size, backup_finish_date
    FROM msdb.dbo.backupset
    WHERE database_name = @dbname AND type = @type
    AND backup_finish_date = 
     (SELECT max(backup_finish_date) 
      FROM msdb.dbo.backupset 
      WHERE database_name = @dbname 
       AND type = @type)


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, March 11, 2014 2:03 PM
    Moderator
  • Hmm, ROW_NUMBER() was introduced in SQL Server 2005, so as long as it's not SQL 2000, the above query should work fine. Otherwise there are other ways to get the top row and as I said, if you're looking for particular database, then just use top 1 query. If you want this to work for all DBs, then check Tibor's query.

    You can also see these blogs 

    http://social.technet.microsoft.com/wiki/contents/articles/19670.t-sql-useful-links.aspx#Select_Top_N_Rows_per_Group

    and pick up any of other solutions from these blogs that work in lower versions of SQL Server.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, March 11, 2014 2:30 PM
    Moderator