RESTORE MASTER DB in SQL 2000

Do not know SQL at all so assume that part of my problem is ignorance on the topic that is making me misinterpret documentation.  The only person with SQL background is on vacation.  HELP!  I need to restore the master db from a backup before I can put production databases back online for users.

1.  I know I need to start SQL in single-user-mode, but when I run the DOS command it returns the info I've copied and pasted below, BUT it never goes back to the DOS prompt as if it wasn't quite finished.  

2.  After 30 minutes of no activity, I give up on waiting for the DOS prompt and open Enterprise Manager but it returns this error:  A connection could not be established to SSD-SQL.  Reason: Login failed for user MY-DOMAIN\Administrator.  Reason:  Server is in single user mode. Only one administrator can connect at this time.  Please verify SQL Server is running and check your SQL Server registration properties by right-clicking the node and try again.

3.  This is a new install of SQL to replace failed hardware.  All production databases were detached and moved to the new server before installing SQL.  The master was backed up to a drive on the new server.  Production databases have been reattached successfully.  All we need is to restore the backedup master.

4.  Once I get past starting SQL in single-user mode and can access it with Enterprise Manager, I will also need a step-by-step explanation of how to do the restore.

5.  High points due to urgency.  SQL has been down for 3 days!



E:\MSSQL\Binn>sqlservr.exe -c -m
2006-04-07 08:18:58.25 server    Microsoft SQL Server  2000 - 8.00.194 (Intel X8
6)
        Aug  6 2000 00:57:48
        Copyright (c) 1988-2000 Microsoft Corporation
        Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2006-04-07 08:18:58.25 server    Copyright (C) 1988-2000 Microsoft Corporation.

2006-04-07 08:18:58.25 server    All rights reserved.
2006-04-07 08:18:58.25 server    Server Process ID is 4032.
2006-04-07 08:18:58.27 server    Logging SQL Server messages in file 'e:\MSSQL\l
og\ERRORLOG'.
2006-04-07 08:18:58.27 server    SQL Server is starting at priority class 'norma
l'(2 CPUs detected).
2006-04-07 08:18:58.34 server    SQL Server configured for thread mode processin
g.
2006-04-07 08:18:58.34 server    Using dynamic lock allocation. [2500] Lock Bloc
ks, [5000] Lock Owner Blocks.
2006-04-07 08:18:58.39 server    Attempting to initialize Distributed Transactio
n Coordinator.
2006-04-07 08:19:00.44 spid4     Warning ******************
2006-04-07 08:19:00.44 spid4     SQL Server started in single user mode. Updates
 allowed to system catalogs.
2006-04-07 08:19:00.44 spid4     Starting up database 'master'.
2006-04-07 08:19:00.50 server    Using 'SSNETLIB.DLL' version '8.0.194'.
2006-04-07 08:19:00.50 spid5     Starting up database 'model'.
2006-04-07 08:19:00.50 spid4     Server name is 'SSD-SQL'.
2006-04-07 08:19:00.52 spid7     Starting up database 'msdb'.
2006-04-07 08:19:00.52 spid8     Starting up database 'pubs'.
2006-04-07 08:19:00.52 spid9     Starting up database 'Northwind'.
2006-04-07 08:19:00.53 spid10    Starting up database 'Snohomish SD'.
2006-04-07 08:19:00.53 spid11    Starting up database 'AeXNS'.
2006-04-07 08:19:00.53 spid12    Starting up database 'AeXNS_Helpdesk'.
2006-04-07 08:19:00.55 spid13    Starting up database 'DKADatabase'.
2006-04-07 08:19:00.55 spid14    Starting up database 'eXpress'.
2006-04-07 08:19:00.59 spid5     Clearing tempdb database.
2006-04-07 08:19:00.59 spid8     Starting up database 'ITAssist'.
2006-04-07 08:19:00.63 spid9     Starting up database 'MailMeter'.
2006-04-07 08:19:00.63 spid7     Starting up database 'MSProject'.
2006-04-07 08:19:00.63 spid10    Starting up database 'MSProjSQL2003'.
2006-04-07 08:19:00.66 spid13    Recovery is checkpointing database 'DKADatabase
' (10)
2006-04-07 08:19:00.70 spid13    Starting up database 'InTouch_Sno'.
2006-04-07 08:19:00.70 spid8     Starting up database 'portal'.
2006-04-07 08:19:00.72 spid11    Starting up database 'ProjectServer'.
2006-04-07 08:19:00.74 spid7     Starting up database 'SharePointPortal_Config_d
b'.
2006-04-07 08:19:00.75 spid5     Starting up database 'tempdb'.
2006-04-07 08:19:00.78 spid13    Analysis of database 'InTouch_Sno' (16) is 100%
 complete (approximately 0 more seconds)
2006-04-07 08:19:00.78 spid8     Starting up database 'SPS01_Config_db'.
2006-04-07 08:19:00.81 spid7     Starting up database 'ssd-altiris_LM_W3SVC_5572
00837_Collab'.
2006-04-07 08:19:00.83 spid13    Starting up database 'STS_ssd-portal_1'.
2006-04-07 08:19:00.86 spid10    Starting up database 'STS_ssd-portal_2023571034
'.
2006-04-07 08:19:00.88 spid8     Starting up database 'STS_ssd-share_1'.
2006-04-07 08:19:00.89 spid7     Starting up database 'STS_ssd-share_686140274'.

2006-04-07 08:19:00.94 server    SQL server listening on Shared Memory, Named Pi
pes.
2006-04-07 08:19:00.94 server    SQL Server is ready for client connections
2006-04-07 08:19:00.97 spid8     Starting up database 'AeXNS_Incidents'.
2006-04-07 08:19:01.03 spid8     Starting up database 'Training1_PROF'.
2006-04-07 08:19:01.09 spid11    Starting up database 'Training1_SERV'.
2006-04-07 08:19:01.11 spid8     Starting up database 'Training1_SITE'.
2006-04-07 08:19:02.63 spid9     Analysis of database 'MailMeter' (13) is 100% c
omplete (approximately 0 more seconds)
2006-04-07 08:19:12.39 spid4     Recovery complete.
SSD-ECNAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin HillSr. SQL Server DBACommented:
I'm not seeing errors in the log....are you starting SQL as a console application ( -c parameter) here?  Will it start as a service?

0
SSD-ECNAuthor Commented:
As I said earlier, I'm SQL ignorant.  All I know is that I need to restore the master db and documentation tells me to go to a command prompt from the binn directory of MSSQL and issue command sqlservr.exe -c -m  which is supposed to put me in single use mode so I can do the restore.  
I'm just not sure if I'm doing this correctly as I can no longer access my databases in Enterprise Mgr.  The only way I can start SQL now is to reboot or possibly CTL-C the above command.  Then, yes, the service starts and I can see all databases.  

See #2 in my original question:  It almost seems as if I have a permissions issue.  I'm logged on as the same user who installed SQL and during installation chose Windows + SQL authentication.
0
SSD-ECNAuthor Commented:
I just tried CTL-C and it asked if I wanted to "shut down" SQL, when I say NO, it says "CTL-C dismissed, continuing execution."

This seems to say I AM running SQL in single-user mode.  So why can't I use Enteprise Mgr to run my restore?  Documentation says
1 .  Start SQL in single-user mode
2.  Use Enterprise Manager or Transact-SQL to restore master from backup.  

Don't know anything about Transact-SQL and can't connect with Enterprise Manager.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

SSD-ECNAuthor Commented:
I've read all the documentation.  I'm trying to follow the instructions which say to start SQL in single-user mode then use Enterprise Mgr to restore from the backup.  BUT when I start SQL in single-user mode, Enterprise Mgr won't connect!
0
SireesCommented:
Can you connect to Query Analyzer and then run the restore from there

USE master
GO
RESTORE DATABASE master
   FROM Disk = 'your backup location'
GO

0
SireesCommented:
Step by step process

1. Stop the MSSQL server and sql agent service
2. Go to command prompt and execute below command,.

sqlservr.exe -c -m


3. Login to sql server using query analyzer and restore the master database.
USE master
GO
RESTORE DATABASE master
   FROM Disk = 'your backup location'
GO


4. After the restore .. go to old comamnd prompt and press CONTRL and C to
close the old session


5. Go to control panel and start the MSSQL server and Sql agent service.
0
SSD-ECNAuthor Commented:
Sounds like this is the answer I need--just one more question.  How do I "login to sql server using query analyzer"  As I said, I do not know SQL at all.
0
SireesCommented:
OPen Enterprise Manager -> Tools ->SQL Query Analyzer

In Query Analyzer ->File ->Connect and enter the server name.
0
SSD-ECNAuthor Commented:
Ah, but that's my problem!  Once I run the sqlserver.exe -c -m, I can no longer open Enterprise Manager.  

I get this error:  "A connection could not be established to SSD-SQL.  Reason: Login failed for user MY-DOMAIN\Administrator.  Reason:  Server is in single user mode. Only one administrator can connect at this time.  Please verify SQL Server is running and check your SQL Server registration properties by right-clicking the node and try again."
0
SireesCommented:
OK go to

Start -> All programs->SQL Server ->Query Analyzer

While SQL Server is in single user mode you should still be able to connect from SQL Server Query Analyzer if you connect using the "sa" account.
0
SSD-ECNAuthor Commented:
In Query Analyzer, I tried logging in to the sa account and got the error: "Unabele to connect to server SSD-SQL:
Server: Msg 18461, Level 16, State 1
[Microsoft][odbc sql server driver]sql server]Login failed for user 'sa'.  Reason: Server is in single user mode.  Only one administrator can connect at this time."
0
SSD-ECNAuthor Commented:
OOPS!  Failed to mention that I'm connected to SQL via remote desktop.  Could this be my problem?
0
SireesCommented:
>> Only one administrator can connect at this time. <<

Just a dumb question...is any other administrator connected to the server?

>> Failed to mention that I'm connected to SQL via remote desktop <<

This should not be a problem.
0
SSD-ECNAuthor Commented:
No, there is no other administrator connected.  
0
SSD-ECNAuthor Commented:
I'm rebooting SQL just to make sure.
0
SSD-ECNAuthor Commented:
OK, I found my "other administrator" problem.  An application that accesses one of the databases was set up to run as administrator.  I've killed that connection and tried to restore the master with QAnalyzer and am now getting this error:
"Server: Msg 3168, Level 16, State 1, Line 1
The backup of the system database on device F:\SQL BU\master\master_db_200604060400.BAK cannot be restored because it was created by a different version of the server (134218546) than this server (134217922).
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally."
0
SireesCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.