Start SQL Server in single user mode command prompt is a must know task for any SQL server DBA. This can be either done via GUI by setting the startup parameters for an instance in SQL Server Configuration Manager or through the command prompt. I found it easy and fast through command prompt. This is how it can be done.
This is either done using the net start/stop command or by sqlserver.exe command. Let’s first check how it is done via net start/stop command
The syntax for a net start/stop command is
NET START <SERVICE_NAME> <Parameters>
The service name can be obtained as shown below.
- Type services.msc in “RUN” window and click OK. This will open the service dialog box.
- Navigate to the SQL Server instance you wish to get the service name for and double click it. This will open the properties dialog box for that instance. Copy the service name listed there.
The next step is to first stop the SQL Server service. Stopping SQL Server service will also stop the SQL Server Agent service. Open up a command prompt with administrative privileges and stop the SQL Server service as shown below.
The next step is to start the service in single user mode. This is done by specifying /m parameter with NET START command.
To start the SQL Instance in multi user mode, stop the service using net stop as shown above and then start it without specifying \m parameter as shown below.
One thing to note here is that starting SQL Server Instance doesn’t start the SQL Server Agent service. In order to start the SQL Server Agent, get the service name using the method shared above and start it as shown in above image.
The other way to do this is via sqlserve.exe. Please refer to this http://technet.microsoft.com/en-us/library/ms180965(v=sql.105).aspx for this method.
Happy Learning!!!
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
