Can't start sql server in single user mode

Question

I'm trying to recover my local sql server's sa password.

I read that I should put it in single user mode, and I did that.

I added the -m; to the startup parameters.

I don't have sql agent running, and I restarted the Sql Server.

When I try:

sqlmd -S .\sqlexpress

I get the message:

Login failed for the user 'mydomain\myuser'. reason: server is in single user mode.
only one administrator can connect at this time.

I also tried changing the startup parameter to:

-mSQLCMD

but I get the same error.

Answer 1

Did you try:

NET STOP MSSQLSERVER

… give it a minute … 

NET START MSSQLSERVER /m

Answer 2

What worked for me what basically the same thing but I didn't expect what I should do.

NET STOP [MSSQLSERVERName]

NET START [MSSQLSERVERName] /m /c /f

No go (But actually this is what worked)

Now, at first I thought nothing had changed because I got a failure message that the server couldn't be started. I also checked out sql server configuration manager and checked the service status. Where it usually read started or stopped it read pending changes.

Unexpected Hang

I couldn't stop or restart the service and thought it was hung so I restarted my computer. It was not until later when I figured out how to fix the problem that I realized that "pending changes" might be the result I wanted to see (I guess, "pending changes" means go fix the problem now).

Up and running

I tried the net start command again followed by checking sql configuration manager again -- again "pending changes..."--. But, this time I started up Sql Server Management Studio and I was back in business again, thankfully!

-c
Starts SQL Server service independently of the Service Control Manager. Doing so speeds up startup.

-f This flag starts SQL Server with minimal configuration and allows updates to system tables. This is useful if the DBA has tweaked the configuration options such that the service won't start. If you find yourself in such situation you'll appreciate "-f" flag, since this could be the only way to correct the mistakes in the SQL Server configuration.

-m
Starts SQL Server in single-user mode. This means only one user can connect to the server at one time and ad-hoc updates of system tables are allowed. Furthermore, -m option does not start CHECKPOINT. Single-user mode is useful when one of the system databases is corrupt and you have to reload it from a backup.