Scheduling a SQL Agent Job to Run on a Calculated Day that isn’t included in the Regular Scheduler Options

I’ve had a hard time coming up with a title for this post, because while the concept is easy to understand, it’s difficult to summarize.
We refresh one of our development instances on the first Monday of each month.  That’s an easy thing to schedule, as it’s an option within the SQL Agent scheduler:

We want to send a notification to the affected users beforehand, however, and since the refresh occurs in the wee hours of the morning, we can’t send out that alert on Monday.  It has to occur on the Friday before the first Monday. 
That, friends and neighbors, isn’t a built-in option.
It’s a little trickier (though in the end, not terrible) because the Friday before the first Monday could be in this month or in next month.


So what to do?  One good solution is to schedule a job to run every Friday.  It checks to see if today is the Friday before the first Monday, and if it is, it sends an email.
Here’s that the script looks like that accomplishes this:

DECLARE @msgbody VARCHAR(500)      
DECLARE @emaillist VARCHAR(100)       
DECLARE @subj VARCHAR(100)      
DECLARE @FONM DATETIME -- FirstofNextMonth       
DECLARE @dateofrefresh VARCHAR(50)
SET @emaillist = 'NotifcationList@mymail.edu' --Email recipients, semicolon separated       
SET @FONM = DateAdd(Month, 1 + DateDiff(Month, 0, GETDATE()), 0) --First day of the next month       
SET @dateofrefresh = CONVERT(VARCHAR, DateAdd(day, (9 - DatePart(weekday, @FONM)) % 7, @FONM), 101) --First Monday of next month
/* @dateofrefresh is the 1st Monday of the month. 
    The IF statement below checks to see if 3 days from today is the first Monday.       
    If it is, the refresh will happen on Monday, so we send the email.       
*/    
IF CONVERT(VARCHAR, dateadd(day, 3, getdate()), 101) = @DateOfRefresh -- Friday is 3 days from Monday, so if these match, send the email       
BEGIN       
    SET @subj = 'DB refresh scheduled for ' + @dateofrefresh      
    SET @msgbody = 'Your DB is scheduled for refresh on ' + @dateofrefresh + '.'       
    + CHAR(13) + CHAR(10)      
    + 'Please ensure that all of your data has been saved, as it will be overwritten by a copy of production.'
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'email_profile'
         ,@recipients = @emaillist      
        ,@subject = @subj      
        ,@body = @msgbody     
END 

 

Working through the rationale

The first thing we do is find the first day of next month, as we’ll do our following calculation based on that:

SET @FONM = DateAdd(Month, 1 + DateDiff(Month, 0, GETDATE()), 0)

The inner part of this:

DateDiff(Month, 0, GETDATE())

returns the number of months between date zero (1/1/1900) and the first of this month.  The outer DateAdd piece adds one month to that number, which is the first of next month.

Once we’ve got the first day of next month, we need to find the closest Monday to that date:

CONVERT(VARCHAR, DateAdd(day, (9 - DatePart(weekday, @FONM)) % 7, @FONM), 101) 

The inner-most part of this line

DatePart(weekday, @FONM)

returns the integer 1 – 7 corresponding to Sunday – Saturday.

The next piece 

DateAdd(day, (9 - DatePart(weekday, @FONM)) % 7, @FONM)

Does the actual math.  What we’re doing is subtracting the datepart integer from 9, which gives us the number of days between the first of the month and the next Monday.  The %7 forces SQL server to use Sunday as the first day of the week, so we don’t have to worry about systems with a non-default @@datefirst setting.

Once we have the date for the first Monday of next month, we’ll check and see if today is three days from that date.  If it is, then this is the Friday before the first Monday, and we send our email.  If it isn’t, we simply end, doing nothing

IF CONVERT(VARCHAR, dateadd(day, 3, getdate()), 101) = @DateOfRefresh -- Friday is 3 days from Monday, so if these match, send the email    
BEGIN    
    SET @subj = 'DB refresh scheduled for ' + @dateofrefresh     
    SET @msgbody = 'Your DB is scheduled for refresh on ' + @dateofrefresh + '.'    
    + CHAR(13) + CHAR(10)   
    + 'Please ensure that all of your data has been saved, as it will be overwritten by a copy of production.'   

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'email_profile'     
        ,@recipients = @emaillist      
        ,@subject = @subj    
        ,@body = @msgbody