Ranga Babu
Querying SQL Server in single user mode using SQL Server management studio

Different ways to start a SQL Server in single user mode

September 17, 2019 by

In this article, we will review different ways to start SQL Server in single user mode.

Many DBA’s might have a situation like restoring a master database or other system databases from the backup that needs SQL Server to be started in single user mode. There are different ways to start SQL Server single user mode. Let us discuss them one by one.

Starting SQL Server single user mode using SQL Server Configuration Manager

Open run by pressing Windows and R keys together. As I am using SQL Server 2016, I typed sqlservermanager13.msc to open the SQL Server Configuration Manager.

open SQL Server configuration manager from run

Please refer to the below list for other SQL Server versions:

  • SQL Server 2012 (11.x) – sqlservermanagr11.msc
  • SQL Server 2014 (12.x) – sqlservermanagr12.msc
  • SQL Server 2016 – sqlservermanager13.msc
  • SQL Server 2017 – sqlservermanager14.msc

You can also open it by clicking on start and search for SQL Server Configuration Manager as shown in the below image and click on SQL Server Configuration Manager (version) to open it.

open SQL Server configuration manager from Start

Once you open configuration manager, click on SQL Server Services which will show SQL Server Services for all the instances along with SQL Server Agent services.

SQL Server single user mode

Select the SQL Server service of the instance that you want to start in single user mode. Right-click on the service and click on Properties as shown in the below image:

SQL Server service properties

Navigate to the Startup Parameters tab. Type -m and click on Add as shown in the below image:

startup parameters in SQL Server service

Click on the Apply button which adds the startup parameter -m to the startup parameters list of that SQL Server instance. Click on the OK button on the warning window.

warning on changing SQL Server service

Right-click on SQL Server service and click on the Restart to restart the SQL Server instance. SQL Server will start in single user mode.

restart SQL Server in single user mode

Now connect to SQL Server using SQL Server Management Studio or SQLCMD. You may receive login failed error as shown in the below image. This is due to the SQL Server Agent service running and consuming only available connection.

login failed error

Make sure you stop the SQL Server Agent service of the SQL erver instance as the SQL Server Agent and try connecting to SQL Server using SQLCMD or SQL Server Management Studio (SSMS).

connecting SQL Server using SQLCMD

It is advisable to use SQLCMD when you want to query SQL Server that is started in single user mode as connecting directly and query using SQL Server Management Studio that uses more than one connection. To query SQL Server single user mode using SQL Server Management Studio, open SQL Server Management Studio, and do not connect to SQL Server directly. Close the connection window and click on New Query as shown in the below image which opens a query editor in SQL Server Management Studio:

Querying SQL Server in single user mode using SQL Server management studio

All the users who are part of the Local Administrator group can connect to SQL Server with privileges of sysadmin server-level role.

To start SQL Server in multi-user mode, remove the added -m start parameter from properties of the SQL Server service and restart the SQL Server service.

Starting SQL Server single user mode using Command Prompt

We can also start SQL Server single user mode using the Command Prompt. Navigate to Start and search for services as shown in the below image. Click on Services which will open Services window.

open services

In the Services window, locate the SQL Server instance service that you want to start in single user mode. Right-click on the service and click on Properties as shown in the below image:

service properties

In the Properties window, you can see the name and display name of the service. Now copy the name of the service which will be used in Command Prompt to start the SQL Server instance in single user mode.

SQL Service name to start SQL Server in single user mode using command prompt

Open run by pressing Windows and R keys together. Type cmd and press enter button that opens the Command Prompt.

Execute the following command to stop the SQL Server service. In this case, MSSQLSERVER is the name of the SQL Server service. Replace it with yours:

NET STOP MSSQLSERVER

Enter Y to continue by stopping the SQL Server Agent service as shown in the below image:

stop SQL Server services using command prompt

Once the services are stopped successfully, start the SQL Server service by passing m parameter. Open the Command Prompt and execute the following command to start SQL Server service in single user mode. Please refer to the below image:

NET START MSSQLSERVER /m

stop SQL Server services using command prompt

Starting SQL Server single user mode using the executable file

Open the Command Prompt and navigate to the folder where sqlservr.exe is located. As I am using the default instance the path is as below:

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn

Run sqlservr.exe with -m as a parameter as shown in the below image:

start SQL Server services using executable file

Conclusion

In this article, we explored how to start SQL Server single user mode using SQL Server Configuration Manager by adding -m in startup parameter and using Command Prompt as well. In case you have any questions, please feel free to ask in the comment section below.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu

Latest posts by Ranga Babu (see all)

1,211 Views