[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

RESTORE MASTER DB in SQL 2000

Posted on 2006-04-07
18
Medium Priority
?
2,166 Views
Last Modified: 2012-06-22
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.
0
Comment
Question by:SSD-ECN
  • 10
  • 7
18 Comments
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 16401747
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
 

Author Comment

by:SSD-ECN
ID: 16401855
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
 

Author Comment

by:SSD-ECN
ID: 16401933
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 20

Expert Comment

by:Sirees
ID: 16402136
0
 

Author Comment

by:SSD-ECN
ID: 16402159
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
 
LVL 20

Expert Comment

by:Sirees
ID: 16402247
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
 
LVL 20

Expert Comment

by:Sirees
ID: 16402260
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
 

Author Comment

by:SSD-ECN
ID: 16402285
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
 
LVL 20

Expert Comment

by:Sirees
ID: 16402316
OPen Enterprise Manager -> Tools ->SQL Query Analyzer

In Query Analyzer ->File ->Connect and enter the server name.
0
 

Author Comment

by:SSD-ECN
ID: 16402357
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
 
LVL 20

Expert Comment

by:Sirees
ID: 16402580
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
 

Author Comment

by:SSD-ECN
ID: 16402798
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
 

Author Comment

by:SSD-ECN
ID: 16402917
OOPS!  Failed to mention that I'm connected to SQL via remote desktop.  Could this be my problem?
0
 
LVL 20

Expert Comment

by:Sirees
ID: 16403005
>> 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
 

Author Comment

by:SSD-ECN
ID: 16403022
No, there is no other administrator connected.  
0
 

Author Comment

by:SSD-ECN
ID: 16403046
I'm rebooting SQL just to make sure.
0
 

Author Comment

by:SSD-ECN
ID: 16403317
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
 
LVL 20

Accepted Solution

by:
Sirees earned 2000 total points
ID: 16403383
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question