Starting SQL server in single user mode?

[Swati]

Hello!

I want to know how to start SQL in single user mode?

Regards!

[Dino.M]

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.

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.

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.

[opaper]

Sql Server can be started from command line, using the Sqlserver.exe. This exe has some very important parameters with which a Dba should be familiar with.
-m is used for starting Sql Server in single user mode and -f is used to start the Sql Server in minimal configuration mode

[Praetor]

Single User mode allows only one single user to connect to the database.
Even if SQL server is in Single User Mode, you can connect the SQL Query Analyzer with the SA user.

Single user mode specifies that only one user at a time can access the database. Basically it is used for maintenance purposes.

If any other users are connected to the database and you switch the database to single user mode then their connections to the database will be closed without any warning message.

Following are the steps to start SQL server in Single User mode -

On your server : Start >> Run >> type in CMD and hit Ok >> it will open up Command Prompt >> here execute the following command

net stop MSSqlServer

>> it will stop the SQL service >> now to start SQL in Single User mode execute following command

sqlservr -m

####

How to Set a single database into Single User Mode :-

You can set a single database into single user mode using an alternative procedure but this process cannot be used to set the MASTER database into single user mode.

# Connect to SQL Server using SQL Query Analyzer with SA user

# Execute the following command -

alter database database-name set SINGLE_USER

where “database-name” is the actual name of the database.

This cannot be used to place the databases MASTER, MSDB or TEMPDB in single user mode.

# To set the database back into normal multi-user mode use, execute the following command -

alter database db-name set MULTI_USER