Skip to content

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

Latest commit

 

History

History
81 lines (54 loc) · 6.47 KB

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

File metadata and controls

81 lines (54 loc) · 6.47 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/20/2022
ms.service sql
ms.subservice configuration
ms.topic conceptual
helpviewer_keywords
starting SQL Server, single-user mode
single-user mode [SQL Server]

Start SQL Server in single-user mode

[!INCLUDE SQL Server]

Under certain circumstances, you may have to start an instance of [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] in single-user mode.

For restoring a master database on Linux in single-user mode, see Restore the master database on Linux 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.

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.

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"

Note for clustered installations

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

See also