SQL Server 2012 - Single Mode User to Restore Master Db

[MickT]

I've closed all SQL Server service except the MSSQLSERVER, and have set that service to startup in single user mode by using the -m switch in the Configuration Window.

However, when I tried to login using the "sa" account" a dialog box popped up telling me that the service was in single user mode and only one Administrator could login.

I am the only Administrator as the server (Developer edition)is on my PC.

Has anyone come across this and resolved it, please?

I was following the instructions given in the Wrox book "Profession al Microsoft SQL Server 2012 Administration - Jorgensen & Others" in order to restore a backup of the Master Db after upgrading the PC to Windows 8 Pro.

[NeoPa]

And you have nothing else connecting to the service at the time you ran this? No querying session? Nothing?

[MickT]

I can't even login to run a query.

I stopped all the other SQL services.

I'm the only one using the SQL Server.

[NeoPa]

I thought you knew what you were about. The way the question was worded and you only having 24 posts even now (even though you've been a member almost as long as I have), shows you think before posting.

I'm afraid I'm as confused as you are Mick. I was hoping you may have overlooked something obvious (as so many people do), but it was always a long-shot i'm afraid. Let's hope someone has come across your situation before and can help you further.

[MickT]

Thank you for you interest and for trying to help.

[zmbd]

Mick:
Is this the EXACT error you are receiving, both text and number (other than the generic bit of course :) ):

Failed to connect to <servername>

Login failed for user 'sa'. Reason: Server is in single user mode.
Only one administrator can connect at this time. (Microsoft SSQL Server, Error: 18461)

{{Edit} Is the SQL Server Agent service still running?}

[zmbd]

This is what I was looking for:
Start SQL Server in Single-User Mode
Has a little bit worth reading :)

Related information concerning why you can't get a connection:
Connect to SQL Server When System Administrators Are Locked Out

[NeoPa]

Z:
Is the SQL Server Agent service still running?

I think you can take that as a No Z. Although nothing on that service specifically, he does state twice that :
I stopped all the other SQL services.

PS. I couldn't get either of your links to work for me :-(

[zmbd]

Neopa: Please try the links again. They worked both at work and now at home without any issues on my end.

I still had to double check about the server agent. From my IT guys that handle the SQL-Server for the plant it was the very fist thing he told me to ask.

[MickT]

Hello,

I am attaching the screen print of the error message. It seems to the same as described by zmbd.

SQL Server Agent was closed, along with all the other services, so that only MSSQLSERVER was running.

I've looked at the 2 links, and tried the 2 suggested -m switches. The sqlcmd was accepted but I still couldn't log in. The longer one came up as an invalid command.

I guess I'll have to re-created the logins, etc, and do it the longer way.

Thank you both for your interest and help.

[imgnothumb]http://bytes.com/attachments/attachment/6765d1355310726/single-user-mode-problem.jpg[/imgnothumb]

[zmbd]

Waiting on my I.T. guru to show up; however, I know him pretty well and can guess that he's going to say one of (if not both)... "well - it's obvious he missed something." and/or "... did he enter everything EXACTLY has shown in the links? I mean exactly, like case and spacing."
Now I'm guessing that's what he'll save; however, I've known him for some 20 years - one kind-of gets to know what to expect.

Anyway while waiting for my friend to get to work.... I took a poke out on the web and came up with this http://sqlserver-help.com/2012/02/08...t-should-i-do/ given that most of the time these blogs are blocked at work, I'm going to take it as a sign that someone in IT found this usuful or that you should have the information. You'll need to scroll down about mid-way to get to the step by step. One thing I noted is that there is a description for setting this up using the configuration manager!

If this does not work then I'm at a loss until later this morning when my support shows up (if, been a lot of cold/flu making the rounds and he didn't look so hot)... now it you want to know how to determine the amount of magnesium in a raw iron ore sample - I'm guy! :)

Best of luck!

[NeoPa]

Z:
From my IT guys that handle the SQL-Server for the plant it was the very fist thing he told me to ask.

Absolutely right. My point was only that the OP'd already stated it quite forcefully by then. Believe me when I say that was also my first thought. I only didn't phrase my first question that way because it had already been made clear. After their second post it was even more categorical. That said, I was only trying to clarify the state of things for you, not trying to criticise in any way. Of all people, I understand how easy it is to miss things in a thread. I've done it more than most, one way or another.

The links are working fine for me now BTW. I expect is was a temporary issue my end. Maybe at MSDN, but temporary either way.

@Mick.
One thing that isn't absolutely clear, and comes from one of the links posted by Z :
Did you close ALL services first, before then starting up the SQL Server service in the special way? If you close other services after starting up SQL Server then it won't work correctly.

[zmbd]

MickT: I certainly ment no offense by re-asking about the services... (read on :) )

Neopa: I had mentioned to my friend that OP had said he shut down everything SQL related. It appears however, that he has "been there/done that" himself, only to discover that the SQL Server Agent service was still running... despite the fact that he had "shut it down." I understand that OP had stated that he had shut down "all" of the services it in #1 and again in #3; however, I'm not going to disregard the inquery and experience of my friend when he's been in the industry some 20+ years.

In anycase... my IT SQLSrvr guy is in house... sounds like someone dug him up from the grave. He said basically what I thought he would... he did add that the most common mistake is to put a space between the "m", double-quote, and the "Microsoft. .." the next issue is that the command is case sensitive, and finally is that the account was not localadmin.

He said outside of that... without hands on he couldn't help.

[NeoPa]

Nor would I suggest you disregard your friend's experience Z. It's good that we can take advantage of that :-)

In short then, notwithstanding all that's already been said clearly and categorically, we need Mick to confirm that :

1. He shut down all SQL Server related services first, especially including the SQL Server Agent one.
2. He checked that all these services had shut down fully and completely before proceeding.
3. Only then did he try to start the SQL Server service in the way proscribed, ensuring that he didn't make the mistake of including an extraneous space after the double-quote (") of "m" and the word "Microsoft" which follows.

We already know there are no extra SQL processes started or running after this point, so that appears to be all the confirmation required.

[PsychoCoder]

Are you trying to connect via SSMS? If so that could be your issue, SSMS opens a connection when you connect via Object Explorer, then a new connect with each query you open.

Try this:

Open SSMS, do not connect to any database via Object Explorer, and open a Dedicated Administrator Connection (DAC) query windows by doing the following:

Click New Query

1. Type the following into the Server Name dialog box: ADMIN:ServerNam e\InstanceName
2. From here, you can run any scripts needed against your database in Single User Mode.

Let me know if that helps :)