在SQL Server的数据库维护过程中，有时候在一些特殊情况下需要在单用户模式(single-user mode)下启动SQL Server实例。下面总结一下单用户模式启动SQL Server的几种方式：
1：命令模式(sqlservr.exe)启动
首先在命令窗口中切换到SQL Server的Binn目录下(这个要视SQL Server实际安装路径情况而定，另外，在SQL Server多实例情况下，必须切换到对应路径)，如果你对sqlservr.exe命令不熟悉，可以查看相关帮助信息。如下所示：
- C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr.exe /?
- usage: sqlservr
- [-a<L2 buffer pool directory>,<size in GB>] (adding an L2 buffer pool file)
- [-c] (not as a service)
- [-d file] (alternative master data file)
- [-l file] (alternative master log file)
- [-e file] (alternate errorlog file)
- [-f] (minimal configuration mode)
- [-m] (single user admin mode)
- [-g number] (stack MB to reserve)
- [-k <decimal number>] (checkpoint speed in MB/sec)
- [-n] (do not use event logging)
- [-s name] (alternate registry key name)
- [-T <number>] (trace flag turned on at startup)
- [-x] (no statistics tracking)
- [-y number] (stack dump on this error)
- [-B] (breakpoint on error (used with -y))
- [-K] (force regeneration of service master key (if exists))
- [-v] (list version information)
- See documentation for details.
- 2018-04-06 11:28:00.52 SQL Server shutdown has been initiated
sqlservr.ex启动时，当前环境存在多实例的情况下，而你又没有指定参数-s的值，那么就会收到类似如下信息， 需要你指定-s参数。
- C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr.exe -c -m
- 2018-04-06 11:40:54.15 Server Multiple instances of SQL server are installe
- d on this computer. Renter the command, specifying the -s parameter with the nam
- e of the instance that you want to start.
- 2018-04-06 11:40:54.16 Server SQL Server shutdown has been initiated
- sqlservr.exe -c -m -s{instancename}
sqlservr.ex启动时，如果SQL Server服务本身还在运行，就会报“Operating system error = 32(The process cannot access the file because it is being used by another process.).
- C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr.exe -c -m -sMSSQLSERVER
- 2018-04-06 11:41:59.01 Server Error: 17058, Severity: 16, State: 1.
- 2018-04-06 11:41:59.01 Server initerrlog: Could not open error log file 'C:
- \Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Ope
- rating system error = 32(The process cannot access the file because it is being
- used by another process.).
- 2018-04-06 11:41:59.32 Server Error: 17058, Severity: 16, State: 1.
- 2018-04-06 11:41:59.32 Server initerrlog: Could not open error log file 'C:
- \Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Ope
- rating system error = 32(The process cannot access the file because it is being
- used by another process.).
- 2018-04-06 11:42:02.04 Server SQL Server shutdown has been initiated
如果在sqlservr.exe当中退出单用户模式，直接使用CTRL+C 或 CTRL + Break，如下所示：
2：命令模式(net star)启动
- C:\Users>net stop mssqlserver
- The following services are dependent on the SQL Server (MSSQLSERVER) service.
- Stopping the SQL Server (MSSQLSERVER) service will also stop these services.
- SQL Server Agent (MSSQLSERVER)
- Do you want to continue this operation? (Y/N) [N]: y
- The SQL Server Agent (MSSQLSERVER) service is stopping.
- The SQL Server Agent (MSSQLSERVER) service was stopped successfully.
- The SQL Server (MSSQLSERVER) service is stopping.
- The SQL Server (MSSQLSERVER) service was stopped successfully.
- C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>net start mssqlserver /m
- The SQL Server (MSSQLSERVER) service is starting.
- The SQL Server (MSSQLSERVER) service was started successfully.
3：SQL Server配置管理器启动
在SQL Server配置管理器中，找到对应实例，右键单击属性，在启动参数里面增加参数-m，然后重启即可。
在单用户模式下启动SQL Server实例时，请注意下列事项：
- 只有一个用户可以连接到服务器。
- 不执行CHECKPOINT 进程。默认情况下，启动时自动执行此进程。
在单用户模式下启动SQL Server 可使用计算机本地 Administrators 组的任何成员作为 sysadmin 固定服务器角色的成员连接到 SQL Server 实例。有关详细信息，请参阅在系统管理员被锁定时连接到 SQL Server。
在单用户模式下， 只有一个用户可以连接到服务器，那么这样问题就来了，很有可能当你需要登录的时候，这个唯一的的用户已经被其它用户捷足先登了。此时你却被拒之门外，是否相当抓狂。此时你可能遇到下面错误
- C:\Users>sqlcmd
- Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user '
- xxxx'. Reason: Server is in single user mode. Only one administrator c
- an connect at this time..
SSMS客户端一般遇到下面这样的错误信息：
- Login failed for user 'xxxx'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
错误日志或命令里面输出的日志，你会看到类似如下信息：
- 2018-04-06 12:21:14.85 Logon Error: 18461, Severity: 14, State: 1.
- 2018-04-06 12:21:14.85 Logon Login failed for user 'xxx'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: 192.168.xxx.xxx]
在这种情况下，怎么办呢?难道要拼速度?当然不是，你需要从下面这些方面注意：
在单用户模式下连接到SQL Server实例之前，停止SQL Server Agent 服务;否则 SQL Server Agent 服务将使用该连接，从而使其阻塞。
在单用户模式下启动SQL Server实例时，SQL Server Management Studio 可以连接到 SQL Server。但是Management Studio中的对象资源管理器可能会失败，因为在某些操作中它需要使用多个连接。若要在单用户模式下管理 SQL Server，可以执行 Transact-SQL 语句(仅通过 Management Studio 中的查询编辑器连接)或者使用 sqlcmd 实用工具。
当您将 -m 选项与 sqlcmd 或 Management Studio 结合使用时，可以将连接限制为指定的客户端应用程序。例如，-m"sqlcmd" 将连接限制为单个连接并且该连接必须将自身标识为 sqlcmd 客户端程序。当您正在单用户模式下启动 SQL Server 并且未知的客户端应用程序正在占用这个唯一的可用连接时，使用此选项。若要通过 Management Studio 中的查询编辑器进行连接，请使用 -m"Microsoft SQL Server Management Studio - Query"。
如下所示，如果你指定了单用户只能以SQLCMD连接，那么此时，其它通过SSMS等其它方式连接数据库都会报上面错误，其它通过程序连接过来的连接就不会抢占这个连接了。
- C:\Windows\system32>net stop mssqlserver
- The SQL Server (MSSQLSERVER) service is stopping.
- The SQL Server (MSSQLSERVER) service was stopped successfully.
- C:\Windows\system32>net start mssqlserver /m"SQLCMD"
- The SQL Server (MSSQLSERVER) service is starting.
- The SQL Server (MSSQLSERVER) service was started successfully.
- C:\Windows\system32>
如果你指定参数/m"Microsoft SQL Server Management Studio - Query" 那么就会阻止像应用程序或SQLCMD登录
- C:\Windows\system32>net stop mssqlserver
- The SQL Server (MSSQLSERVER) service is stopping.
- The SQL Server (MSSQLSERVER) service was stopped successfully.
- C:\Windows\system32>net start mssqlserver /m"Microsoft SQL Server Management Studio - Query"
- The SQL Server (MSSQLSERVER) service is starting.
- The SQL Server (MSSQLSERVER) service was started successfully.
- C:\Users>sqlcmd
- Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user '
- xxx'. Reason: Server is in single user mode. Only one administrator c
- an connect at this time..
原文链接：https://mp.weixin.qq.com/s/zs6WOVGjw_Xe7pV3mHMfOg
