Post reply

backupset, backupfile and sysdatabases

  • January 25, 2005 at 2:36 pm

    #66868

    I'm trying to write a cursor, here is the select:

    select sd.name

        from sysdatabases sd

        INNER JOIN

        (

         select REPLACE(bf.logical_name,'_Data','') as name, max(bs.backup_finish_date) as finish

            from msdb.dbo.backupfile bf

         INNER JOIN

            msdb.dbo.backupset bs on

            bs.backup_set_id = bf.backup_set_id

        where bf.logical_name not like '%_Log'

          AND bf.logical_name not in ('master', 'modeldev', 'MSDBData')

         GROUP BY REPLACE(bf.logical_name,'_Data',''), bs.backup_finish_date

        )x

        ON sd.name = x.name

        WHERE DateDiff(hh,x.finish,getdate()) >= 14

     

    I want to only back up databases that haven't been backed up in the last 14 hours.  As soon as I add the max(bs.backup_finish_date) the result set blows out to include every backup set, and not one name for each distinct database I need to backup.  What am i doing wrong?

  • January 25, 2005 at 2:44 pm

    #539050

    Your Group By is wrong - it contains the same column you're taking the Max() of, therefore you'll get 1 record for each date instead of just the most recent.

     

  • January 25, 2005 at 2:55 pm

    #539054

    You are the best!  Thanks!!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply