At home, my test environment for SQL Server runs on a Windows 10 PC. It’s the same PC that I use to do a bunch of different things & I don’t always want SQL Server running in the background so I’ve set the start mode for the service to Manual.
However, because shutting down Windows 10 doesn’t do what I would expect it to, I often find that SQL Server is still running when I start up my PC the next time. And even though I know it won’t stop on its own, I keep forgetting to shut it down at the end of the last session. Also, I’m lazy – the several clicks it takes me to open SQL Server Configuration Manager is clearly too much for me. 😅
So, having learned a bit about PowerShell in the past few months, I’ve decided to put it to use so the next time I log on, I’m not wondering why SQL Server is eating up half of my computer’s resources.
Aim: Create a PowerShell script that starts or stops the SQL Server service & execute it from a Desktop shortcut.
After some trial & error, here’s my script saved to a ps1 file. It’s probably not the most elegant, but it’s my first time using PowerShell in anger so it’ll do (& I’ll get better with more practice!). Based on the input parameter $start which can be either 1 or 0, Start-Service or Stop-Service will be run for the default instance of SQL Server.
This is what happens when I run the script directly in PowerShell to shut down SQL Server. The script will ask for a value to be supplied (!? will helpfully return the Help Message I included in the script in case I ever forget what the allowed values are). Once the SQL Server service has stopped, it’s current status will be returned by Get-Service (this is only relevant for executing the script directly in PowerShell).
Note: PowerShell must be run as administrator to execute this script due to it starting/stopping a service.
And when I refresh SQL Server Configuration Manager, I can confirm that the service is indeed stopped.
Same in the opposite direction to start the service. This time around, I’ve passed in the value for the parameter in the same line where I execute the script, rather than waiting for it to prompt me.
Now, I’m no more likely to launch PowerShell as an administrator & execute my script from there than I am to remember to shut down SQL Server from Configuration Manager when I’m done with it, so the next step is to put a Desktop shortcut in place so I can just double-click on it when I’ve closed all the other windows I’m working with.
First, I right-click my ps1 file and create a shortcut, then rename it to something more useful (e.g. “Start or Stop SQL Server”) and move it to the Desktop.
And when I double-click on the shortcut… it just opens the script in Notepad, rather than executing it. Not the desired behaviour!
However, thanks to a SuperUser query from over a decade ago, I’ve found that the way to fix this is to open the Properties of the shortcut & in the Shortcut tab, prefix the path in the Target field with powershell.exe -f.
Then, click Advanced & tick Run As Administrator. Without the addition of powershell.exe -f to the Target field, this option will remain greyed out.
Once those changes are made, double-clicking on the shortcut will open a prompt asking “Do you want to allow this app to make changes to your device?”. Clicking Yes will open the familiar Administrator session in PowerShell & supplying the correct value for the $start parameter will start or stop SQL Server as desired.
Most excellent!
Yes, there may be as many clicks involved as launching SQL Server Configuration Manager & stopping SQL Server from there, but with a big shortcut on my Desktop, I’m less likely to forget to actually stop the service when I’m done with it.
Next: Now that I’ve got a PowerShell script that I can easily run, can I automate it to run on logon so I don’t even need to do the manual steps to stop SQL Server?
Be First to Comment