SQL – Backup Report

The simple query to find the database backup status

The select statment consists of

  1. ServerName
  2. dbname
  3. BackupStartDate
  4. BackupFinishDate
  5. BackupAge
  6. Size
  7. status
  8. Type

The query will only run from sql 2005 and later version.

Select  
 SERVERPROPERTY('ServerName'), 
 db.name, 
 CONVERT(VARCHAR(10), b.backup_start_date, 103) +   + convert(VARCHAR(8), b.backup_start_date, 14) backup_start_date, 
 CONVERT(VARCHAR(10), b.backup_finish_date, 103) +   + convert(VARCHAR(8), b.backup_finish_date, 14) backup_finish_date, 
  case 
        when (DATEDIFF(hour, b.backup_start_date, getdate())<24)then 'Success' 
        when (DATEDIFF(hour, b.backup_start_date, getdate())>=24)then 'Failed' 
      end Status, 
DATEDIFF(hh, b.backup_finish_date, GETDATE())BackupAgeInHours, 
(b.backup_size/1024/1024/1024 )BackupSize, 
case b.[type] 
WHEN 'D' THEN 'Full' 
WHEN 'I' THEN 'Differential' 
WHEN 'L' THEN 'Transaction Log' 
END Type, 
ISNULL(STR(ABS(DATEDIFF(day, GetDate(),(Backup_finish_date)))), 'NEVER')DaysSinceLastBackup 
FROM sys.sysdatabases db  
Left OUTER JOIN (SELECT * , ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY backup_finish_date DESC) AS RNUM 
FROM msdb.dbo.backupset) b ON b.database_name = db.name  AND RNUM = 1 
where dbid<>2
 The Second part is for sending an HTML Email. This requires a dbmail configured on the server.
DECLARE @tableHTML  NVARCHAR(MAX) ; 

 SET @tableHTML = 
  N'<H1>Databases Backup Report</H1>' + 
  N'<table border="1">' + 
  N'<tr><th>Server Name</th><th>DatabaseName</th> 
  <th>[BackupStartDate]</th> 
  <th>[BackupFinishDate]</th> 
  <th>[Status24hrs]</th> 
  <th>[BackupAge (Hours)]</th> 
  <th>BackupSizeGB</th> 
  <th>Type</th> 
  <th>DaysSinceLastBackup</th> 
  </tr>' + 
  CAST ( (    
  Select  
  td=SERVERPROPERTY('ServerName'),' ', 
  td=db.name,' ', 
  td =CONVERT(VARCHAR(10), b.backup_start_date, 103) +   + convert(VARCHAR(8), b.backup_start_date, 14),' ', 
  td=CONVERT(VARCHAR(10), b.backup_finish_date, 103) +   + convert(VARCHAR(8), b.backup_finish_date, 14),' ', 
  td= case 
        when (DATEDIFF(hour, b.backup_start_date, getdate())<24)then 'Success' 
        when (DATEDIFF(hour, b.backup_start_date, getdate())>=24)then 'Failed' 
      end,' ', 
td= DATEDIFF(hh, b.backup_finish_date, GETDATE()),' ', 
td=(b.backup_size/1024/1024/1024 ),' ', 
td=case b.[type] 
WHEN 'D' THEN 'Full' 
WHEN 'I' THEN 'Differential' 
WHEN 'L' THEN 'Transaction Log' 
END ,' ', 
td=ISNULL(STR(ABS(DATEDIFF(day, GetDate(),(Backup_finish_date)))), 'NEVER'),' ' 
FROM sys.sysdatabases db  
Left OUTER JOIN (SELECT * , ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY backup_finish_date DESC) AS RNUM 
FROM msdb.dbo.backupset) b ON b.database_name = db.name  AND RNUM = 1 
where dbid<>2 
      FOR XML PATH('tr'), TYPE  
  ) AS NVARCHAR(MAX) ) + 
  N'</table>' ; 

 EXEC msdb.dbo.sp_send_dbmail @recipients='pjayaram@appvion.com', 
  @subject = 'Database Backup', 
  @body = @tableHTML, 
  @body_format = 'HTML' ;
Output:-
About these ads

About Prashanth Jayaram

I’m a Database technologist having 8+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell. hobbies are playing sports and drawing.
This entry was posted in SQL, XML and tagged . Bookmark the permalink.

2 Responses to SQL – Backup Report

  1. balu says:

    thanks for the script..Much appreciated

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s