ZFnhfv3s

1IF OBJECT_ID('tempdb..#BackupStatus') IS NOT NULL 
2DROP Table tempdb..#BackupStatus
3
4create table tempdb..#BackupStatus(
5
6Server_name VARCHAR(50),
7Full_Backup_Status_Weekly VARCHAR(50),
8Diff_Backup_Status_Daily VARCHAR(50),
9Transaction_log_backup_Hourly VARCHAR(50))
10
11--select * from #BackupStatus
12
13insert into tempdb..#BackupStatus
14(Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly)
15--select Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly from
16values
17use msdb
18go
19
20WITH [MostRecentBackupStatus_CTE]
21AS
22(
23    SELECT  bsfull.[server_name] ,
24            bsfull.[database_name] ,
25            bsfull.[backup_finish_date] AS [last_full_backup] ,
26            bsdiff.[backup_finish_date] AS [last_diff_backup] ,
27            bstlog.[backup_finish_date] AS [last_tran_backup] ,
28            DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
29            DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
30            DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] 
31
32    FROM    [msdb]..[backupset] AS bsfull
33            LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
34                                                       AND bstlog.[server_name] = bsfull.[server_name]
35                                                       AND bstlog.[type] = 'L'
36                                                       AND bstlog.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
37                                                                                            FROM    [msdb]..[backupset] b2
38                                                                                            WHERE   b2.[database_name] = bsfull.[database_name]
39                                                                                                    AND b2.[server_name] = bsfull.[server_name]
40                                                                                                    AND b2.[type] = 'L') )
41            LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
42                                                       AND bsdiff.[server_name] = bsfull.[server_name]
43                                                       AND bsdiff.[type] = 'I'
44                                                       AND bsdiff.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
45                                                                                            FROM    [msdb]..[backupset] b2
46                                                                                            WHERE   b2.[database_name] = bsfull.[database_name]
47                                                                                                    AND b2.[server_name] = bsfull.[server_name]
48                                                                                                    AND b2.[type] = N'I') )
49    WHERE   bsfull.[type] = N'D'
50            AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
51                                                 FROM   [msdb]..[backupset] b2
52                                                 WHERE  b2.[database_name] = bsfull.[database_name]
53                                                        AND b2.[server_name] = bsfull.[server_name]
54                                                        AND b2.[type] = N'D') )
55            AND EXISTS ( SELECT [name]
56                         FROM   [master].[sys].[databases]
57                         WHERE  [name] = bsfull.[database_name] )
58            AND bsfull.[database_name] NOT IN  (N'tempdb' , N'Master' ,N'Model' , N'MSDB')
59)
60SELECT  c.[server_name]  ,
61
62       -- c.[database_name] ,
63       -- d.[recovery_model_desc] ,
64        --c.[last_full_backup] ,
65        --c.[last_diff_backup] ,
66        --c.[last_tran_backup] ,
67        CASE 
68            WHEN c.[days_since_full_backup] <= 7 THEN 'Success'
69            WHEN c.[days_since_full_backup] >= 7 THEN 'Failed'
70        ELSE NULL END AS Full_Backup_Status_Weekly,
71            CASE 
72            WHEN c.[days_since_diff_backup] <= 1 THEN 'Success'
73            WHEN c.[days_since_diff_backup] >= 1 THEN 'Failed'
74        ELSE NULL END AS Diff_Backup_Status_Daily,
75        CASE 
76            WHEN c.[hours_since_tranlog_backup] <= 1 THEN 'Success'
77            WHEN c.[hours_since_tranlog_backup] >= 1 THEN 'Failed'
78        ELSE NULL END AS Transaction_log_backup_Hourly
79
80
81       --c.[days_since_full_backup] ,
82       -- c.[days_since_diff_backup] ,
83       -- c.[hours_since_tranlog_backup] 
84        --c.[full_backup_location] ,
85        --c.[diff_backup_location] ,
86        --c.[tlog_backup_location]
87FROM    [MostRecentBackupStatus_CTE] c
88        INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];
89
90
91        DROP Table tempdb..#BackupStatus