单用户模式启动SQL Server实例总结

同类参考文章：http://www.mssqlmct.cn/dba/?post=124&tdsourcetag=s_pctim_aiomsg

作者：潇湘隐者 出处：http://www.cnblogs.com/kerrycode/

 

在SQL Server的数据库维护过程中，有时候在一些特殊情况下需要在单用户模式下启动SQL Server实例。 下面总结一下单用户模式启动SQL Server的几种方式：

 

1：命令模式(sqlservr.exe)启动

 

首先在命令窗口中切换到Binn目录（这个要视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参数的SQL Server服务名称。

 

 

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr.ex

e -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.ex

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

 

 

作者：潇湘隐者 出处：http://www.cnblogs.com/kerrycode/