Technical Article

Backup history details EMAIL (past 24 hours) in HTML format


Step 1: Create the view in your dba management database.

Step 2: Run the email section T-SQL which will send an email.

FYI - You need to create database mail in SQL Server and enable all required mail properties prior to using this script.

Reference :

Step 3: Create a job which is scheduled to run daily executing the mail T-SQL script. (This script is not included)


Use Cases:

1. In a busy SQL Server where we have 100's of databases taken using scripts, maintenance plans, backup tape media etc to different locations and we don't know where the backup is located. 

2. This script email will be handy to get the database name, backup date, backup time and backup location, which will be useful to inform the storage admin or backup admin for the location that needs to be restored from backup disk or tape.

3. Audit for previous backups. If backup is not taken in servers like QA, DEV, UAT etc. 


Step 1: Create a view which is easy to manage.

CREATE view [dbo].[Backup_Status] as SELECT as Database_Name, 
ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NO BACKUP') as DaysSinceLastBackup,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 101), 'NOT APPLICABLE') as LastBackupDate,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 108), 'NOT APPLICABLE') as LastBackupTime,
bkpfmly.physical_device_name as File_Location
FROM sys.sysdatabases sdb 
LEFT OUTER JOIN msdb.dbo.backupset bkpst with (readpast) ON bkpst.database_name = AND bkpst.type = 'D' 
LEFT OUTER JOIN msdb.dbo.backupmediafamily bkpfmly with (readpast) ON bkpfmly.media_set_id = bkpst.media_set_id
GROUP BY sdb.Name,physical_device_name
HAVING CONVERT(nvarchar(10),MAX(Backup_finish_date),20) between CONVERT(nvarchar(10),(GETDATE() - 1),20) AND CONVERT(nvarchar(10),(GETDATE()),20)

Note: This script provides the backup history for the past 24 hours. If you want more days than just 24 hours change the HAVING section.

EX:- 3 days
HAVING CONVERT(nvarchar(10),MAX(Backup_finish_date),20) between CONVERT(nvarchar(10),(GETDATE() - 3),20) AND CONVERT(nvarchar(10),(GETDATE()),20)
In case you need the complete history of backup comment the HAVING section.

HTML Email:


SET @xml = CAST(( SELECT [Database_Name] AS 'td','',[DaysSinceLastBackup] AS 'td','',
       [LastBackupDate] AS 'td','', [LastBackupTime] AS 'td','',[File_Location] AS 'td',''
FROM  Backup_Status ORDER BY LastBackupTime asc

SET @body ='<html><body><H3>Please find the values for Database Backup details</H3>
<table border = 1> 
<th bgcolor="#66CCFF">  Database_Name </th> <th bgcolor="#66CCFF">  DaysSinceLastBackup </th> <th bgcolor="#66CCFF">  LastBackupDate </th> <th bgcolor="#66CCFF">  LastBackupTime </th> <th bgcolor="#66CCFF">  File_Location </th></tr>'    

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = '<SQL Database Mail Profile Name>',
@body = @body,
@body_format ='HTML',
@recipients = '<Notification Group email Address>',
@subject ='SQL Server Backup Status' ;

Please email me if you have any questions to


4.25 (8)

You rated this post out of 5. Change rating




4.25 (8)

You rated this post out of 5. Change rating