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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
114 Comments. Leave new
Hello Pinal Dave,
I am trying to start/stop a windows service from SQL command. I tried these lines
EXEC xp_cmdshell ‘net start “Listen Service”‘
— To allow advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 1
GO
— To update the currently configured value for advanced options.
RECONFIGURE
GO
— To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
— To update the currently configured value for this feature.
RECONFIGURE
GO
I am getting an access denied error. So I enabled the proxy login in the Security options.
But I am still getting the error.
Can you please help me?.
Thank you,
Uday
Whatever you do with xp_cmdshell would run with service account. Have you looked into https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-xp-cmdshell-proxy-account-transact-sql?view=sql-server-2017
I have used a similar method for a few years that could be useful.
I created 3 batch files (START.bat, STOP.bat, RESTART.bat) that I save to C: and set them to hidden. I then create a shortcuts to each on my desktop. Set the shortcuts to be run in Admin mode. That way, all I have to do is click on the icon for whatever I want to do and it’s all automated.
Here is the contents of the RESTART.bat file that I use for SQL Server 2014. You can modify it for whichever version you are using. This script is just the STOP and START scripts combined in one batch:
@ECHO OFF
ECHO Restarting Services
ECHO.
ECHO.
ECHO Stopping Services…
ECHO.
ECHO.
NET STOP “SQLWriter”
NET STOP “SQL Server Agent (MSSQLSERVER)”
NET STOP “SQL Server Browser”
NET STOP “SQL Server Reporting Services (MSSQLSERVER)”
NET STOP “SQL Server Analysis Services (MSSQLSERVER)”
NET STOP “SQL Server Integration Services 12.0”
NET STOP “SQL Full-text Filter Daemon Launcher (MSSQLSERVER)”
NET STOP “SQL Server (MSSQLSERVER)”
ECHO.
ECHO.
ECHO Services Stopped
ECHO.
ECHO.
ECHO Starting Services…
ECHO.
ECHO.
NET START “SQL Server (MSSQLSERVER)”
NET START “SQL Server Integration Services 12.0”
NET START “SQL Server Analysis Services (MSSQLSERVER)”
NET START “SQL Server Reporting Services (MSSQLSERVER)”
NET START “SQL Server Browser”
NET START “SQL Server Agent (MSSQLSERVER)”
NET START “SQLWriter”
ECHO.
ECHO.
ECHO Services Started
ECHO.
ECHO.
PAUSE
Hope this helps.
I have used a similar method for a few years that could be useful.
I created 3 batch files (START.bat, STOP.bat, RESTART.bat) that I save to C:\ and set them to hidden. I then create a shortcuts to each on my desktop. Set the shortcuts to be run in Admin mode. That way, all I have to do is click on the icon for whatever I want to do and it’s all automated.
Here is the contents of the RESTART.bat file that I use for SQL Server 2014. You can modify it for whichever version you are using. This script is just the STOP and START scripts combined in one batch:
@ECHO OFF
ECHO Restarting Services
ECHO.
ECHO.
ECHO Stopping Services…
ECHO.
ECHO.
NET STOP “SQLWriter”
NET STOP “SQL Server Agent (MSSQLSERVER)”
NET STOP “SQL Server Browser”
NET STOP “SQL Server Reporting Services (MSSQLSERVER)”
NET STOP “SQL Server Analysis Services (MSSQLSERVER)”
NET STOP “SQL Server Integration Services 12.0″
NET STOP “SQL Full-text Filter Daemon Launcher (MSSQLSERVER)”
NET STOP “SQL Server (MSSQLSERVER)”
ECHO.
ECHO.
ECHO Services Stopped
ECHO.
ECHO.
ECHO Starting Services…
ECHO.
ECHO.
NET START “SQL Server (MSSQLSERVER)”
NET START “SQL Server Integration Services 12.0″
NET START “SQL Server Analysis Services (MSSQLSERVER)”
NET START “SQL Server Reporting Services (MSSQLSERVER)”
NET START “SQL Server Browser”
NET START “SQL Server Agent (MSSQLSERVER)”
NET START “SQLWriter”
ECHO.
ECHO.
ECHO Services Started
ECHO.
ECHO.
PAUSE
Hope this helps.
sql express service automatically stop again and again in win 7,8 on my client computer. what i do please tell me.
Hi Sir,
Is there any way to start-Stop windows service using sql script.
I tried this , but am getting “Access is denied. System error 5 has occurred.” error.
EXEC master.dbo.sp_configure ‘show advanced options’, 1 RECONFIGURE WITH OVERRIDE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1 RECONFIGURE WITH OVERRIDE
exec xp_cmdshell ‘net start SQLAgent$SQLEXPRESS ‘
Use Master
EXEC master.dbo.sp_configure ‘show advanced options’, 1 RECONFIGURE WITH OVERRIDE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 0 RECONFIGURE WITH OVERRIDE
Hi Pinal,
How can I detect if the agent’s service is running and otherwise send an alert message.
Thanks.
Buen día, si agluien me puede ayudar.
Ejecuto el comando
net start /W “SQL Server Reporting Services (MSSQLSERVER)”
Pero en mi servidor no termina de ejecutarse y envía esto.
C:\Windows\system32>net start /W “SQL Server Reporting Services (MSSQLSERVER)”
The SQL Server Reporting Services (MSSQLSERVER) service is starting……..
The SQL Server Reporting Services (MSSQLSERVER) service could not be started.
The service did not report an error.
More help is available by typing NET HELPMSG 3534.
Aun así si vuelvo a ejecutar el start indica que ya esta en ejecución,
C:\Windows\system32>net start /W “SQL Server Reporting Services (MSSQLSERVER)”
The requested service has already been started.
More help is available by typing NET HELPMSG 2182.
Alguien me puede apoyar o sabe como resolver este detalle.
Muchas gracias.
Hi Pinal Dave. I want the below scenario from Command Line:
Question: How to get a directory path by giving the string of the folder name?
Can you please answer the above question.