SQL Server Express – Scheduling Jobs via Windows Task Scheduler

Background

Needing a lightweight SQL Server to use as a failover, in case of Production outage,  settled on Sql Express, v2017 no doubt.

 

Problem

SQL Server Express is free, but missing SQL Server Agent, and so no built-in scheduler.

There are some tasks that I need to schedule.

Those tasks are backing up the databases, Index Defrag, and incremental data refresh.

Like Dela potholes soon as I make my way to doorsteps.

 

Files

refreshData.cmd

Outline

  1. Logging
    • Make directory log, if not present
    • If log\refreshdata.log file exists remove it
  2. Issue sqlcmd
    • Pass along the following arguments
      • -b
        • If error occurs in SQL, set errorlevel environment variable
      • -d
        • pass along database name; in our case hrdb
      • -e
        • Pass along payload
        • Call Stored Procedure ( exec dbo.usp_RefreshData )
      • -o
        • Output file is log\refreshData.log
  3. If %errorlevel% equal (-equ ) to zero (0), exit batch file
  4. call emailNotificationFailure.cmd errorlevel

Code


if not exist log mkdir log

if exist log\refreshData.log del log\refreshData.log

ECHO Errorlevel [SQLCMD - PRE] :- %ERRORLEVEL%


sqlcmd -b -S (local) -d hrdb -Q"exec [dbo].[usp_RefreshData]" -o log\refreshData.log

ECHO  [SQLCMD - POST] :- %ERRORLEVEL%

if "%errorlevel%" equ "0" exit /b %errorlevel%

REM Email Notification Failure
call emailNotificationFailure.cmd errorlevel

 

emailNotificationFailure.cmd

Outline

  1. Set _domain name
    • Here we set our email provider domain name
  2. Invoke powershell
    • Pass along -file emailNotificationFailure.ps1

Code

set _domain=labdomain.org
powershell.exe -file emailNotificationFailure.ps1 %_domain%

 

emailNotificationFailure.ps1

Code

Set-StrictMode -Version 1

#Get domain name as first argument
$domain=$args[0]

$self="daniel.adeniji"
$computer=$env:computername

$emailFrom = "$computer@$domain"
$emailTo = "$self@$domain"

$subject = "failed to synch data"

$body = "failed to synch data"

$SMTPServer = "smtp.$domain"

$SMTPServerPort = "25"

$fileAttachment="log\refreshData.log"

"emailFrom :- $emailFrom"
"emailTo :- $emailTo"
"SMTPServer :- $SMTPServer"
"SMTPServerPort :- $SMTPServerPort"

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, $SMTPServerPort)

$objEmailMessage = New-Object System.Net.Mail.MailMessage

$objEmailMessage.From = $emailFrom;
$objEmailMessage.To.Add($emailTo);
$objEmailMessage.Subject = $subject;
$objEmailMessage.Body = $body;

$objAttachment  =$null
if (Test-Path $fileAttachment -PathType Leaf)
{

	"File Attachment ( $fileAttachment ) Added "

	$objAttachment = new-object Net.Mail.Attachment($fileAttachment)

	$objEmailMessage.Attachments.Add($objAttachment)
	
}	
else
{

	"File Attachment ( $fileAttachment ) :- Skipped"

}

$SMTPClient.Send($objEmailMessage);

if ($objAttachment )
{

	$objAttachment.Dispose()
	
}	

 

Task Scheduler – Management

Task Scheduler – Management – Add task

General

Triggers

 

Actions

Actions – Edit Action

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s