Skip to content

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

Latest commit

 

History

History
99 lines (64 loc) · 7.9 KB

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

File metadata and controls

99 lines (64 loc) · 7.9 KB
title Start SQL Server in Single-User Mode
description 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.
author rwestMSFT
ms.author randolphwest
ms.date 07/15/2025
ms.service sql
ms.subservice configuration
ms.topic how-to
helpviewer_keywords
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 [!INCLUDE ssNoVersion] instance in single-user mode, which allows only one user connection to the instance.

Start instance in single-user mode

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

The following example starts the [!INCLUDE ssNoVersion] instance in single-user mode via command line 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 the master database in [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] in single-user mode, note that:

  • Only one user can connect to the server.

  • The CHECKPOINT process isn't executed. By default, it's executed automatically at startup.

Note

Stop the [!INCLUDE ssNoVersion] Agent service before connecting to an instance of [!INCLUDE ssNoVersion] in single-user mode; otherwise, the [!INCLUDE ssNoVersion] Agent service uses the connection, thereby blocking it.

When you start an instance of [!INCLUDE ssNoVersion] in single-user mode, [!INCLUDE ssManStudioFull] can connect to [!INCLUDE ssNoVersion]. Connecting Object Explorer in [!INCLUDE ssManStudio] might fail because it requires more than one connection for some operations. To manage [!INCLUDE ssNoVersion] in single-user mode, execute [!INCLUDE tsql] statements by connecting through the Query Editor in [!INCLUDE ssManStudio], or use the sqlcmd utility.

When you use the -m option, you can append a specific application name to restrict connections to only those with the same application name specified in the connection string. For example, the sqlcmd utility uses SQLCMD as the application name in its connection string. If you specify -mSQLCMD as a startup parameter, [!INCLUDE ssNoVersion] instance is started in single-user mode and connections from applications other than sqlcmd are rejected. Use this option when you're starting [!INCLUDE ssNoVersion] in single-user mode and an unknown client application is taking the only available connection.

To connect through the Query Editor in [!INCLUDE ssManStudio], you can use -mSSMSQueryEditor and enter App=SSMSQueryEditor on the Additional Connection Parameters tab in the Connect to Database Engine dialog.

Note

The application name specified with the -m startup option might be case-sensitive.

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 [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] is started in single-user mode. If you need to troubleshoot issues with a database that requires starting [!INCLUDE ssNoVersion] 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 [!INCLUDE ssNoVersion] in single-user mode so the database comes online.

Failover cluster instances

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

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

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