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/)