Script to Start the SQL Server Services

Question

Script to Start the SQL Server Services

rambilla4

SSCrazy Eights

Points: 8715
More actions
December 6, 2009 at 4:40 pm

#228394

Hi,
We have 4 SQL Server 2005 instances on single server. For maintenance purposes, we frequently need to Stop and Start the all the SQL Services ( SQL Server Service, SQL Agent Services, Full text Service, Integration Service, Reporting Services). It's taking 20 mins to stop all the Services manually from SQL Server configuration manager.
I went through the link http://msdn.microsoft.com/en-us/library/ms180965.aspx and it's saying that DO NOT use net commands to start and stop SQL Server services from command prompt UNLESS for troubleshooting.
So is there any single t-sql script or VB script, that will stop and start all the services?
Thank You

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

The article referenced is speaking of starting SQLSERVER.EXE from the command prompt, not stopping and starting services using net commands.

In the article, it discusses stopping the services using net commands in the context of if you started SQL server from the command prompt.

Using net start and net stop to stop and start SQL services can be done (that is a service start command from the command line that will function as if you clicked start from services.msc).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 2

thank you,

Could you please advice me what is the best to way to start and stop the SQL services instead of starting one-by-one from SQL Server configuration manager manually.

I want to Stop and start SQL services using single script. Will that possible or do I need to stop/start manually from SQL Server configuration manager?

thanks

Ram

Xxx Right there with Babe Points: 738 More actions · Answer 3

Hello Ram,

net start / net stop commands shouldn't be an issue. Here's a good article on doing so: http://vyaskn.tripod.com/restart_sql_server_service.htm

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

You said you need to do this for maintenance purposes... what are they and why do you think you must stop the SQL Server Services to accomplish them?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CANCER Ten Centuries Points: 1026 More actions · Answer 5

Hi ..

You can use the following vb script to start the sql service automatically..

*********************************************************************

VBS Script: By Service Name

strComputer = InputBox ("Enter Machine Name")

objServiceName = "Service_Name"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CimV2")

Set colListOfServices = objWMIService.ExecQuery ("Select * From Win32_Service Where Name ='" & objServiceName & "'")

For Each objService in colListOfServices

objService.ChangeStartMode("Automatic")

Wscript.Sleep 5000

errReturnCode = objService.StartService()

Example: objServiceName = " TlntSvr"

VBS Script: By Service Display Name

strComputer = InputBox ("Enter Machine Name")

objServiceDisplayName = "Service_Display_Name"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CimV2")

Set colListOfServices = objWMIService.ExecQuery ("Select * From Win32_Service Where DisplayName ='" & objServiceDisplayName & "'")

For Each objService in colListOfServices

objService.ChangeStartMode("Automatic")

Wscript.Sleep 5000

errReturnCode = objService.StartService()

Example: objServiceDisplayName = "Telnet"

Note: You can also change the Start Mode to “Disabled” Or “Manual” as well as Stop the service using StopService()

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 6

You said you need to do this for maintenance purposes... what are they and why do you think you must stop the SQL Server Services to accomplish them?

We stop the SQL Services when:

1. Network team do some maintenance on Network

2. SAN upgrade and other kind of SAN maintenance

When network team do the maintenance, the cluster resource try to fail over backup & forth between the nodes. For safer side, we stop the services before maintenance and start the services after the maintenance.

I'm not sure in which cases, we need to stop the services. It would be really helps me if you provide some scenarios where we need to stop sql services and where we DO NOT need to stop the services.

Many thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

There is an integral piece of information. Knowing that the server is clustered is essential information for the answer.

Using net stop net start for the services will cause the server to fail over. If they are doing SAN maintenance, you probably want the services stopped the entire duration of maintenance. In this case you should stop the services through cluster administration.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 8

Thank You,

There is an integral piece of information. Knowing that the server is clustered is essential information for the answer.
Using net stop net start for the services will cause the server to fail over. If they are doing SAN maintenance, you probably want the services stopped the entire duration of maintenance. In this case you should stop the services through cluster administration.

If the server is standalone then how to stop & Start SQL Services using a script?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

rambilla4 (12/7/2009)
Thank You,
There is an integral piece of information. Knowing that the server is clustered is essential information for the answer.
Using net stop net start for the services will cause the server to fail over. If they are doing SAN maintenance, you probably want the services stopped the entire duration of maintenance. In this case you should stop the services through cluster administration.
If the server is standalone then how to stop & Start SQL Services using a script?

Standalone - see the previous posts in this thread.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Arjun SreeVastsva SSCertifiable Points: 7141 More actions · Answer 10

SQL SERVER – 2005 – Start Stop Restart SQL Server From Command Prompt

Very frequently I use following command prompt script to start and stop default instance of SQL Server. Our network admin loves this commands as this is very easy.

Click Start >> Run >> type cmd to start command prompt.

Start default instance of SQL Server

net start mssqlserver

Stop default instance of SQL Server

net stop mssqlserver

Start and Stop default instance of SQL Server.

You can create batch file to execute both the commands together.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 11

Once again, Books online covers all of this:

SQL Server 2005 Books Online (November 2008) topic "Stopping Services"

http://msdn.microsoft.com/en-us/library/ms189095%28SQL.90%29.aspx

another ref:

http://blog.sqlauthority.com/2007/09/09/sql-server-2005-start-stop-restart-sql-server-from-command-prompt/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 12

With a cluster, I am with Jason on this one. You should be using the Cluster Administrator to handle the stop and start. How often do these maintenance periods occur? If it is frequently, you have bigger problems than stopping the SQL Services.