Way to test alerts for backups? We have some SQL Servers that are backed up using T-SQL and some that are using 3rd party backups (like Egnyte).

Is there a way to setup alerts inside SQL Server for that? I also need a way to test so we can be sure the alerts are working.

Any help is appreciated.
Note: This is for Microsoft SQL Server.

For testing purpose, I downloaded a corrupt database backup file from SQLSkills.com

Below is a quick script that will help you (obviously you can adapt the logic to do it dynamically for all your databases and send email in a nice HTML format) :

begin try
    BACKUP DATABASE broken TO DISK='D:\broken2005\broken2.bck'
    WITH CHECKSUM  --- very Important 
end try
begin catch

    declare @errorSubject char(100)
    declare @errorBody varchar(max)

    set @errorSubject = 'Full backup failed for database Broken on ' +@@servername
    set @errorBody = 'Full backup failed '+ char(10)+Cast(Error_Number() as nvarchar)+' - ['+Error_Message()+']'

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'databaseMail Profile',
    @recipients ='DBATeamEmailAddress@somecompany.com',
    @body = @errorBody,
    @subject = @errorSubject

end catch


Alternatively, you can use SQL Server Backup solution from Ola Hallengren.