• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1903
  • Last Modified:

Single User mode in SQL Server 2000

Hi,

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...

0
pai_prasad
Asked:
pai_prasad
  • 5
  • 2
  • 2
  • +1
1 Solution
 
ptjcbCommented:
This article might help:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4g4w.asp

Are you logged in as sa? Try logging in as a sa and sa password. You should be good to go.
0
 
pai_prasadAuthor Commented:
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...
0
 
ptjcbCommented:
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.



0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
pai_prasadAuthor Commented:
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...
0
 
Eugene ZCommented:
check mssqlserver service - see if there is start parameter (General tab) - if yes - remove
0
 
pai_prasadAuthor Commented:
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...
0
 
Eugene ZCommented:
can you restart mssqlserver service  usual way?
0
 
pai_prasadAuthor Commented:
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...
0
 
pai_prasadAuthor Commented:
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 !!!!!!!!!!!!
0
 
moduloCommented:
Closed, 100 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now