Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

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:-

Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...