garychu
asked on
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\l og\ERRORLO G'.
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 192.168.100.129: 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 127.0.0.1: 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
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\l
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 192.168.100.129: 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 127.0.0.1: 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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
no objections
sqlservr.exe –c -m