March 18, 2016 at 2:10 am
hi
i am sing a stored procedure in order to backup my DBs. then i shedule it in a job .
in order to Verify a success of database backups i am using
USE [msdb]
GO
;WITH [MostRecentBackupStatus_CTE]
AS
(
SELECT bsfull.[server_name] ,
bsfull.[database_name] ,
bsfull.[backup_finish_date] AS [last_full_backup] ,
bsdiff.[backup_finish_date] AS [last_diff_backup] ,
bstlog.[backup_finish_date] AS [last_tran_backup] ,
DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bsfull.[media_set_id]
) AS [full_backup_location] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bsdiff.[media_set_id]
) AS [diff_backup_location] ,
( SELECT [physical_device_name]
FROM [msdb]..[backupmediafamily] bmf
WHERE bmf.[media_set_id] = bstlog.[media_set_id]
) AS [tlog_backup_location]
FROM [msdb]..[backupset] AS bsfull
LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
AND bstlog.[server_name] = bsfull.[server_name]
AND bstlog.[type] = 'L'
AND bstlog.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = 'L') )
LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
AND bsdiff.[server_name] = bsfull.[server_name]
AND bsdiff.[type] = 'I'
AND bsdiff.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = N'I') )
WHERE bsfull.[type] = N'D'
AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
FROM [msdb]..[backupset] b2
WHERE b2.[database_name] = bsfull.[database_name]
AND b2.[server_name] = bsfull.[server_name]
AND b2.[type] = N'D') )
AND EXISTS ( SELECT [name]
FROM [master].[sys].[databases]
WHERE [name] = bsfull.[database_name] )
AND bsfull.[database_name] <> N'tempdb'
AND bsfull.[database_name] IN ('MSCRM','MSCRM_CONFIG','CRMConnectivity')
and convert(char(10),bsfull.[backup_finish_date],126)=CONVERT(char(10),GETDATE(),126)
)
SELECT c.[server_name] ,
c.[database_name] ,
d.[recovery_model_desc] ,
c.[last_full_backup] ,
c.[last_diff_backup] ,
c.[last_tran_backup] ,
c.[days_since_full_backup] ,
c.[days_since_diff_backup] ,
c.[hours_since_tranlog_backup] ,
c.[full_backup_location] ,
c.[diff_backup_location] ,
c.[tlog_backup_location]
FROM [MostRecentBackupStatus_CTE] c
INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];
GO
i will appreciate if you tell me how can i send an email( notification) to some recipients when the backup is succesful or if there is a problem. any help will appreciate
thanks
March 18, 2016 at 2:41 am
Don't send alerts on success. It's just noise that people will ignore. Alerts should be when something has gone wrong.
Set up a job to test-restore your database backups (just having taken it doesn't mean it's restorable) and configure the job to send email if it fails (it's one of the steps of the job creation dialogues)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2016 at 2:46 am
georgheretis (3/18/2016)
hii am sing a stored procedure in order to backup my DBs. then i shedule it in a job .
in order to Verify a success of database backups i am using
USE [msdb]
GO
;WITH [MostRecentBackupStatus_CTE]
AS
(
EDIT
)
SELECT c.[server_name] ,
c.[database_name] ,
d.[recovery_model_desc] ,
c.[last_full_backup] ,
c.[last_diff_backup] ,
c.[last_tran_backup] ,
c.[days_since_full_backup] ,
c.[days_since_diff_backup] ,
c.[hours_since_tranlog_backup] ,
c.[full_backup_location] ,
c.[diff_backup_location] ,
c.[tlog_backup_location]
FROM [MostRecentBackupStatus_CTE] c
INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];
GO
i will appreciate if you tell me how can i send an email( notification) to some recipients when the backup is succesful or if there is a problem. any help will appreciate
thanks
It's actually reasonably straightforward. You need to add another step or two to your job. The first task to add is a step to send an email when everything's ok.
Add the step as a Transact-SQL script and in the code window enter something like this:
EXEC msdb.dbo.sp_send_dbmail
@from_address = 'DO_Not_repy@whoeveryouare.com',
@recipients = 'whoever@needstoknow.co.uk',
@body = 'Everything is awesome.',
@subject = 'Backup Successful'.
Save this step.
Next, go to the step that runs your backup and, on the advanced tab, where it says 'On success action:', set that to go to the step you've just created.
After that, create another Transact-SQL script step. This time in the code window enter something like:EXEC msdb.dbo.sp_send_dbmail
@from_address = 'DO_Not_repy@whoeveryouare.com',
@recipients = 'whoever@needstoknow.co.uk',
@body = 'Everything is not awesome.',
@subject = 'Backup failed'.
When you've done that, return to your backup step and click on the advanced tab again. This time set the 'On failure action:' to go to the step you created to send a failure email.
It seems pretty involved but it's easy enough.
And one more thing, you don't need to start a CTE with a semi-colon. The previous statement must be ended with one but if it's the first statement in a batch they're unnecessary.
Don't send alerts on success. It's just noise that people will ignore. Alerts should be when something has gone wrong.
Good point Gail, the first five minutes of my day is spent deleting the everything's ok emails.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply