Which Databases are Being Backed Up - Another Look

  • santveer

    SSC-Addicted

    Points: 447

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/co


    1234

  • Peter Schott

    SSCrazy Eights

    Points: 9601

    I thought this was a pretty good reminder of what we are and are not backing up. I did have to do some modifications in my case - we do transaction log backups and database level backups. Our database backups are done both to tape and to file (two separate backups). If I want to know when the actual last database backup was, this needs to be modified a little bit as follows:

    Select a.name as DBName, cBackupType, cBackupLocation, dBackup from master.dbo.sysdatabases a

    left join

    (select database_name as DBName,

    CASE WHEN type = 'L' Then 'Log'

    WHEN type = 'D' THEN 'Database'

    ELSE 'Other'

    END as cBackupType,

    CASE WHEN user_name = 'DOMAIN\TapeBackupAccount'

    THEN 'Tape'

    ELSE 'Disk'

    END as cBackupLocation,

    max(backup_finish_date) as dBackup

    from msdb.dbo.backupset where backup_finish_date <= getdate()

    group by database_name, type, user_name) B

    on a.name = b.DBName

    When I select from this, I'm able to filter out Disk/Tape and Log/Database backups. I didn't include differential backups because we don't use them at my company. This was very useful to find those databases which may have been backed up to disk for a transaction log backup, but no recent db backup. It was also useful to find those db's that have only been captured on tape recently.

    Overall, this was a great reminder and gave me a little nudge to check on my own systems. Thanks for the article.

    -Pete Schott

  • santveer

    SSC-Addicted

    Points: 447

    Pete Schott,

    I agree with you. I got a number of emails to include Backup type in this script.

    Below is the revised script to take care of this issue.

    Select a.name,backup_type, Backup_Date from master.dbo.sysdatabases a

    left join

     ( select database_name, Backup_Type = Case type  when 'D' then  'Database'

           When 'I' then  'Database Differential'

           When 'L' then  'Log'

           When 'F' then  'File or Filegroup'

           Else

           'Error'

              End,

       max(backup_finish_date) backup_date

      from msdb.dbo.backupset where backup_finish_date <= getdate()

      group by database_name,Type

    )

    B

    on a.name=b.database_name


    1234

  • Michael Ziese-200679

    Say Hey Kid

    Points: 709

    I have problems with your script.

    I cant find the the cmax and backup_date columns.

    My SQL 2000 Database hasn't this objects.

    I dont know what I shoud do.

    I am  not still a newbee but i'am new in this forum.

     

     

    Kind Regards

     

    Michael

  • Peter Schott

    SSCrazy Eights

    Points: 9601

    Not sure about cMax - don't see that in any of the posts. If you look at the queries, the query posted contains a "derived table". This is a subquery that we can join against. (inside the parens). In that derived table, we use an alias to define backup_date (e.g. select getdate() backup_date or select getdate() as backup_date or select backup_date = getdate() )

    That's probably where this is coming from. We use this because we don't want every date in the table, just the latest/max date.

  • santveer

    SSC-Addicted

    Points: 447

    All,

     

    Regarding CMAX function, this is actualy a typoerror... please read it MAX function.

     


    1234

  • santveer

    SSC-Addicted

    Points: 447

    please use this script

     

    Select a.name,backup_type, Backup_Date from master.dbo.sysdatabases a

    left join

     ( select database_name, Backup_Type = Case type  when 'D' then  'Database'

           When 'I' then  'Database Differential'

           When 'L' then  'Log'

           When 'F' then  'File or Filegroup'

           Else

           'Error'

              End,

       max(backup_finish_date) backup_date

      from msdb.dbo.backupset where backup_finish_date <= getdate()

      group by database_name,Type

    )

    B

    on a.name=b.database_name

     


    1234

  • RichB

    SSCrazy Eights

    Points: 9651

    Guess we can spot who doesn't run a case sensitive server...

  • kbirecki

    Grasshopper

    Points: 15

    Why does this (case sensitive server) matter?

  • Peter Schott

    SSCrazy Eights

    Points: 9601

    Because if you are trying to access one of the system tables/columns in Proper Case, you will more than likely fail. Most (if not all) of them are in all lowercase. I think the one I saw right away was Backup_Type which should be backup_type in a case-sensitive server or it won't work.

    Case sensitive collations require that the case you are using matches exactly to that on the device. In those cases Red red rEd.

    -Pete

Viewing 10 posts - 1 through 10 (of 10 total)

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