Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server / SQL Server 2014
Tip/Trick

SQL Server Database Backup Status Report

Rate me:
Please Sign up or sign in to vote.
4.63/5 (4 votes)
5 Jun 2015CPOL3 min read 30.9K   5   2
Know whether your databases are backed up successfully in the last 24 hours or not !!!

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.

SQL
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)

-- never backed up
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

Image 1

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".

SQL
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

Image 2

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

Image 3

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India
Dathuraj Pasarge is having around 10 years of experience in the IT industry, and he began his career as .NET and SQL Server Developer. He currently associated with IBM India, being a Sr.Database Administrator, supporting end to end migration activities, providing HADR solutions, and working on complex BAU issues for multiple clients on MSSQL and
MySQL platform. He is very passionate about delivering trainings, mentoring, and writing blogs on different Database Management system forums.

Comments and Discussions

 
QuestionHow to schedule to tun this through multiple servers Pin
Member 1487169223-Jun-20 16:37
Member 1487169223-Jun-20 16:37 
QuestionScript #1 Pin
Member 1239441615-Mar-16 9:34
Member 1239441615-Mar-16 9:34 
The script you posted for a backup status report is very close to what i need. Can you tell me how I can get it to check for success after each type of backup. Meaning, I want to know if the last full backup was successful/failed and is the last differential was successful/failed. From what it appears the code checks the status of the last backup. I need it to check the last backup status of each type(full and diff) of backup.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.