How to start the default instance of SQL Server in single-user mode through Command Prompt

Article
11/15/2012

Question

_{Thursday, November 15, 2012 2:24 PM}

Thanks

Thanks, Purna

All replies (29)

_{Thursday, November 15, 2012 2:40 PM ✅Answered}

Open a command prompt by running as administrator and browse to path where you have SQL Server binaries(binn folder) and run below command to start default instance in single user more

sqlservr.exe -m

for more info refer

http://msdn.microsoft.com/en-us/library/ms180965(v=sql.105).aspx

_{Thursday, November 15, 2012 4:23 PM ✅Answered | 1 vote}

I hope you are doing on your test server.. & learning prospective-

Inorder to bring single user mode you have to Browse the sqlservr.exe file where it exists-

the easiest way you can get that exe is as mentioned below (Note I have used My test server)-

go to start->run->type cmd->type services.msc->search for SQL server (MSSQLserver) name->right click properties->

->under General tab you can see path to executable: you can see like below-(see here where that Sqlservr.exe exists under My test machine)
"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER

so copy up to this path : C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\

then again go to cmd-> navigate to path then type ->
cd C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\ ->hit the enter button then you can see like below-

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>

then type like this C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn> sqlservr.exe -m

Note ->If any other SQL server service if it is started then stop it..for ex-sql server agent...

Inorder to stop you can press Ctrl+C or if you close cmd it goes. ->it will ask for y/n --select what you need..

there are many other way also it exists if need then let me know.

Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

_{Saturday, November 17, 2012 7:46 AM ✅Answered}

Try with this

net stop MSSQLSERVER

net start MSSQLSERVER /m

Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::

_{Thursday, November 15, 2012 2:53 PM}

Hi Keerthi,

are you there

Thanks,

Purna

Thanks, Purna

_{Thursday, November 15, 2012 2:55 PM}

Hye poorna..yes tell me if you have any further questions on this

_{Thursday, November 15, 2012 3:04 PM}

hi Keerthi,

i am trying different ways to put the single user mode using command prompt..

i have no screens ..

Thanks,

Purna

Thanks, Purna

_{Thursday, November 15, 2012 3:07 PM}

Hi Keerthi,

if have any possible give me your mail id or voice number..

because i am self learner i have no.of dots please provide your number..

i hope your help full for me..

Thanks,

Purna

Thanks, Purna

_{Thursday, November 15, 2012 4:25 PM}

IMO, the far easiest way is to do thir fro the Services applet. Properties for the service, add -m parameter, do *not* SELECT OK here, but instead select Start.

Tibor Karaszi, SQL Server MVP | web | blog

_{Thursday, November 15, 2012 4:35 PM}

run sqlservr.exe -m in the bin folder of the installed folder

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2K8\MSSQL\Binn>sqlservr.exe -m

this path is from my desk top check it either

Ramesh Babu Vavilla MCTS,MSBI

_{Thursday, November 15, 2012 5:51 PM}

why do you need to switch SQL Server into single-user mode ?

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

_{Friday, November 16, 2012 2:11 AM}

Hi Rama,

as per your step i am trying to put single user mode through command pramt

But i am getting the below error please resolve my issue

Thanks,

Thanks, Purna

_{Friday, November 16, 2012 2:19 AM}

give sqlservr.exe not sqlserver.exe

_{Friday, November 16, 2012 3:12 AM}

Hi Rama,

Again i am facing below error ...

Thanks,

Thanks, Purna

_{Friday, November 16, 2012 3:25 AM}

usually the single user mode you will use for some Maninteance and troubleshooting purpose of SQL server service incase if it is unable to start(we can use minimal or other trace flag to start the DB engine)..

Ok tell me before your starting through the CMD what was the SQL server service status ,is that was running, if so that wil get lock so you need to stop and then try from CMD

Note -->I hope you are doing on test server..:)

_{Friday, November 16, 2012 3:27 AM}

Could you let us know what happened and why you are trying to start SQL in single user mode.

Try opening the command prompt by running as administrator, verify if the SQL Server errorlog folder exists and you have enough permissions on the folder

_{Friday, November 16, 2012 11:05 AM}

Hi Rama,

Cd E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn (enter)

E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn> sqlservr.exe-m

Thanks for your valuable suggestions, but still also i am unable to put the single user mode through command prompt,

if any possible give me a your valuable E-MAIL ID:

Thanks, Purna

_{Friday, November 16, 2012 11:09 AM}

Hi Keerthi,

Activally i am new learner in sql dba , in my orng one my friend ask this ques ,

that way i am trying different ways but i am unable to get the result .

Any way Thanks for your valuable suggestions, but still also i am unable to put the single user mode through command prompt,

Cd E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn (enter)

E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn> sqlservr.exe-m

if any possible give me a your valuable E-MAIL ID:

i have no.of dots that way i am asking your mail id;

Thanks, Purna

_{Friday, November 16, 2012 12:54 PM}

> Net start mssqlserver /m (for default instance)

> Net start mssql\Instance name> /m (for named instance)

Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

_{Friday, November 16, 2012 1:07 PM}

what is the error you are getting-- the other way you can do as mentioned below-1.Go to cmd ->net start MSSQLSERVER /m
to stop use ->net stop MSSQLSERVER*

Use. SCM(SQL server configuration Manager) using startup parameter->

i,e put -m like below-
-m;-dC:\My master\master.mdf;-eC:\My master\ERRORLOG;-lC:\My master\mastlog.ldf ->click ok*

it will ask for restart ->do perform*
*

_{Saturday, November 17, 2012 3:12 AM}

Hi Rama,

I know this way..

*2. Use. SCM(SQL server configuration Manager) using startup parameter->

i,e put -m like below-
-m;-dC:\My master\master.mdf;-eC:\My master\ERRORLOG;-lC:\My master\mastlog.ldf ->click ok*

it will ask for restart ->do perform

but i need to open on only CMD

please help me..

Thanks

Thanks, Purna

_{Saturday, November 17, 2012 3:22 AM}

*Go to cmd ->net start MSSQLSERVER /m

to stop use ->net stop MSSQLSERVER*
*

_{Saturday, November 17, 2012 3:36 AM}

Now also i am getting the below error .

Thanks, Purna

_{Saturday, November 17, 2012 3:39 AM}

please give me u r cell no or give me call on this +91-9036409290

please rama

Thanks, Purna

_{Saturday, November 17, 2012 3:41 AM}

hello,

Give the one space after MSSQLSERVER ->like this MSSQLSERVER /m

net start MSSQLSERVER /m

_{Sunday, November 18, 2012 5:13 AM}

Hi Nagu,

i am trying this way also but i am getting below error.

please give me if any other way but only through CMD

Thanks, Purna

_{Sunday, November 18, 2012 5:17 AM}

Hi Rama,

thank you very mach i put the single user mode through CMD ..

Thanks, Purna

_{Sunday, November 18, 2012 5:18 AM}

Hi Nagu,

thank you very mach i put the single user mode through CMD ..

Thanks, Purna

_{Friday, November 23, 2012 5:02 AM | 2 votes}

Hi Purna

Can you please Mark Answer if it is helpful

Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::

_{Monday, November 26, 2012 6:49 AM}

Hi Rama,

thank you very mach i put the single user mode through CMD ..

Thanks, Purna

Hi Purna,

Could you please share the solution with us? If someone provided help for you, then please mark his reply as answer, thanks.

Ray Chen
TechNet Community Support

Share via

How to start the default instance of SQL Server in single-user mode through Command Prompt

Question

All replies (29)

Additional resources