在单用户模式下启动SQL Server的不同方法

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

在本文中，我们将介绍在单用户模式下启动SQL Server的不同方法。

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.

许多DBA可能会遇到需要从SQL Server以单用户模式启动的备份中还原master数据库或其他系统数据库的情况。 有多种方法可以启动SQL Server单用户模式。 让我们一一讨论。

使用SQL Server配置管理器启动SQL Server单用户模式 (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.

同时按下Windows和R键，即可执行Open run。 在使用SQL Server 2016时，我键入sqlservermanager13.msc打开SQL Server配置管理器。

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

有关其他SQL Server版本，请参考下面的列表：

• SQL Server 2012 (11.x) – sqlservermanagr11.msc
  SQL Server 2012（11.x）– sqlservermanagr11.msc
• SQL Server 2014 (12.x) – sqlservermanagr12.msc
  SQL Server 2014（12.x）– sqlservermanagr12.msc
• SQL Server 2016 – sqlservermanager13.msc
  SQL Server 2016 – sqlservermanager13.msc
• SQL Server 2017 – sqlservermanager14.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.

您也可以通过单击开始打开它并搜索SQL Server Configuration Manager，如下图所示，然后单击SQL Server Configuration Manager（版本）将其打开。

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服务” ，它将显示所有实例SQL Server服务以及SQL Server代理服务。

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服务。 右键单击该服务，然后单击“ 属性” ，如下图所示：

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

导航到“ 启动参数”选项卡。 键入-m并单击添加 ，如下图所示：

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.

单击“ 应用”按钮，它将启动参数-m添加到该SQL Server实例的启动参数列表中。 单击警告窗口上的确定按钮。

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.

右键单击SQL Server服务，然后单击“ 重新启动”以重新启动SQL Server实例。 SQL Server将以单用户模式启动。

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.

现在，使用SQL Server Management Studio或SQLCMD连接到SQL Server。 您可能会收到登录失败错误，如下图所示。 这是由于SQL Server代理服务正在运行并且仅消耗可用的连接。

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).

确保停止SQL erver实例SQL Server代理服务作为SQL Server代理，并尝试使用SQLCMD或SQL Server Management Studio（SSMS）连接到SQL Server。

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:

建议您以单连接方式查询以单用户模式启动SQL Server并使用使用多个连接SQL Server Management Studio查询时，建议使用SQLCMD。 若要使用SQL Server Management Studio查询SQL Server单用户模式，请打开SQL Server Management Studio，并且不要直接连接到SQL Server。 关闭连接窗口，然后单击“ 新建查询” ，如下图所示，该图在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.

属于本地管理员组的所有用户都可以使用sysadmin服务器级角色的特权连接到SQL Server。

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.

若要以多用户模式启动SQL Server，请从SQL Server服务的属性中删除添加的-m start参数，然后重新启动SQL Server服务。

使用命令提示符启动SQL Server单用户模式 (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.

我们还可以使用命令提示符启动SQL Server单用户模式。 导航到“ 开始”并搜索服务，如下图所示。 单击服务，这将打开服务窗口。

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:

在“服务”窗口中，找到要以单用户模式启动SQL Server实例服务。 右键单击该服务，然后单击“ 属性” ，如下图所示：

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 Server实例的服务的名称。

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

同时按下Windows和R键，即可执行Open run。 键入cmd，然后按Enter键以打开命令提示符。

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:

执行以下命令以停止SQL Server服务。 在这种情况下， MSSQLSERVER是SQL Server服务的名称。 用您的替换它：

NET STOP MSSQLSERVER

NET停止MSSQLSERVER

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

输入Y以通过停止SQL Server代理服务继续操作，如下图所示：

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:

服务成功停止后，通过传递m参数来启动SQL Server服务。 打开命令提示符并执行以下命令以单用户模式启动SQL Server服务。 请参考下图：

NET START MSSQLSERVER /m

NET START MSSQLSERVER /米

使用可执行文件启动SQL Server单用户模式 (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:

打开命令提示符，然后导航到sqlservr.exe所在的文件夹。 当我使用默认实例时，路径如下：

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

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

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

使用-m作为参数运行sqlservr.exe，如下图所示：

结论 (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.

在本文中，我们探索了如何通过在启动参数中添加-m以及同时使用命令提示符来使用SQL Server Configuration Manager启动SQL Server单用户模式。 如果您有任何疑问，请随时在下面的评论部分中提问。

翻译自: https://www.sqlshack.com/different-ways-to-start-a-sql-server-single-user-mode/