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.
