Yesterday we looked at scheduling PowerShell scripts with SQL Sever Agent, but what do you do if you’re running SQL Server Express and don’t have that luxury?
Well, it turns out that it’s pretty easy to schedule PowerShell taks with the Windows Task Scheduler.
Images in this post are from Task Scheduler on Windows 2012, but they are pretty much identical back to Windows 2008.
Task Scheduler offers a nice “Create Basic Task Wizard”, which quickly runs you through the pertinent options from the full task creation process,b ut unfortunately this doesn’t let you set a more complicated schedule than once a day:
To do that you need to create a full scheduled task. Stepping through process isn’t too complicated, but just a few gotchas to watch out for.
The first pane you’ll come to is the general properties one. Here you can provide a title for you task, and change the user under who’s security context the job will run. You’ll need to tick the box “Run whether user is logged on or not” otherwise it won’t run when the user is logged off. If you run it as any user other than the one creating the task, then any time you modify the task you’ll be prompted to provide the users credentials:
This is to prevent someone modifying a job to abuse any elevated privileges it runs under.
The next step along it ‘Triggers’:
here we can build up the conditions under which our scheduled task will fire. As shown in the above screenshot you can set multiple triggers for the same task. For instance, you might want to run transaction log backups every 15 minutes durig 9-5 Production hours, but only every 2 hours outside of that window.
You build or Edit a trigger with the following screen:
You can set pretty much any schedule you want here. Don’t feel constrained by the values in the drop down boxes, you can type your own values if you want (for instance every 7 minutes).
Once we have our triggers we can define the actions that we want to take when they trigger:
Just like with Trigger you can have multiple Actions that will all fire on a trigger:
Note that the Action we take is to start PowerShell, not to run out script. This is because .ps1 PowerShell scripts are not themselves executable, but need to be passed to PowerShell to run. So PowerShell is our program, here I’m relying on the system %PATH% variable to find it, but if you want to ensure the correct exe is started you can provide the full path to the exe. So as PowerShell is fired up we pass our script in as an Argument. As long as all file references in your scripts are fully qualified you probably don’t need to populate the “Start In” box, but if you are loading custom modules or other sub scripts then it may be easier to start the script in a specific location.
Now as this is a series on PowerShell it’d a be a bit remiss not to show you how to create a new Scheduled task using PowerShell itself. The bad news is, that this is only implemented in Windows 2012 and won’t be back ported:
$action = New-ScheduledTaskAction -Execute PowerShell.exe -Argument "C:\scripts\backup_all_dbs.ps1" $trigger = New-ScheduledTaskTrigger -once -at 00:01 -RepetitionInterval (New-TimeSpan -Minutes 30) -RepetitionDuration (new-timespan -Days 1) Register-ScheduledTask -TaskName "SQL Backup" -TaskPath "\" -Action $action -Trigger $trigger -user "Domain\User" -Password "p@55w0rd"
In versions prior you have to use schtasks. schtasks has a large and flexible syntax which is beyond the scope of this article, but is well documented here with plenty of examples. For completeness here is the schtasks command line for the above PowerShell:
schtasks /create /sc minute /mo 30 /tn "SQL Backup" /tr "powershell.exe -c c:\scripts\SQL_backup.ps1" /ru Domain\User
When you run this, schtask will prompt you for the users password.
Tomorrow we’ll begin to look at using PowerShell to restore SQL Server databases
This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here
Leave a Reply