June 4, 2016 at 1:07 am
I'll be quick on this one. Basically, I have a requirement to develop a script that would provide the client a backup report with the following details listed.
- Db name
- Backup Type (Full/Diff/Tlog)
- Backup Length - time it took to run the backup
- Backup Size - the size of the backup file
- Backup Status - if the backup completed or failed.
Now, if the backup failed the client would like to receive an email providing them the database name, and the error message for the failure.
I have attached the script that I am working on. Please feel free to modify, or suggest me something better, and let me know firstly how I would handle the backup status message...it seems to be really tricky, and then also to incorporate the code into an html report sent out to the client everyday.
Regards,
Feivel
June 4, 2016 at 7:15 am
you can not get directly for the failed db backup. you can only find the failed job report however you want to find which databases are not been backed up then you can send an alert based on that.
you can find the below query which i used it for the same reason.
SELECT s.name AS Database_Name, s.Recovery_model_desc, CASE WHEN b.backup_set_id IS NULL THEN 'NO Backup' ELSE 'Backup Complete' END AS Backup_Completed,b.backup_start_date , b.backup_finish_date,
DATEDIFF(MINUTE, b.backup_start_date, b.backup_finish_date) completion_min ,(DATEDIFF(DAY,b.backup_finish_date,GETDATE())) Days_Since_Last_Backup, lb.Last_Backup_Finish_DateTime Differential_Finish_Time,(DATEDIFF(DAY,lb.Last_Backup_Finish_DateTime ,GETDATE())) Days_Since_Last_Differential_Backup,ll.Last_Backup_Finish_DateTime Last_Log_Finish_DateTime,
(DATEDIFF(DAY,ll.Last_Backup_Finish_DateTime,GETDATE()))AS Days_Since_Last_Log_Backup,ll.Log_Backup_Size_MB AS LOG_Backup_Size_MB,CASE WHEN b.[type] = 'D' THEN 'Full Backup' WHEN b.[type] = 'I' THEN 'Differential Database'
WHEN b.[type] = 'L' THEN 'Log' WHEN b.[type] = 'F' THEN 'File/Filegroup' WHEN b.[type] = 'G' THEN 'Differential File' WHEN b.[type] = 'P' THEN 'Partial' WHEN b.[type] = 'Q'THEN 'Differential partial' END AS Backup_Type,
ROUND(((b.backup_size/1024)/1024),2) AS Backup_Size_MB
FROM MASTER.sys.databases s LEFT OUTER JOIN msdb.dbo.backupset b ON s.name = b.database_name LEFT OUTER JOIN msdb.dbo.backupmediafamily bf ON b.media_set_id = bf.media_set_id
INNER JOIN (SELECT s.name AS Name,MAX(b.backup_finish_date) AS Last_Backup_Finish_DateTime
FROM MASTER.sys.databases s LEFT OUTER JOIN msdb.dbo.backupset b ON s.name = b.database_name
WHERE s.name<>'TempDB' AND b.[type] ='D' OR b.[type] IS NULL
GROUP BY s.name) l ON s.Name=l.Name AND ISNULL(b.backup_finish_date,'01/01/1900') = ISNULL(l. Last_Backup_Finish_DateTime,'01/01/1900') LEFT OUTER JOIN
(SELECT s.name AS Name, MAX(b.backup_finish_date) AS Last_Backup_Finish_DateTime
FROM MASTER.sys.databases s LEFT OUTER JOIN msdb.dbo.backupset b ON s.name = b.database_name
WHERE s.name<>'TempDB' AND b.[type] ='I' GROUP BY s.name) lb ON s.Name=lb.Name LEFT OUTER JOIN (SELECT Name,Backup_Finish_DateTime AS Last_Backup_Finish_DateTime,Backup_Size_MB AS Log_Backup_Size_MB
FROM (SELECT s.name AS Name, b.backup_finish_date AS Backup_Finish_DateTime,
ROUND(((b.backup_size/1024)/1024),2) AS Backup_Size_MB, ROW_NUMBER() OVER(Partition by s.name ORDER BY b.backup_finish_date DESC) AS RevOrderBuDate
FROM MASTER.sys.databases s LEFT OUTER JOIN msdb.dbo.backupset b ON s.name = b.database_name
WHERE s.name<>'TempDB' AND b.[type] ='L') a WHERE RevOrderBuDate=1) ll ON s.Name=ll.Name WHERE s.name<>'TempDB'
June 5, 2016 at 2:28 am
Thanks!. I would also need some HTML formatting to send an email to the client...would you be able to help.
June 6, 2016 at 9:05 pm
Give me the html
Format you would like to send via email
July 4, 2016 at 6:44 am
It should be a simple HTML format...same as what they have shown in one of the examples in books online. Can you provide me with that formatting?
Regards,
Feivel.
July 4, 2016 at 8:31 am
ffarouqi (7/4/2016)
It should be a simple HTML format...same as what they have shown in one of the examples in books online. Can you provide me with that formatting?Regards,
Feivel.
If you have an example of the HTML formatting you want, just adapt it to work for your report. This will also help you learn how to do it so you'll be able to support it and create other reports like it in the future.
July 5, 2016 at 6:44 am
ffarouqi (7/4/2016)
It should be a simple HTML format...same as what they have shown in one of the examples in books online. Can you provide me with that formatting?Regards,
Feivel.
Formatting table output is fairly easy. Lookup sp_Send_DBMail and look for example "C. Sending an HTML e-mail message".
Also, the most reliable way to get information on failure is to read the SQL Server log and combine that information with the successes from MSDB.
EXEC sp_readerrorlog 0, 1, 'BACKUP failed'
;
For more information on that tool, see the following article.
https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply