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/)
Nice one thanks. I suppose one would need to do this in a case of TempDB issues?
What kind of TempDB issue you are referring here ?
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.
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.
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.
[…] 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/) […]
[…] 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/) […]
[…] 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. […]
[…] 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. […]
[…] 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. […]
[…] 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. […]
[…] 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. […]
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?
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]
Can you please try with SQLCMD