Bagikan melalui

  • Artikel

Starting SQL Server in Single-User Mode

Under certain circumstances, you may have to start an instance of SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.

Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role. For more information, see Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out.

When you start an instance of SQL Server in single-user mode, note the following:

  • Only one user can connect to the server.

  • The CHECKPOINT process is not executed. By default, it is executed automatically at startup.

Note

Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.

When you start an instance of SQL Server in single-user mode, SQL Server Management Studio can connect to SQL Server. Object Explorer in Management Studio might fail because it requires more than one connection for some operations. To manage SQL Server in single-user mode, execute Transact-SQL statements by connecting only through the Query Editor in Management Studio, or use the sqlcmd utility.

When you use the -m option with sqlcmd or Management Studio, you can limit the connections to a specified client application. For example, -m"sqlcmd" limits connections to a single connection and that connection must identify itself as the sqlcmd client program. Use this option when you are starting SQL Server in single-user mode and an unknown client application is taking the only available connection. To connect through the Query Editor in Management Studio, use -m"Microsoft SQL Server Management Studio - Query".

Important

Do not use this option as a security feature. The client application provides the client-application name, and can provide a false name as part of the connection string.

Change History

Updated content

Added the description about using the -m option to limit connections to a client application.