Check Last Backup
--=============================================
--==    Missing Backup in last 24 hours
--=============================================
SELECT  CONVERT(NVARCHAR(30), x.name) AS Database_Name ,
        Backup_Finish_Date
FROM    master.dbo.sysdatabases x
        FULL JOIN ( SELECT  a.database_name ,
                            a.server_name ,
                            a.database_creation_date ,
                            a.backup_start_date ,
                            a.backup_finish_date ,
                            DATEDIFF(second, a.backup_start_date,
                                     a.backup_finish_date) backup_running_seconds ,
                            a.backup_size
                    FROM    msdb.dbo.backupset a
                            INNER JOIN ( SELECT database_name ,
                                                MAX(backup_set_id) AS backup_set_id
                                         FROM   msdb.dbo.backupset
                                         WHERE  type = 'D'
                                         GROUP BY database_name
                                       ) AS b ON a.backup_set_id = b.backup_set_id
                                                 AND backup_finish_date >= GETDATE()
                                                 - 1
                  ) AS y ON x.name = y.database_name
WHERE   backup_finish_date IS NULL
        AND x.name <> 'tempdb'
 
--=============================================
--==    Last Backup Details
--=============================================
 SELECT 
        @@servername AS ServerName,
        a.NAME ,
        a.create_date , 
        b.backup_start_date AS LAST_FULL_DATE ,
        CASE WHEN b.backup_start_date IS NULL THEN 'FULL Warning !!!'
             ELSE ''
        END AS FULL_WARNING ,
        c.backup_start_date AS LAST_DIFF_DATE ,
        CASE WHEN c.backup_start_date IS NULL THEN 'DIFF Warning !!!'
             ELSE ''
        END AS DIFF_WARNING ,
        d.backup_start_date AS LAST_LOG_DATE ,
        CASE WHEN a.Recovery_model_desc <> 'SIMPLE'
                  AND d.backup_start_date IS NULL THEN 'LOG Warning !!!'
             ELSE ''
        END AS LOG_WARNING ,
        a.Recovery_model_desc AS RECOVERY_MODEL ,
        GETDATE() AS CRETED_DATE
    --'DIFFERENTIAL' AS backup_type  
 FROM   master.sys.databases a
        LEFT JOIN ( SELECT  database_name ,
                            MAX(backup_start_date) AS backup_start_date
                    FROM    msdb..backupset
                    WHERE   type = 'D'
                    GROUP BY database_name
                  ) AS b ON a.name = b.database_name  
    --FULL  
        LEFT JOIN ( SELECT  database_name ,
                            MAX(backup_start_date) AS backup_start_date
                    FROM    msdb..backupset
                    WHERE   type = 'I'
                    GROUP BY database_name
                  ) AS c ON a.name = c.database_name
    --LOG
        LEFT JOIN ( SELECT  database_name ,
                            MAX(backup_start_date) AS backup_start_date
                    FROM    msdb..backupset
                    WHERE   type = 'L'
                    GROUP BY database_name
                  ) AS d ON a.name = d.database_name
 WHERE  a.Name NOT IN ( 'tempdb' )
 ORDER BY b.backup_start_date ASC
        --a.NAME
 
SELECT  *
                    FROM    msdb..backupset
                    WHERE   type = 'D'
                    ORDER by backup_start_date
 
--==============================================
--== All Details for last Full backup
--==============================================
SELECT  *
FROM    msdb..backupset
WHERE   backup_set_id IN (
        SELECT  backup_set_id
        FROM    ( SELECT    database_name ,
                            MAX(backup_set_id) AS backup_set_id
                  FROM      msdb..backupset
                  WHERE     type = 'D'
                  GROUP BY  database_name
                ) AS A )
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License