Putting MSSQL 2000 into single user mode for master db restore

MSSQL 2000 Std SP3 is installed in a box running Win 2003 Server SP1.
I have been trying to set it up as a standby server.

I tried to test restore an application database.
First by;
net stop mssqlserver
Followed by;
sqlservr -m
to it into single user mode.

With the following results;
2007-03-21 18:18:01.45 server    Copyright (C) 1988-2002 Microsoft Corporation.
2007-03-21 18:18:01.45 server    All rights reserved.
2007-03-21 18:18:01.48 server    Server Process ID is 3700.
2007-03-21 18:18:01.48 server    Logging SQL Server messages in file 'd:\sql\goodhealth\MSSQL\log\ERRORLOG'.
2007-03-21 18:18:01.53 server    SQL Server is starting at priority class 'normal'(1 CPU detected).
2007-03-21 18:18:01.67 server    SQL Server configured for thread mode processing.
2007-03-21 18:18:01.73 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2007-03-21 18:18:02.04 server    Attempting to initialize Distributed Transaction Coordinator.
2007-03-21 18:18:03.37 spid3     Warning ******************
2007-03-21 18:18:03.39 spid3     SQL Server started in single user mode. Updates allowed to system catalogs.
2007-03-21 18:18:03.40 spid3     Starting up database 'master'.
2007-03-21 18:18:03.53 server    Using 'SSNETLIB.DLL' version '8.0.760'.
2007-03-21 18:18:03.54 spid5     Starting up database 'model'.
2007-03-21 18:18:03.56 server    SQL server listening on 1433.
2007-03-21 18:18:03.57 spid3     Server name is 'EXACT-STANDBYA'.
2007-03-21 18:18:03.57 server    SQL server listening on 1433.
2007-03-21 18:18:03.59 server    SQL server listening on TCP, Shared Memory, Named Pipes.
2007-03-21 18:18:03.60 server    SQL Server is ready for client connections
2007-03-21 18:18:03.62 spid7     Starting up database 'msdb'.
2007-03-21 18:18:03.64 spid8     Starting up database 'pubs'.
2007-03-21 18:18:03.64 spid9     Starting up database 'Northwind'.
2007-03-21 18:18:03.96 spid5     Clearing tempdb database.
2007-03-21 18:18:04.40 spid5     Starting up database 'tempdb'.
2007-03-21 18:18:04.48 spid3     Recovery complete.
2007-03-21 18:18:04.48 spid3     SQL global counter collection task is created.

The process seems incomplete at this point. And MSSQL does not appear to have entered into single user mode because on attempting a restore of the master db in sql analyser, a message returns that single user mode is required. Similar outcome when restore is attempted from Enterprise Manager.

Any assistance would be gratefully received. Thanks
Who is Participating?
garychuConnect With a Mentor Author Commented:
I had not realised that inspite of the message output, sqlservr -m had actually placed MSSSQL Server in single user mode. I left the process running and proceeded to do the Master restore in query analyser.
On successful completion of the restore, the sqlservr -m process terminated normally by itself.

For a newbie like me, the whole procedure was certainly not a user friendly one.
But thanks for the input, angeliiii.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
single mode startup is actually:
sqlservr.exe –c -m

garychuAuthor Commented:
Thanks angellll
I have actually tried that as well, with no difference in result.
Is there a way to tell if in fact sqlservr has been put into single user mode?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no objections
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.