Muokkaa

Jaa

Single-user mode for SQL Server

  • Artikkeli

Applies to: SQL Server

This article provides information and steps to start your SQL Server instance in single-user mode, which allows only one user to connect to the instance.

Start instance 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 Connect to SQL Server when system administrators are locked out.

Under certain circumstances, you might have to start an instance of SQL Server in single-user mode by using the startup option -m. For example, you might 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.

The following example starts the SQL Server instance in single-user mode and only allows connection through the SQL Server Management Studio Query Editor.

net start "SQL Server (MSSQLSERVER)" /m"Microsoft SQL Server Management Studio - Query"

To restore a master database on Linux in single-user mode, see Restore the master database on Linux in single-user mode.

General considerations

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 isn't 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 through the Query Editor in Management Studio or Azure Data 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.

Note

On Linux, SQLCMD must be capitalized as shown.

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're 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

Don't 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.

Always On considerations

There are additional considerations when using single-server mode for SQL Server instances that are configured as an Always On failover cluster instance (FCI), or your databases are part of an Always On availability group (AG).

Availability groups

Startup of the Always On availability group and databases in the group is skipped when SQL Server is started in single-user mode. If you need to troubleshoot issues with a database that requires starting SQL Server in single-user mode, and the database is also part of an availability group, you must remove the database from the availability group before starting SQL Server in single-user mode so the database comes online.

Failover cluster instances

For SQL Server installation in a clustered environment, when SQL Server is started in single user mode, the cluster resource dll uses up the available connection thereby blocking any other connections to the server. When SQL Server is in this state, if you try to bring SQL Server Agent resource online, it might fail over the SQL resource to a different node if the resource is configured to affect the group.

To get around the problem use the following procedure:

  1. Remove the -m startup parameter from the SQL Server Advanced Properties.

  2. Take the SQL Server resource offline.

  3. From the current owner node of this group, issue the following command from the command prompt:

    net start MSSQLSERVER /m

  4. Verify from the cluster administrator or failover cluster management console that the SQL Server resource is still offline.

  5. Connect to the SQL Server now using the following command and do the necessary operation: SQLCMD -E -S<servername>.

  6. Once the operation is complete, close the command prompt and bring back the SQL and other resources online through cluster administrator.

Related content