At my wits end trying to move msdb, model, master

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

At my wits end trying to move msdb, model, master

Author

Topic

stormonts
Starting Member

3 Posts

Posted - 2010-03-07 : 23:57:53

We are running SQL Server 2005 and I am at my wits end trying to move msdb, model, and master databases.

I run "NET STOP MSSQL$SEP" and then run "NET START MSSQL$SEP /c /m /T3608". I start SQL Server Management Studio and am prompted to select the server instance. Selecting the one I am interested give me this message:

TITLE: Connect to Server
------------------------------

Cannot connect to hawk\sep.

------------------------------
ADDITIONAL INFORMATION:

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

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476

If I cancel that, click on "New query", I am again prompted to connect to a server and get the same message if I click on an instance.

How can I get this thing in single user mode and get to a query where I can move msdb, model, and master?

I have followed http://support.microsoft.com/kb/224071 but it isn't exactly clear there how to get to a query screen in management studio.

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2010-03-08 : 04:01:20

did u try sqlcmd to login to sql server; also check the permission of the login...

stormonts
Starting Member

3 Posts

Posted - 2010-03-08 : 13:15:49

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\zzmailman>sqlcmd
Canceling connection. Please wait...Sqlcmd: Error: Microsoft SQL Native Client :
Operation was canceled..

C:\Documents and Settings\zzmailman>net stop MSSQL SEP
The syntax of this command is:

NET STOP
service

C:\Documents and Settings\zzmailman>net stop MSSQL$SEP
The following services are dependent on the SQL Server (SEP) service.
Stopping the SQL Server (SEP) service will also stop these services.

SQL Server Agent (SEP)

Do you want to continue this operation? (Y/N) [N]: y
The SQL Server Agent (SEP) service is stopping.
The SQL Server Agent (SEP) service was stopped successfully.

The SQL Server (SEP) service is stopping.
The SQL Server (SEP) service was stopped successfully.

C:\Documents and Settings\zzmailman>net start MSSQL$SEP /c /m /t3608
The SQL Server (SEP) service is starting..
The SQL Server (SEP) service was started successfully.

No luck. ran net stop MSSQL$SEP then net START MSSQL$SEP /c /m /T3608

Then

C:\Documents and Settings\tester>sqlcmd -U sa -S hawk\sep
Password: Msg 18461, Level 14, State 1, Server HAWK\SEP, Line 1
Login failed for user 'sa'. Reason: Server is in single user mode. Only one admi
nistrator can connect at this time.

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2010-03-08 : 15:41:26

Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it

stormonts
Starting Member

3 Posts

Posted - 2010-03-09 : 13:24:32

I did stop the SQL Agent service and got the same message.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-09 : 17:25:08

Is the SQL Agent service configured to start automatically?

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources