Single User mode in SQL Server 2000

Posted on 2005-03-18
Medium Priority
Last Modified: 2012-06-27

I need to restore the MASTER database from the current backup.
Here is what I did.

from the command prompt

net stop sqlserveragent
net stop mssqlserver

C:\Program Files\Microsoft SQL Server\MSSQL\Binn>sqlservr -c -m
now the SQL Server is in single user mode.
At this point I dont have any user connections to the database.
When I try to access the MASTER database from EM it says "Login failed.Only one administrator is allowed in single user mode "...

I cant figure out who has logged in???
Before I did all this stuff I changed the STARTUP parameters from the EM connection and included -m ...

now even after restarting the service SQL Server still boots in SINGLE USER MODE.
There is no way I can access it now.

help me...

Question by:pai_prasad
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
LVL 27

Expert Comment

ID: 13578040
This article might help:


Are you logged in as sa? Try logging in as a sa and sa password. You should be good to go.
LVL 10

Author Comment

ID: 13578158
yep i am in as a "sa"...
i have read that article earlier..
i need to know how to boot the sql server in normal mode...
LVL 27

Expert Comment

ID: 13578295
You are booting SQL Server in single user mode - you are the only user - you are logging in as sa and you cannot access the server?  Is the SQL Server agent stopped?

Are you still using the -m parameter? From BOL:
You can override the default startup options temporarily and start an instance of SQL Server by using the following additional startup options.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 10

Author Comment

ID: 13578518
i am not using the -m parameter..
but somehow when i restart the service its in single user mode...
as i said i cant login using sa....
i try 2 restart the service from dos prompt...
LVL 43

Expert Comment

by:Eugene Z
ID: 13578752
check mssqlserver service - see if there is start parameter (General tab) - if yes - remove
LVL 10

Author Comment

ID: 13579104
there isnt any startup parameter in the service...
i think when i added -m from the connection of EM it did it..
i have deleted that connection from EM..but still that doesnt help...
LVL 43

Expert Comment

by:Eugene Z
ID: 13579177
can you restart mssqlserver service  usual way?
LVL 10

Author Comment

ID: 13579318
yes i can very well restart my mssqlserver service...
but every time it restarts in Single User mode...
i got lucky once..wat i did is stop the service...
start it again..
while its doing tat hit
isql -Usa -Ppassword thru command prompt...
i was able to login ..
wat parameter shud i change so tat the next time the service starts in normal manner...
LVL 10

Author Comment

ID: 13579629
well i answered my question myself...
its the SQL Agent which logs in and hence when i try to login with sa it doesnt allow..
i stopped the SQL Agent service..
restarted the mssqlservice...
took out the -m parameter from the startup setting of the EM connection and EUREKA !!!!!!!!!!!!

Accepted Solution

modulo earned 0 total points
ID: 13616922
Closed, 100 points refunded.

Community Support Moderator
Experts Exchange

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question