Introduction
One of the most important DBA roles is to perform daily health check and make sure to have a valid database backup available in case of database corruption or for any other reasons. I have seen many backup status scripts on the SQL forum but most of them are missing essential columns or there are too many unwanted columns in the output. Using the below scripts, DBA should able to assess the backup status against hundreds and thousands of databases, and even it helps in troubleshooting the backup issues as part of the daily health check.
Background
There is no system procedure that tells you whether a database backup is successful or failed from a specific period. You take any environment surely every database must be backed up (either with full or differential) in the last 24 hours of time and whatever backup strategy you are using, it may be a weekly full and daily differential, or a monthly full and daily differential or a daily full, the listed 3 scripts could be used depending on your situation and requirement. The first query will be useful in-case you want to verify the last successful backup time and its status in the last 24 hours of time (by default parameter @check is set to 24, if needed you can change its value). The second script is mainly to validate the tlog backup status and the third and last script is a combination of both script 1 and script 2.
Script 3 is updated with few fixes and added new column DBStatus, hence I recommend to use last script (# 3) for the db backup health checks.
Script 1
If the backups (either Full or Differential) have to happen for every 24 hours on each SQL instance, then the below query helps you in identifying whether a database is successfully backed up or not.
SET NOCOUNT ON
declare @check int
set @check=24
declare @FinalAge int
declare @hf int
declare @hd int
declare @servername nvarchar(60)
declare @dbname nvarchar(60)
declare @lastFullBackup datetime
declare @lastDiffBackup datetime
declare @NotBackedupSinceHrs int
declare @status nvarchar(30)
DECLARE @table1 table (Servername nvarchar(60), DBName nvarchar(60),
LastFullBackup datetime, LastDiffBackup datetime,NotBackedupSince int,[Status] nvarchar(30))
declare c1 cursor for
Select Distinct convert(varchar(60),@@Servername) as Servername,
convert(varchar(60),e.database_name) as DBname,
(Select convert(varchar(25),Max(backup_finish_date) , 100)
From msdb..backupset a
Where a.database_name=e.database_name
and a.server_name = @@servername and type='D'
Group by a.database_name) Last_FullBackup,
(Select convert(varchar(25),Max(backup_finish_date) , 100)
From msdb..backupset c
where c.database_name=e.database_name
and c.server_name = @@servername
and type='I' Group by c.database_name) Last_Diff_Backup,
NULL as NotBackedupSinceHrs,NULL as [Status]
From msdb..backupset e
Where e.server_name = @@Servername and
e.database_name not in ('tempdb')
and e.database_name in (Select Distinct name from master..sysdatabases where dbid<>2)
Union all
select Distinct convert(varchar(60),@@Servername) as Servername,
convert(varchar(60),name) as DBname,
NULL, NULL,NULL as NotBackedupSinceHrs,NULL as [Status]
from master..sysdatabases as record
where name not in (select distinct database_name from msdb..backupset)and dbid<>2 order by 1,2
OPEN c1
FETCH NEXT FROM c1 INTO @servername,@dbname,@LastFullBackup,@LastDiffBackup,@NotBackedupSinceHrs,@status
WHILE @@FETCH_STATUS=0
BEGIN
IF (@LastFullBackup IS NULL)
BEGIN
set @LastFullBackup='1900-01-01 00:00:00.000'
END
IF (@LastDiffBackup IS NULL)
BEGIN
set @LastDiffBackup='1900-01-01 00:00:00.000'
END
select @hf=datediff(hh,@LastFullBackup,GETDATE())
select @hd=datediff(hh,@LastDiffBackup,GETDATE())
IF (@hf<@hd)
SET @FinalAge=@hf
ELSE
SET @FinalAge=@hd
INSERT INTO @table1 values (@servername,@dbname,@LastFullBackup,@LastDiffBackup,@FinalAge,@status)
FETCH NEXT FROM c1 INTO @servername,@dbname,@LastFullBackup,@LastDiffBackup,@NotBackedupSinceHrs,@status
END
UPDATE @table1 SET status = CASE
WHEN NotBackedUpSince <=@check THEN 'Success'
WHEN NotBackedUpSince > = @check THEN 'Failed, Action required !!!!'
END
UPDATE @table1 SET Status='Success'where DBName='master' and NotBackedUpSince< =@check +144
SELECT ServerName as 'SQLInstanceName',DBName as 'DatabaseName',LastFullBackup,LastDiffBackup,
NotBackedupSince as 'LastBackupHrs',Status FROM @table1 order by NotBackedUpSince desc
CLOSE c1
DEALLOCATE c1
Script 2
In some of the production environment, we may pass over to configure the transactional log backup even when the database recovery model is Full. This query helps you in identifying the database recovery model, last successful tlog backup datetime, log file size, status (such as "Success", "Failed!!", and "NA"). By default, I set parameter "@check" to 12 hour. If transactional log backup executed successfully in the last 12 hours, then status will be shown as "Success" else it will display as "Failed".
SET NOCOUNT ON
declare @check int
set @check= 12
declare @hl int
declare @servername nvarchar(60)
declare @dbname nvarchar(60)
declare @lastTlogBackup datetime
declare @NoTLogSince int
declare @Recovery nvarchar(20)
declare @TlogBkpStatus nvarchar(30)
DECLARE @serverVersion varchar(50)
select @serverVersion = CONVERT(varchar(50),SERVERPROPERTY('ProductVersion'))
SET @serverVersion = LEFT(@serverVersion, CHARINDEX('.', @serverVersion) - 1)
DECLARE @table1 table (Servername nvarchar(60), DBName nvarchar(60),lastTlogBackup datetime,
[Recovery] varchar(20),NoTLogSince int,TlogBkpStatus nvarchar(30))
declare c1 cursor for Select Distinct convert(varchar(60),@@Servername) as Servername,
convert(varchar(60),e.database_name) as DBname, (Select convert(varchar(25),
Max(backup_finish_date) , 100) From msdb..backupset c Where c.database_name=e.database_name
and c.server_name = @@servername and type='L' Group by c.database_name)
Last_Tlog_Backup, convert(varchar(20),convert(sysname,
DatabasePropertyEx (e.database_name,'Recovery'))) as Recovery,
NULL AS NoTLogSince,NULL as TlogBkpStatus FROM msdb..backupset e
WHERE e.server_name = @@Servername and e.database_name not in ('tempdb')
and e.database_name in (Select Distinct name from master..sysdatabases
where dbid not in (1,2,3))
Union all select Distinct convert(varchar(60),@@Servername) as Servername,
convert(varchar(60),name) as DBname,NULL,
convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),
NULL,NULL from master..sysdatabases as record
where name not in (select distinct database_name from msdb..backupset)
and dbid not in (1,2,3) order by 1,2
OPEN c1
FETCH NEXT FROM c1 INTO @servername,@dbname,@lastTlogBackup,@Recovery,
@NoTLogSince, @TlogBkpStatus
WHILE @@FETCH_STATUS=0
BEGIN
IF (@lastTlogBackup IS NULL)
BEGIN
set @lastTlogBackup='1900-01-01 00:00:00.000'
END
select @NoTLogSince=datediff(hour,@lastTlogBackup,GETDATE())
INSERT INTO @table1 values (@servername,@dbname,@lastTlogBackup,@Recovery,@NoTLogSince,
@TlogBkpStatus)
UPDATE @table1 SET TlogBkpStatus=CASE
WHEN NoTLogSince<=@check THEN 'Success'
WHEN NoTLogSince>=@check THEN 'Failed, Action required !!!!'
END
UPDATE @table1 SET TlogBkpStatus ='NA' where [Recovery]='SIMPLE' OR DBName='model'
FETCH NEXT FROM c1 INTO @servername,@dbname,@lastTlogBackup,@Recovery,
@NoTLogSince,@TlogBkpStatus
END
IF convert(int,@serverVersion)>=9
BEGIN
SELECT ServerName as 'SQLInstanceName',DBName as 'DatabaseName',
(mf.size*8)/1024 as LogFileSize_inMB,LastTlogBackup ,[Recovery] ,
[NoTLogSince] as [NoTLogSince_Hrs],TlogBkpStatus
FROM @table1 tv inner join master.sys.master_files mf
on tv.DBName=(select db_name(mf.database_id))
where mf.type_desc='LOG' and mf.file_id=2 order by NoTLogSince desc
END
IF convert(int,@serverVersion)<9
BEGIN
SELECT ServerName as 'SQLInstanceName',DBName as 'DatabaseName',
(mf.size*8)/1024 as LogFileSize_inMB,LastTlogBackup ,[Recovery] ,
[NoTLogSince] as [NoTLogSince_Hrs],TlogBkpStatus FROM @table1 tv
inner join master..sysaltfiles mf on tv.DBName=(select db_name(mf.dbid))
where mf.fileid=2 order by NoTLogSince desc
END
CLOSE c1
DEALLOCATE c1
Script 3
The below query is a combination of both script 1 and 2.
SET NOCOUNT ON
declare @check int
set @check=24 -- In hours, based on backup status will be updated, if no full or
declare @CheckTlog int
set @CheckTlog=4 -- In hours, based on tlog backup, status will be updated.
declare @LastFullBackup datetime
declare @LastDiffBackup datetime
declare @lastTlogBackup datetime
declare @NotBackedupSinceHrs int
declare @NoTLogSince int
declare @status nvarchar(40)
declare @Recovery nvarchar(20)
declare @TlogBkpStatus nvarchar(40)
declare @dbstatus nvarchar(30)
declare @FinalAge int
declare @hf int
declare @hd int
declare @hl int
declare @ServerName nvarchar(60)
declare @dbname nvarchar(60)
DECLARE @table1 table (Servername nvarchar(60), DBName nvarchar(60),LastFullBackup datetime,
LastDiffBackup datetime,NotBackedUpSince int,dbstatus nvarchar(30), [Status] nvarchar(40),lastTlogBackup datetime,
[Recovery] varchar(20),NoTLogSince int,TlogBkpStatus nvarchar(40))
declare c1 cursor for Select Distinct convert(varchar(60),@@Servername) as Servername,
convert(varchar(60),e.database_name) as DBname,
(Select convert(varchar(25),Max(backup_finish_date) , 100)
FROM msdb..backupset a Where a.database_name=e.database_name
and a.server_name = @@servername and type='D'
Group by a.database_name) Last_FullBackup,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb..backupset c
Where c.database_name=e.database_name and c.server_name = @@servername
and type='I' Group by c.database_name) Last_Diff_Backup, NULL as NotBackedUpSinceHrs, NULL as [DBStatus],
NULL as [Status], (Select convert(varchar(25),Max(backup_finish_date),100)
From msdb..backupset c Where c.database_name=e.database_name
and c.server_name = @@servername
and type='L' Group by c.database_name) Last_Diff_Backup,
convert(varchar(20),convert(sysname,DatabasePropertyEx (e.database_name,'Recovery'))) as Recovery,
NULL, NULL as TlogBkpStatus From msdb..backupset e WHERE
e.server_name = @@Servername and e.database_name not in ('tempdb')
and e.database_name in (Select Distinct name from master..sysdatabases where dbid<>2)
UNION ALL
SELECT DISTINCT convert(varchar(60),@@Servername) as Servername,
convert(varchar(60),name) as DBname,NULL, NULL,NULL as NotBackedUpSinceHrs,NULL AS [DBStatus],
NULL as [Status],NULL,convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),
NULL,NULL from master..sysdatabases as record
WHERE name NOT IN (SELECT DISTINCT database_name FROM msdb..backupset)
and dbid<>2 ORDER BY 1,2
OPEN c1
FETCH NEXT FROM c1 INTO @ServerName,@dbname,@LastFullBackup,@LastDiffBackup,
@NotBackedupSinceHrs,@dbstatus,@status,@lastTlogBackup,@Recovery, @NoTLogSince, @TlogBkpStatus
WHILE @@FETCH_STATUS=0
BEGIN
IF (@LastFullBackup IS NULL)
BEGIN
set @LastFullBackup='1900-01-01 00:00:00.000'
END
IF (@LastDiffBackup IS NULL)
BEGIN
set @LastDiffBackup='1900-01-01 00:00:00.000'
END
IF (@lastTlogBackup IS NULL)
BEGIN
set @lastTlogBackup='1900-01-01 00:00:00.000'
END
select @hf=datediff(hh,@LastFullBackup,GETDATE())
select @hd=datediff(hh,@LastDiffBackup,GETDATE())
select @NoTLogSince=datediff(hh,@lastTlogBackup,GETDATE())
IF (@hf<@hd)
SET @FinalAge=@hf
ELSE
SET @FinalAge=@hd
SET @NotBackedupSinceHrs=@FinalAge
--set @dbstatus= null
set @dbstatus=(select convert(varchar(20),DATABASEPROPERTYEX(@dbname,'status')))
--UPDATE @table1 SET [Status]='DB in ' + @dbstatus +' state' where dbStatus<>'ONLINE'
--print @dbstatus
--print @dbname
INSERT INTO @table1 values (@ServerName,@dbname,@LastFullBackup,@LastDiffBackup,
@NotBackedupSinceHrs,@dbstatus,@status,@lastTlogBackup,@Recovery,@NoTLogSince,@TlogBkpStatus)
--set @dbstatus= null
UPDATE @table1 SET [Status] = CASE
WHEN NotBackedUpSince <=@check THEN 'Success'
WHEN NotBackedUpSince > = @check THEN '!!! Failed, Action required !!!!'
END
--Print @dbstatus
UPDATE @table1 SET Status=@dbstatus where dbstatus<>'ONLINE'
UPDATE @table1 SET Status='Success'where DBName='master' and NotBackedUpSince< =@check +144
UPDATE @table1 SET TlogBkpStatus=CASE
WHEN NoTLogSince<=@CheckTlog THEN 'Success'
WHEN NoTLogSince>=@CheckTlog THEN '!!! Failed, Action required !!!!'
END
UPDATE @table1 SET TlogBkpStatus ='NA' where [Recovery]='SIMPLE' OR DBName='model'
--print @dbstatus
FETCH NEXT FROM c1 INTO @ServerName,@dbname,@LastFullBackup,@LastDiffBackup,
@NotBackedupSinceHrs,@dbstatus,@status,
@lastTlogBackup,@Recovery, @NoTLogSince,@TlogBkpStatus
END
UPDATE @table1 SET Status='Not in Online',TlogBkpStatus='Not in Online' where dbstatus <>'ONLINE'
SELECT Servername as 'SQLInstanceName',DBName as 'DatabaseName',LastFullBackup,
LastDiffBackup,NotBackedUpSince as 'LastBackup_Hrs',dbstatus,[Status] as 'Backup Status',lastTlogBackup , [Recovery] ,
NoTLogSince,TlogBkpStatus FROM @table1 order by DBName
CLOSE c1
DEALLOCATE c1
Points of Interest
As per your requirement, you can set the period (in hours) using @check and @checkTLog variables. For instance, if you are interested to know the tlog backups failing in the last one hour, you can set @checkTLog to 1.
You can execute the above queries using SQLCMD as a batch file, to run it on multiple servers to save the query output to a csv or text file and thereby automating with the help of Windows scheduler task, and also you can run it manually from the Central Management Server (CMS) to get the query results from multiple registered instances.
As differential backup cannot be taken for master database, so weekly full backup is considered as success in the report.
History
July 2018 --> Script 3 is updated with few fixes and added new column DBStatus, hence I recommend to use last script (# 3) for the db backup health checks.
