Problem: There are scenarios where you have to verify the multiple SQL Server instances backup within limited time frame whether it meet your organization backup criteria standards or not.
Solution:
You can execute below T-SQL script to check the backup status as per your organization standard by assigning backup conditions value to backup criteria variable. You can execute below script either by connecting each 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 will check the max backup date and evaluate result based on condition as backup is Passed or Not.
Backup Verification Script will return below values. Column names are self-explanatory.
• 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.
<br /> declare @full_backup_criteria_in_days int<br /> declare @diff_backup_criteria_in_days int<br /> declare @log_backup_criteria_in_hours int</p> <p>-- specify the backup criteria<br /> set @full_backup_criteria_in_days = 7 -- 7 day older Full Backup is Pass<br /> set @diff_backup_criteria_in_days = 1 -- 1 day older Diff Backup is Pass<br /> set @log_backup_criteria_in_hours = 2 -- 2 hours older Log Backup is Pass<br />
Complete Backup Verification Script
<br />
declare @full_backup_criteria_in_days int<br />
declare @diff_backup_criteria_in_days int<br />
declare @log_backup_criteria_in_hours int</p>
<p>-- specify the backup criteria<br />
set @full_backup_criteria_in_days = 7 -- 7 day older Full Backup is Pass<br />
set @diff_backup_criteria_in_days = 1 -- 1 day older Diff Backup is Pass<br />
set @log_backup_criteria_in_hours = 2 -- 2 hours older Log Backup is Pass</p>
<p>select<br />
serverproperty('servername') as SQLInstanceName,<br />
quotename(bkup_full.[database_name]) as DatabaseName,<br />
( select sdb.crdate<br />
from [master]..[sysdatabases] sdb<br />
where sdb.name = bkup_full.[database_name]<br />
) as [db_create_ddate],<br />
databasepropertyex(bkup_full.[database_name],'recovery') as DB_Recovery_Model,<br />
databasepropertyex(bkup_full.[database_name],'status') as dbstatus,<br />
case serverproperty('isclustered')<br />
when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)<br />
when 0 then cast(serverproperty('machinename') as varchar)<br />
end as windowsservername,<br />
serverproperty('productversion') as productversion,<br />
serverproperty('productlevel') as productlevel,<br />
serverproperty('edition') as edition,</p>
<p> current_timestamp as current_datetime,</p>
<p> bkup_full.[backup_finish_date] as [last_full_backup_date],<br />
bkup_diff.[backup_finish_date] as [last_diff_backup_date] ,<br />
bkup_log.[backup_finish_date] as [last_tran_backup_date] ,</p>
<p> datediff(dd, bkup_full.[backup_finish_date], current_timestamp) as [days_since_last_full_backup] ,<br />
datediff(dd, bkup_diff.[backup_finish_date], current_timestamp) as [days_since_last_diff_backup] ,<br />
datediff(hh, bkup_log.[backup_finish_date], current_timestamp) as [hours_since_last_tranlog_backup] ,</p>
<p> case<br />
when datediff(dd, bkup_full.[backup_finish_date], current_timestamp) <= @full_backup_criteria_in_days<br />
then 'Pass'<br />
else 'Fail'<br />
end as Full_Backup_Stauts,</p>
<p> case<br />
when datediff(dd, bkup_diff.[backup_finish_date], current_timestamp) <= @diff_backup_criteria_in_days then 'Pass'<br />
else case when quotename(bkup_full.[database_name]) IN ('[master]') then 'N/A' else 'Fail' end<br />
end as Diff_Backup_Stauts,</p>
<p> case<br />
when datediff(hh, bkup_log.[backup_finish_date], current_timestamp) <= @log_backup_criteria_in_hours then 'Pass'<br />
else case when databasepropertyex(bkup_full.[database_name],'recovery') = 'SIMPLE' then 'N/A' else 'Fail' end<br />
end as Log_Backup_Stauts, </p>
<p> ( select top 1 [physical_device_name]<br />
from [msdb]..[backupmediafamily] bkup_media_family<br />
where bkup_media_family.[media_set_id] = bkup_full.[media_set_id]<br />
) as [full_backup_location] ,<br />
( select top 1 [physical_device_name]<br />
from [msdb]..[backupmediafamily] bkup_media_family<br />
where bkup_media_family.[media_set_id] = bkup_diff.[media_set_id]<br />
) as [diff_backup_location] ,<br />
( select top 1 [physical_device_name]<br />
from [msdb]..[backupmediafamily] bkup_media_family<br />
where bkup_media_family.[media_set_id] = bkup_log.[media_set_id]<br />
) as [tlog_backup_location]</p>
<p> from [msdb]..[backupset] as bkup_full<br />
left join [msdb]..[backupset] as bkup_log on bkup_log.[database_name] = bkup_full.[database_name]<br />
and bkup_log.[server_name] = bkup_full.[server_name]<br />
and bkup_log.[type] = N'L'<br />
and bkup_log.[backup_finish_date] = ( (select max([backup_finish_date])<br />
from [msdb]..[backupset] b2<br />
where b2.[database_name] = bkup_full.[database_name]<br />
and b2.[server_name] = bkup_full.[server_name]<br />
and b2.[type] = N'L') )<br />
left join [msdb]..[backupset] as bkup_diff on bkup_diff.[database_name] = bkup_full.[database_name]<br />
and bkup_diff.[server_name] = bkup_full.[server_name]<br />
and bkup_diff.[type] = N'I'<br />
and bkup_diff.[backup_finish_date] = ( (select max([backup_finish_date])<br />
from [msdb]..[backupset] b2<br />
where b2.[database_name] = bkup_full.[database_name]<br />
and b2.[server_name] = bkup_full.[server_name]<br />
and b2.[type] = N'I') )<br />
where bkup_full.[type] = N'D'<br />
and bkup_full.[backup_finish_date] = ( (select max([backup_finish_date])<br />
from [msdb]..[backupset] b2<br />
where b2.[database_name] = bkup_full.[database_name]<br />
and b2.[server_name] = bkup_full.[server_name]<br />
and b2.[type] = N'D') )<br />
and exists ( select [name]<br />
from [master]..[sysdatabases]<br />
where [name] = bkup_full.[database_name] )<br />
and bkup_full.[database_name] <> N'tempdb'<br />
This script is the “Sustain” dimension
A backup you haven’t verified is a hope, not a plan — and this script is exactly the kind of continuous, boring verification that keeps a data platform honest. In my AI risk framework, this is Sustain: monitor and govern, so the other controls hold up in production. The framework: R-I-S-K-S: The 5-Dimension Framework for AI Risk Management
