How to start the SQL instance in Single-User mode?

Sometimes it becomes necessary for DBA to start the SQL Instance in Single-User mode to perform maintenance task like restoring master database upon corruption etc.

One can do this in different ways using

1. SQL Server Configuration Manger

2. Windows Command prompt

1. SQL Server Configuration Manger

 

Go to Start >> All Program >> Microsoft SQL Server 2008 R2 >> Configuration Tool>> SQL Server Configuration Manger>>Click SQL Server Services Tab

Right click on engine service SQL Server (MSSQLSERVER) and click properties tab.

 In properties window click Advanced Tab and type “-m;” in Startup Parameters and click Apply and OK.

  

 Now you can connect the instance through management studio in Single-User mode.

2. Window Command Prompt

To start the SQL Server in single user mode through command prompt follow the step below

1. If SQL instance in running then stop all the services running on the instance.

2. Access the command prompt (C:\>)

3. Type net start MSSQLSERVER /m

For named instance use MSSQL$<instance name>