473,320 Members | 1,838 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL Server 2012 - Single Mode User to Restore Master Db

28
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 "Professional 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.
Dec 11 '12 #1
14 8839
NeoPa
32,556 Expert Mod 16PB
And you have nothing else connecting to the service at the time you ran this? No querying session? Nothing?
Dec 11 '12 #2
MickT
28
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.
Dec 11 '12 #3
NeoPa
32,556 Expert Mod 16PB
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.
Dec 11 '12 #4
MickT
28
Thank you for you interest and for trying to help.
Dec 11 '12 #5
zmbd
5,501 Expert Mod 4TB
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?}
Dec 11 '12 #6
zmbd
5,501 Expert Mod 4TB
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
Dec 11 '12 #7
NeoPa
32,556 Expert Mod 16PB
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 :-(
Dec 12 '12 #8
zmbd
5,501 Expert Mod 4TB
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.
Dec 12 '12 #9
MickT
28
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.

Attached Images
File Type: jpg Single User Mode Problem.jpg (37.2 KB, 3727 views)
Dec 12 '12 #10
zmbd
5,501 Expert Mod 4TB
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!
Dec 12 '12 #11
NeoPa
32,556 Expert Mod 16PB
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.
Dec 12 '12 #12
zmbd
5,501 Expert Mod 4TB
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.
Dec 12 '12 #13
NeoPa
32,556 Expert Mod 16PB
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.
Dec 12 '12 #14
PsychoCoder
465 Expert Mod 256MB
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:ServerName\InstanceName
  2. From here, you can run any scripts needed against your database in Single User Mode.

Let me know if that helps :)
Dec 14 '12 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: debashish | last post by:
Hello Faculties When doing recovery after loss of data files, one would like to start SQL Server in single user mode. Is this possible? Thanks in advance Debashish
1
by: GG | last post by:
Hi, We are planning to create a prod environment on our Dev server, by creating a new named instance on DEV. Our prod server is running on sql enterprise edition 64bit and dev on SQl enterprise...
0
by: rm | last post by:
Through the LDB viewer we are seeing one of our servers in the "users listed" list. The information is legitimate as we have code that is dependent on the non-presence of the LDB file. The code...
0
by: Tom Kaminski [MVP] | last post by:
I want to show a table of master records, with the right most column displayed as a comma (or space) delimited list of details. The practical application of this is a thesaurus, where the master...
4
by: Mark | last post by:
I am setting up an asp.net site using the Ektron CMS. Every page in the site uses the same basic template, with the look of the page changing via CSS and an id on the body. The id is shared by all...
1
by: lior | last post by:
I'm working on asp .net project,when trying to work in debug mode IE stuck(not responding). When I debuged it I saw it stuck when trying to connect to the sql server. After that when I'm...
20
by: Keith G. Murphy | last post by:
I'm trying to get a feel for what most people are doing or consider best practice. Given a mod_perl application talking to a PostgreSQL database on the same host, where different users are...
1
by: Lonnie | last post by:
I'm trying to use the Microsoft Index Server with ASP.NET 2.0 master pages. I'm aware that the "doctitle" = the <title> entry and the "characterization" = the "description" meta tag. If these tags...
1
by: Tim Royal | last post by:
I've got an odd little issue where I've got some web pages that are all identical in content; however, I'd like to have them surrounded by different master pages (so one is a master page branded...
4
by: Veeraraghavan | last post by:
Hi All, I am developing a client server communication using system.net.socket and I am finding it very difficult to get a solution for this. I started with single port communication with single...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.