Start SQL Server in Single User Mode

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw someone recently ask how to reset the sa password, and myself (along with a few others) suggested starting SQL Server in single user mode as an administrator. The poster had problems and at one point I suggested using the command line, which I had used in the past. However that didn’t’ work,  and when I tried it myself, it gave me errors.

The errors were Operating System errors, which indicated that the errorlog location wasn’t accessible by me. So I decided to elevate my privileges.

2015-07-06 13_48_41-Start menu

Once I did that, I was able to get the service to start:

2015-07-06 13_52_27-Administrator_ Command Prompt - sqlservr   -m -s sql2012

I’m not sure what was hard here, and this is how I’ve always managed to start and stop SQL Server in single user mode from the command line. I then see the output of what is sent to the error log at the console.

However I also searched around and found a few other ways that are easy to accomplish.

Net Start

I haven’t often used net start for commands, but I have a few times. In this case, I looked at BOL and found I could do this:

2015-07-06 13_43_20-Administrator_ Command Prompt

This is essentially what clicking "start" in the services applet or Configuration Manager does. However I can add in my "m" parameter with a slash (/) instead of a dash.

To stop the service, I use NET STOP.

2015-07-06 13_43_44-Administrator_ Command Prompt

Configuration Manager

If you run Configuration Manager, you can also add parameters. First, right click the service and get the properties. Then you want the "Startup Parameters" tab. In there, you can add a parameter in the top box, as I’ve done here.

2015-07-06 13_44_19-SQL Server (SQL2012) Properties

Clicking "Add" will put it in the list.

2015-07-06 13_44_26-SQL Server (SQL2012) Properties

When you stop the service, the next startup will have this parameter take effect.

2015-07-06 13_44_06-Sql Server Configuration Manager

Beware that when you start things up, the first connection that successfully logs into SQL Server will be the only one allowed. Any applications looking to connect, monitoring programs (SQL Monitor or DLM Dashboard) or other clients can take your connection, so be careful and quick.

SQLNewBlogger

This was really a quick writeup. I stopped my service and played for 10 minutes, ran a few searches, and took some screenshots. I would have been faster, but I wanted to document this.

This is great practice for a skill you need rarely, but when you do, you’ll be stressed. Be sure you can start and stop SQL Server a few ways, and add parameters like trace flags and the -m for emergencies.

References

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.