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單用戶模式。 讓我們一一討論。
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版本，請參考下面的列表：
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服務。
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 /米
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，如下圖所示：
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/
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
