Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

SQL Services on Laptop

I run several SQL Server instances on my laptop, however I’ll keep the services shutdown to conserve resources and then start up the instance when needed. Doing this type of stuff through the GUI results in several clicks

  1. Right click on SQL instance in SSMS
  2. Select Service Control >> Start
  3. Then you’re of course prompted for UAC
  4. A second prompt asking if you really want to Start SQL Server

This is kind of a pain and much easier to do through a simple PowerShell script called Start-Sql

Start-Process powershell.exe -argumentlist '-noprofile -command get-service -Name "MSSQL`$R2" | ? {$_.Status -eq "Stopped"} | Start-Service' -verb runas

The script starts a SQL Server service named “MSSQL$R2” using the the cmdlets Get-Service and Start-Service. A little trick of using start-process with the –verb runas command kicks of a powershell host as administrator. To stop service the service I’ll use a stop-sql script:

Start-Process powershell.exe -argumentlist '-noprofile -command get-service -Name "MSSQL`$R2" | ? {$_.Status -eq "Running"} | Stop-Service -force' -verb runas

Note: I’ve hardcoded the instance service name so I don’t have to specify a parameter each time. You’ll need to change “MSSQL$R2” to your instance.

Comments

Posted by Jason Brimhall on 19 November 2010

I do a similar thing - but not with powershell.  I could use this too though.

Posted by cmille19 on 21 November 2010

I may not have SQL Server running locally all the time, but do have a PowerShell command window open most of the day, so for me it made sense to use a PowerShell script.

Leave a Comment

Please register or log in to leave a comment.