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.
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.
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.
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:
Navigate to the Startup Parameters tab. Type -m and click on Add as shown in the below image:
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.
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.
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.
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).
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:
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.
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:
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.
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:
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
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:
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.
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019