Using PowerShell to start or stop SQL Server (Part 2)

Following on from my last post, I had decided that automating the step to stop SQL Server would be a better option than relying on me remembering to do it manually when I had finished with the service. Especially since I couldn’t rely on Windows 10 to stop the service when I shut down my PC.

Aim: To create a scheduled task that will automatically stop the SQL Server service on logon.

First, I open Task Scheduler & click Create Tasks under Actions on the right-hand side. I give the task a name, description & tick “Run with highest privileges” (because this PowerShell script needs to be run as an administrator).

I create a Trigger to begin the task “at log on” for my user.

I create an Action to start a program & browse to my ps1 script. In the arguments field, I add 0 for the $start parameter so that it will stop the SQL Server service.

I make no changes to the Conditions & in Settings, I update the time at which to stop the task to the minimum allowed value (1 hour).

And when I test the task by running it from Actions in the Task Scheduler… it just opens the script in Notepad, rather than executing it. Like in the previous post when I tried to run my PowerShell script from the shortcut initially, this is not the desired behaviour! It seems that the “Run with highest privileges” setting doesn’t do what I though it would. However, the issue I’ve hit here is the same that I had hit with the shortcut so maybe the same solution will work here.

I open the task’s Properties & edit the Action to prefix the path in the Program/script field with powershell.exe -f.

This causes a warning to pop up stating that arguments have been included in the Program text box & asking if I want to run powershell.exe with the arguments -f C:\PSAdminScripts\StartStopSQL.ps1 0, which I do so I click Yes.

Now, when I test the task by running it, an empty Administrator session in PowerShell opens. Once that has closed, I check SQL Server Configuration Manager & the SQL Server service is stopped.

And when I shut down & restart my PC, the SQL Server service is stopped on logon, & I can see from the Task Scheduler that the task ran when I logged on.