Daily Archives: December 6, 2019

-- Last backups for all databases 

SELECT 
    CAST(DATABASEPROPERTYEX([d].[name], 'Recovery') AS VARCHAR(10)) [RecoveryModel], 
    LEFT([d].[name], 50) [DatabaseName], 
    MAX([s].[backup_start_date]) [StartDate], 
    DATEDIFF(HOUR, MAX([s].[backup_start_date]), GETDATE()) [HoursAgo], 
    CASE [s].[type] 
    WHEN 'D' THEN 
        'Full' 
    WHEN 'I' THEN 
        'Differential' 
    WHEN 'L' THEN 
        'Log' 
    WHEN 'F' THEN 
        'File or Filegroup' 
    END [LastBackupType] 
FROM [master]..[sysdatabases] [d] 
LEFT OUTER JOIN [msdb]..[backupset] [s] 
    ON [d].[name] = [s].[database_name] 
JOIN ( 
    SELECT 
        [d].[name], 
        MAX([a].[backup_start_date]) [StartDate] 
    FROM [master]..[sysdatabases] [d] 
    LEFT OUTER JOIN [msdb]..[backupset] [a] 
        ON [d].[name] = [a].[database_name] 
    WHERE [d].[name] NOT IN ( 
              'tempdb', 'Northwind', 'pubs', 'model' 
          ) 
          AND [a].[type] = 'D' 
    GROUP BY [d].[name] 
) [x] 
    ON [x].[name] = [d].[name] 
WHERE [d].[name] NOT IN ( 
          'tempdb', 'Northwind', 'pubs', 'model' 
      ) 
GROUP BY [d].[name], 
         [s].[database_name], 
         DATABASEPROPERTYEX([d].[name], 'Recovery'), 
         [s].[type], 
         [x].[StartDate] 
HAVING MAX([s].[backup_start_date]) >= [StartDate] 
ORDER BY [d].[name], 
         MAX([s].[backup_start_date])