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
I learn some new stuff from it too, thanks for sharing your information. book me today
ReplyDeletei was just browsing along and came upon your blog. just wanted to say good blog and this article really helped me. bookmetoday.com
ReplyDeleteGrand Casino Resort, Reno, NV - Mapyro
ReplyDeleteGrand Casino 성남 출장안마 Resort is located in 안동 출장샵 Reno, NV. The casino is open 24 hours and offers live entertainment for 과천 출장안마 guests in NV. The casino is 제주도 출장마사지 located in the 성남 출장샵 centre