Hello,

Sorry for reposting this issue but I never received a response to my
follow up last week(on the 3rd). I am having a problem restoring the
master db on a w2k/sql2k active/passive cluster. After I start sql
server in single user mode I am unable to connect to query analyzer to
run the restore. I get a message saying that this server is in single
user mode and only one admin can be connected. No other apps connect
to this test server besides sql server and the cluster service. The
last response I received was that it could be the sqlagent service. I
removed sqlagent and microsoft search as dependencies of sql server,
set them to do not restart, and disabled them in services. Still when
I try to connect to query analyzer I get the same message. I'm not
sure what is grabbing this connection. Are there any known issues
such as this or am I missing something? Any advice or ideas would be
greatly appreciated.

Also, I re-posted this message about an hour ago but for whatever
reason it hasn't shown up yet in the newsgroup, so in case I made a
mistake posting, I am resending.

Thanks,
Greg

Are you able to start EM, or just not QA? What other SQL-
related apps are open (i.e. Service Manager, etc.)? Is
your applciation opening a connection?

This is not a cluster problem as in this case, it's just
backup and restore stuff ... you're really not interacting
at the cluster level.

Thanks for the reply. No, I am not able to start EM(gives same
message), there aren't any other sql apps open, and no other apps
connect to this machine. This server is to be used solely for
disaster recovery testing, query tuning, etc. but there will not be
any apps outside of sql server and cluster service connecting to sql
server.

When I start the sql service back up in multi user mode and run
profiler to see who is connected it only lists the following as being
connected - cluster service, sqlagent generic
refresher, sqlagent alert engine, and SQLDMO_1. These all run under
the context of the sql server service domain user account. I agree
that something must be grabbing this connection to prevent me from
logging in but have no idea what it could be as nothing outside of
cluster service and sql services show up in profiler. I will try this
again in case I am missing something. I assume that there is no
difference restoring master on a clustered server opposed to
nonclustered. Is that correct?

Just thought this issue may be related to clustering somehow as I've
never experienced this issue while trying to restore the master
database before. My experience has strictly been restoring on a
nonclustered config.


On Mon, 8 Dec 2003 13:58:05 -0800, "Allan Hirt"
<allanh (AT) NOSPAMavanade (DOT) com> wrote:

Are you starting SQL Server up from the command prompt when you restart in
single user mode to perform the restore? You have to do it this way to
prevent the cluster service etc logging in straight away.

From the
\MSSQL\BINN folder
run
sqlservr.exe -c -m

HTH
Tony.

"Greg" <gjackson (AT) thq (DOT) com> wrote

Hi,

I had the same issue than Greg.
There is no way to restore the master db and get single
user access on a clustered SQL server.
I have not received any clear answer from Microsoft but
the cluster service seems to be the cause of the issue.

Champ

The steps to restore master database is same for clustered and
non-clustered SQL Servers. Only in a clustered SQL Server, you need to
perform the steps on the node that owns the SQL shared disk.

From the error message, it definitely appears that someone is grabbing the
connection. If you are unable to find out who is grabbing the connection,
one option is to stop all unnecessary services. The following tables list
the services that should be left running on both the nodes. Stop all other
services (BE CAREFUL : Donot stop any service that absolutely needs to be
running -- for example --> any SAN related services , talk to the NT admin
before stopping the services).

Windows 2000 Advanced Server and Windows 2000 Datacenter Server
" Alerter
" Cluster Service
" Computer Browser
" Distributed File System
" Distributed Link Tracking Client
" Distributed Link Tracking Server
" DNS Client
" Event Log
" License Logging Service
" Logical Disk Manager
" Messenger
" Net Logon
" NT LM Security Support Provider
" Network Connections " Plug and Play
" Print Spooler
" Process Control
" Remote Procedure Call (RPC) Locator
" Remote Procedure Call (RPC)
" Remote Registry Service
" Removable Storage
" Security Accounts Manager
" Server
" TCP/IP NetBIOS Helper
" Windows Management Instrumentation
" Driver Extensions
" Windows Time Service
" Workstation

Once this done. Open Cluster Administrator and see which node owns the SQL
shared disk. Make sure that SQL Server resource is taken offline. Then go
to the node that owns the SQL shared disk; start sql server in single user
mode; connect using QA and restore master db

(NOTE: If your master db is corrupt and SQL Server does not comes online,
then you will first need to rebuild master as per
http://support.microsoft.com/?id=298568 and then restore the master
database from a clean master db backup. As a safety factor, I will take a
file copy of the master, msdb, model dbs mdf and ldf files before running
rebuildm)


Regards,

Uttam Parui
SQL Server Developer Support Engineer, MCDBA, MCSE, MCT
Product Support Services
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.