Home » SQL Server » How to Start SQL Server in Single User Mode ?

How to Start SQL Server in Single User Mode ?

Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc.

Steps to start SQL Server in Single User Mode :-

1) Stop SQL Server Services

2) Add [-m] startup parameter in SQL Services from configuration manager

Parameter [-m] Starts SQL Server instance in single-user mode. SQL Server in single-user mode can connect with single user only and did not start CHECKPOINT process. 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.

From SQL Server 2005 to SQL Server 2008 R2

From SQL Server 2012 onwards

3) Start SQL Services & SQL server will come online in single user mode. You can connect using SQLCMD & continue with desired operation

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)


18 Comments

  1. BV says:

    Nice one thanks. I suppose one would need to do this in a case of TempDB issues?

  2. Michael Meierruth says:

    Sometimes doing it just for a single database can be very useful too:
    alter database MYDB set single_user with rollback immediate
    go

    • Yes. Bringing DB in single user mode also required many time. But taking instance in single user mode in compare of database in single user mode is bit different. When instance in single user mode then one connection for all databases but when DB in single user mode instance & other DB will remain intact & keep working fine. Only that DB allow only one connection at one time.

  3. Tony says:

    Couple questions:

    In Step #1 – Do you stop all SQL Server services? Or, just the two that you have stopped in the image?

    In Step #3 – Do you have to connect using SQLCMD? Or, can you use SQL Server Management Studio? If you have to connect using SQLCMD, what is the best way to do that?

    • 1) Yes, only these 2 services are required. Not all.
      2) Preferred one is SQLCMD because SSMS create more than one connection itself to database that why you can face issue while connecting using SSMS when instance in single user mode. Got to Command Prompt > Type SQLCMD with Server name & user name & password.

  4. Chowdary says:

    if we add the -m to the Startup parameter does it make all the databases into a Single User mode?

    • According to theory NO. When you use -m you SQL instance will work as single mode. No directly impact on database. But I go to practically, If you instance is in single user mode that means only one user can connect to instance at one time. This will result all database to be behave like single user.

  5. […] You can also do it from configuration manager, Like we start SQL server in single user mode. (http://mssqlfun.com/2014/09/04/how-to-start-sql-server-in-single-user-mode/) […]

  6. […] How to Start SQL Server in Single User Mode ? « … – Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc. […]

  7. […] How to Start SQL Server in Single User Mode ? « … – Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc. […]

  8. […] How to Start SQL Server in Single User Mode ? « MSSQLFUN – Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc. […]

  9. […] How to Start SQL Server in Single User Mode ? « … – Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc. […]

  10. Single User Sql Server Command | Dumamey says:

    […] How to Start SQL Server in Single User Mode ? « MSSQLFUN – Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc. […]

  11. Brian Hart says:

    When I tried putting the -m; switch just now and attempting to then restart the SQL2008 service on my machine, it hangs at “starting service…” for a long time and then says “The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.” What am I doing wrong?

  12. Mark Pawelek says:

    Does not work as described above.

    When I installed this instance as MYSVR\Admin, I was under the impression that the sa account could be used to gain access and that the system would create a windows account for the user (MYSVR\Admin) who installed it. Neither account seem to be on this instance.

    So I configured this instance to start in single user mode (startup parameter -m)

    Q1: In computer management, Services. It now shows:

    Name: SQLEXPRESS
    Status: Running
    Startup Type: Automatic
    Log On As: Local System

    So now I try to connect to the running service via SQL Server Man Studio:

    Server Name: MYSVR\SQLEXPRESS
    Authentication: Windows
    User Name: MYSVR\Admin

    While I am logged on to this server using the MYSVR\Admin account. It just throws back “A network-related or instance specific error occurred while establishing a connection to Sql Server.” [Error: 2]

Leave a Reply

Join us on Facebook

microsoftcommunitycontributor
%d bloggers like this: