Skip to content

Latest commit

 

History

History
101 lines (61 loc) · 7.59 KB

start-sql-server-in-single-user-mode.md

File metadata and controls

101 lines (61 loc) · 7.59 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Start SQL Server in single-user mode
Learn about single-user mode in SQL Server. See when it is useful and how to use the startup option -m to start an instance of SQL Server in this mode.
rwestMSFT
randolphwest
03/27/2024
sql
configuration
how-to
starting SQL Server, single-user mode
single-user mode [SQL Server]

Single-user mode for SQL Server

[!INCLUDE SQL Server]

This article provides information and steps to start your [!INCLUDEssNoVersion] instance in single-user mode, which allows only one user to connect to the instance.

Start instance in single-user mode

Starting [!INCLUDEssNoVersion] in single-user mode enables any member of the computer's local Administrators group to connect to the instance of [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] in single-user mode.

The following example starts the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] Agent service before connecting to an instance of [!INCLUDEssNoVersion] in single-user mode; otherwise, the [!INCLUDEssNoVersion] Agent service uses the connection, thereby blocking it.

When you start an instance of [!INCLUDEssNoVersion] in single-user mode, [!INCLUDEssManStudioFull] can connect to [!INCLUDEssNoVersion]. Object Explorer in [!INCLUDEssManStudio] might fail because it requires more than one connection for some operations. To manage [!INCLUDEssNoVersion] in single-user mode, execute [!INCLUDEtsql] statements by connecting through the Query Editor in [!INCLUDEssManStudio] or Azure Data Studio, or use the sqlcmd utility.

When you use the -m option with SQLCMD or [!INCLUDEssManStudio], 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 [!INCLUDEssNoVersion] in single-user mode and an unknown client application is taking the only available connection. To connect through the Query Editor in [!INCLUDEssManStudio], 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] is started in single-user mode. If you need to troubleshoot issues with a database that requires starting [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] in single-user mode so the database comes online.

Failover cluster instances

For [!INCLUDEssNoVersion] installation in a clustered environment, when [!INCLUDEssNoVersion] is started in single user mode, the cluster resource dll uses up the available connection thereby blocking any other connections to the server. When [!INCLUDEssNoVersion] is in this state, if you try to bring [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] Advanced Properties.

  2. Take the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] resource is still offline.

  5. Connect to the [!INCLUDEssNoVersion] 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