Post reply

Find databases without backup

  • September 14, 2016 at 3:30 pm

    #400726

    Comments posted to this topic are about the item Find databases without backup

  • September 30, 2016 at 6:18 am

    #1904134

    Angel, Thanks for taking the time to share this with us. I did run into one problem with the script. On some servers I would get this error message:

    Msg 8152, Level 16, State 13, Line 5

    String or binary data would be truncated.

    The cause of this was this line:

    CREATE TABLE #tablenames (id int NOT NULL IDENTITY (1, 1),name nvarchar (50))

    Some of the databases had names longer than 50 characters. This often happens on SharePoint or Project Server databases. I fixed the problem by changing that line of code to use SYSNAME like this:

    CREATE TABLE #tablenames (id int NOT NULL IDENTITY (1, 1),name SYSNAME)

    Thanks again.

    Lee

  • September 30, 2016 at 12:12 pm

    #1904236

    Angel, Thanks for taking the time to share this with us.

    I just put my 5ctv too 😀

    - Identify Backup type with msdb..backupset.type 'D' - 'Full'; 'I' - 'Differential'; 'L' - 'Log'

    - Specify last time backup was taken using [Backup Status]

    CASE msdb..backupset.backup_start_date > DATEADD( hh, -1, getdate() ) )

    THEN 'an hour' WHEN b.backup_start_date > DATEADD( dd, -1, getdate() )

    THEN 'a day' WHEN b.backup_start_date > DATEADD( dd, -7, getdate() )

    THEN 'a week' END ELSE '*****CHECK BACKUP!!!*****' END )

    Final version:

    SELECT

    s.name

    , b.backup_start_date

    , ( CASE WHEN ( b.backup_start_date > DATEADD( dd, -7, getdate() ) )

    THEN CASE b.type WHEN 'D' THEN 'Full'

    WHEN 'I' THEN 'Differential'

    WHEN 'L' THEN 'Log' END +' Backup within ' +

    CASE WHEN ( b.backup_start_date > DATEADD( hh, -1, getdate() ) )

    THEN 'an hour' WHEN b.backup_start_date > DATEADD( dd, -1, getdate() )

    THEN 'a day' WHEN b.backup_start_date > DATEADD( dd, -7, getdate() )

    THEN 'a week' END ELSE '*****CHECK BACKUP!!!*****' END ) [Backup Status]

    FROM master..sysdatabasess

    LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

    AND b.backup_start_date = ( SELECT MAX(backup_start_date) FROM msdb..backupset

    WHERE database_name = b.database_name

    AND type in ( 'D', 'I', 'L' ) ) -- FULL database backups only, not log backups

    Thanks you

  • September 30, 2016 at 4:06 pm

    #1904275

    Thanks for your comments.

  • October 3, 2016 at 11:53 am

    #1904501

    Thanks for the script.

Viewing 5 posts - 1 through 4 (of 4 total)

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