In this post, I will explain you about a script to verification on SQL Server 2014. There are situations wherever you have got to verify the multiple SQL Server instances backup within limited timeframe whether or not it meet your organization backup criteria standards or not.

You can execute below T-SQL script to see the backup status as per your organization normal by assignment backup conditions value to backup criteria variable. You'll execute below script either by connecting every SQL Server Instance or execute it by registering all the SQL Server Instances in Central Management Server (CMS).

Below T-SQL Script work with all the versions of MSSQL Server and verify FULL, Differential and transaction Log Backup. It'll check the max backup date and appraise result based on condition as backup is Passed or Not.

And this is the Backup Verification Script and it will return below values:

  • SQLInstanceName
  • DatabaseName
  • db_create_ddate
  • DB_Recovery_Model
  • dbstatus
  • windowsservername
  • productversion
  • productlevel
  • edition
  • current_datetime
  • last_full_backup_date
  • last_diff_backup_date
  • last_tran_backup_date
  • days_since_last_full_backup
  • days_since_last_diff_backup
  • hours_since_last_tranlog_backup
  • Full_Backup_Stauts
  • Diff_Backup_Stauts
  • Log_Backup_Stauts
  • full_backup_location
  • diff_backup_location
  • tlog_backup_location

You have to specify the backup condition based on your organization standard by assigning values to below variables.
declare @full_backup_criteria_in_days int
declare @diff_backup_criteria_in_days int
declare @log_backup_criteria_in_hours int
 -- specify the backup criteria
set @full_backup_criteria_in_days = 7 -- 7 day older Full Backup is Pass
set @diff_backup_criteria_in_days = 1 -- 1 day older Diff Backup is Pass
set @log_backup_criteria_in_hours = 2 -- 2 hours older Log Backup is Pass

This is the complete script that I used:
declare @full_backup_criteria_in_days int
declare @diff_backup_criteria_in_days int
declare @log_backup_criteria_in_hours int
-- specify the backup criteria
set @full_backup_criteria_in_days = 7 -- 7 day older Full Backup is Pass
set @diff_backup_criteria_in_days = 1 -- 1 day older Diff Backup is Pass
set @log_backup_criteria_in_hours = 2 -- 2 hours older Log Backup is Pass
select   
           serverproperty('servername') as SQLInstanceName,
           quotename(bkup_full.[database_name]) as DatabaseName,
            ( select    sdb.crdate
              from      [master]..[sysdatabases] sdb
              where     sdb.name = bkup_full.[database_name]
            ) as [db_create_ddate],         
           databasepropertyex(bkup_full.[database_name],'recovery')  as DB_Recovery_Model,
           databasepropertyex(bkup_full.[database_name],'status')  as dbstatus,     
           case serverproperty('isclustered')
                        when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
                        when 0 then cast(serverproperty('machinename') as varchar)
           end as windowsservername,
           serverproperty('productversion') as productversion,
           serverproperty('productlevel') as productlevel,
           serverproperty('edition') as edition,
            current_timestamp as current_datetime,           
           bkup_full.[backup_finish_date] as [last_full_backup_date],         
           bkup_diff.[backup_finish_date] as [last_diff_backup_date] ,
           bkup_log.[backup_finish_date] as [last_tran_backup_date] ,
           datediff(dd, bkup_full.[backup_finish_date], current_timestamp) as [days_since_last_full_backup] ,
           datediff(dd, bkup_diff.[backup_finish_date], current_timestamp) as [days_since_last_diff_backup] ,
           datediff(hh, bkup_log.[backup_finish_date], current_timestamp) as [hours_since_last_tranlog_backup] ,            
           case
                        when datediff(dd, bkup_full.[backup_finish_date], current_timestamp) <= @full_backup_criteria_in_days
                        then 'Pass'
                        else 'Fail'
                        end as Full_Backup_Stauts,            
            case
                        when datediff(dd, bkup_diff.[backup_finish_date], current_timestamp) <= @diff_backup_criteria_in_days  then 'Pass'
                        else case when quotename(bkup_full.[database_name]) IN ('[master]') then 'N/A' else 'Fail' end                      
                        end as Diff_Backup_Stauts,
            case
                        when datediff(hh, bkup_log.[backup_finish_date], current_timestamp) <= @log_backup_criteria_in_hours  then 'Pass'
                        else case when databasepropertyex(bkup_full.[database_name],'recovery') = 'SIMPLE' then 'N/A' else 'Fail' end                      
            end as Log_Backup_Stauts,          
             ( select top 1 [physical_device_name]
              from      [msdb]..[backupmediafamily] bkup_media_family
              where     bkup_media_family.[media_set_id] = bkup_full.[media_set_id]
            ) as [full_backup_location] ,
            ( select top 1  [physical_device_name]
              from      [msdb]..[backupmediafamily] bkup_media_family
              where     bkup_media_family.[media_set_id] = bkup_diff.[media_set_id]
            ) as [diff_backup_location] ,
            ( select top 1  [physical_device_name]
              from      [msdb]..[backupmediafamily] bkup_media_family
              where     bkup_media_family.[media_set_id] = bkup_log.[media_set_id]
            ) as [tlog_backup_location]                                       
    from    [msdb]..[backupset] as bkup_full
            left join [msdb]..[backupset] as bkup_log on bkup_log.[database_name] = bkup_full.[database_name]
               and bkup_log.[server_name] = bkup_full.[server_name]
               and bkup_log.[type] = N'L'
               and bkup_log.[backup_finish_date] = ( (select  max([backup_finish_date])
               from    [msdb]..[backupset] b2  where   b2.[database_name] = bkup_full.[database_name]                                                                                                    and b2.[server_name] = bkup_full.[server_name]                                                                                                    and b2.[type] = N'L') )
            left join [msdb]..[backupset] as bkup_diff on bkup_diff.[database_name] = bkup_full.[database_name]
               and bkup_diff.[server_name] = bkup_full.[server_name]                                                     
               and bkup_diff.[type] = N'I'
               and bkup_diff.[backup_finish_date] = ( (select max([backup_finish_date])
                 from    [msdb]..[backupset] b2
                 where   b2.[database_name] = bkup_full.[database_name]
                 and b2.[server_name] = bkup_full.[server_name]
                 and b2.[type] = N'I') )
                 where   bkup_full.[type] = N'D'
                and bkup_full.[backup_finish_date] = ( (select  max([backup_finish_date])
                       from   [msdb]..[backupset] b2
                       where  b2.[database_name] = bkup_full.[database_name]
                       and b2.[server_name] = bkup_full.[server_name]
                                                        and b2.[type] = N'D') )
            and exists ( select [name]
                         from   [master]..[sysdatabases]
                         where  [name] = bkup_full.[database_name] )
            and bkup_full.[database_name] <> N'tempdb'

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.