Post reply

Last Backup Taken, or Not

  • December 18, 2012 at 6:43 am

    #1569543

    ... and one more version.

    This one works on SQL Server versions 2000-2012...

    SELECT c.Name AS DatabaseName, c.rm AS database_recovery_model,

    CASE c.type

    WHEN 'D' THEN 'Full'

    WHEN 'I' THEN 'Differential'

    WHEN 'L' THEN 'Log'

    ELSE COALESCE(d.type,'No Backup')

    END AS BackupType,

    Isnull(d.LastBackUpTaken, 'Never') AS LastBackUpTaken

    FROM ( SELECT b.Name, DATABASEPROPERTYEX(b.Name, 'recovery') as rm, a.*

    FROM (SELECT type='D' UNION ALL SELECT 'L' UNION ALL SELECT 'I') a, master.dbo.sysdatabases b

    WHERE DATABASEPROPERTYEX(b.Name, 'recovery') IN ( 'FULL', 'BULK_LOGGED') OR a.type IN ( 'D', 'I')

    ) c LEFT OUTER JOIN ( SELECT database_name,

    --Isnull(Max(recovery_model),'No Backup Taken') AS backup_recovery_model,

    type,

    CONVERT(VARCHAR(17), CONVERT(DATETIME, Max(backup_finish_date), 113)) AS LastBackUpTaken

    FROM msdb.dbo.backupset

    GROUP BY database_name, type

    ) d

    ON d.database_name = c.name AND c.type = d.type

    WHERE NOT ( c.type = 'I' AND d.LastBackUpTaken IS NULL) AND c.name <> 'tempdb'

    GROUP BY c.Name, c.type, d.LastBackUpTaken, c.rm, d.type

    ORDER BY 1, c.type

    Best regards
    karl

  • May 10, 2016 at 1:46 pm

    #1877947

    Thanks for the script.

Viewing 2 posts - 16 through 16 (of 16 total)

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