how to set up sql server alerts based on a schedule basis?

Question

We would like to stop getting sql server alerts from dev servers during the weekend. We have alerts for severity 016 to 025.

Answer 1

Probably the easiest way I can think of is to set up an agent job to disable/enable the agent on a schedule.

https://learn.microsoft.com/en-us/sql/ssms/agent/disable-or-reactivate-an-alert?view=sql-server-2017

https://www.mssqltips.com/sqlservertip/4848/getting-started-with-sql-server-agent--part-1/

-- changes the enabled setting of Test Alert to 0  
USE msdb ;  
GO  

EXEC dbo.sp_update_alert  
    @name = N'Test Alert',  
    @enabled = 0 ;  
GO  

You can generate the required code for all alerts that fire a notification by running this:

USE msdb;
DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
' END + N'EXEC dbo.sp_update_alert @name = N''' + sa.name + ''', @enabled = 0;'
FROM dbo.sysalerts sa
WHERE sa.has_notification = 1
ORDER BY sa.name DESC;
PRINT @cmd;

This will generate the required code to re-enable those alerts:

USE msdb;
DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
' END + N'EXEC dbo.sp_update_alert @name = N''' + sa.name + ''', @enabled = 0;'
FROM dbo.sysalerts sa
WHERE sa.has_notification = 1
ORDER BY sa.name DESC;
PRINT @cmd;

I'd add the generated "disable" code to an agent job that runs on Friday evening, and the "enable" code into a job that runs on Monday morning.