Master Database Restore in SQL Server 6.5
Laura Liotus
Has anyone successfully restored master database in SQL 6.5? I've
followed the instructions in Online Books. I receive error 2140 upon
trying to start the database.
Any help is appreciated.
Laura Liotus
Srinivas
I think the instructions are pretty clear. Try these steps
make sure your mssqlserver service is NOT running
1) From a dos window , switch over to \mssql\binn dir and start the sql
server this was
sqlservr -c -m -T4022
2) Then open another dos window, and establish a connection through isql
isql -E
3) The run the load command from the 1> prompt
load database master from disk="c:\master.dmp" ( or whereever the master
dump file is)
4) after the restore is done it will break the connection and the server
will stop.
Good luck ...
Laura Liotus wrote in message <362393D1...@msx.upmc.edu>...
Neil Pike
Yes, I've restored master a few times ok. Note that the 2140 is an NT
error, not a SQL error as such. Try the below
PSS ID Number: Q180738
Article last modified on 05-11-1998
WINNT:6.0,6.5
winnt
======================================================================
-----------------------------------------------------------------------
--
The information in this article applies to:
- Microsoft SQL Server, versions 6.0 and 6.5
-----------------------------------------------------------------------
--
SYMPTOMS
========
Attempting to start the MSSQLServer service may cause the following
error:
Windows NT Error 2140: An internal Windows NT error occurred.
The following error may also appear in the SQL Server errorlog:
Init config error 2. The system cannot find the file specified.
Opening master.dat for config information.
CAUSE
=====
These errors can be caused by any of the following:
- The Master.dat file is not in the correct directory, or the Windows
NT
account being used does not have appropriate permissions to that
directory.
- The Windows NT user account that the MSSQLServer service starts
under
does not have permission to the registry keys.
- An sp_configure value has been set out of range.
- Registry entries are missing.
WORKAROUND
==========
To resolve this problem, do the following:
1. Check to make sure the Master.dat file exists in the <SQL_root>\Data
directory. There may be a case where the master device was installed
to
another directory. To determine this, check the errorlog from the
<SQL_root>\Log directory. If you find an operating system error 2
("Could not find the file specified"), it will also list the path to
the
directory where SQL Server expects to find Master.dat.
If you locate Master.dat and it is not in the <SQL_root>\Data
directory,
use the following command to start the MSSQLServer service:
sqlservr /d<path_to_Master.dat> /e<path_to_errorlog>
NOTE: There is no space between the command switches (that is, "d"
or
"e") and the paths. For more information on the SQLSERVR
command-line
executable program, see the SQL Server Books Online.
If SQL Server starts, the startup entries in the registry are
incorrect.
To correct them, skip to step 4 of this procedure, below.
2. In Control Panel Services, select the MSSQLServer service and click
Startup. In the Log On As section, note what account SQL Server is
running under. Make sure this Windows NT account has all the
appropriate
permissions to read all the SQL Server registry keys, as well as the
directory containing the Master.dat file. For additional information
on
MSSQLServer service permissions, see the following article in the
Microsoft Knowledge Base:
ARTICLE-ID: Q164167
TITLE : INF: Troubleshooting SQL Security Manager Messages
3. Check to make sure that the sp_configure values are not set too
high.
For information on how to do this, see the following article in the
Microsoft Knowledge Base:
ARTICLE-ID: Q173090
TITLE : INF: Changing Configuration Values When SQL Server
Won't
Start
4. If none of the steps above resolves the problem, run Registry
Rebuild
for SQL Server from SQL Server setup. To do this, perform the steps
below.
CAUTION: It is recommended that you make a complete backup of your
system before performing the registry rebuild.
a. Restart the computer and log on as the administrator of the local
machine.
b. Use SQL Server Setup to note the options that are currently
configured in the registry, for example, the network libraries
that
SQL Server is currently listening in on, or the Security model
(Standard, Integrated, or Mixed) that SQL Server is using.
c. From a command prompt, issue the following command from the
<SQL_root>\Binn directory:
setup /t RegistryRebuild = On
NOTE: This command must be typed exactly as shown, including
capitalization. The spaces surrounding the equal sign (=) are
required.
SQL Server Setup will start. It will appear as though SQL Server
is
being reinstalled, but it is just rewriting the registry
information. Ensure that the path to where SQL Server and
Master.dat
are located is correct. Reset any options noted in the previous
step
(step b.)
NOTE: Information on the character set, sort order, and size of
the
master database is stored within the existing Master.dat file.
d. Start SQL Server normally.
MORE INFORMATION
================
For more information on the RegistryRebuild option of Setup, see the
following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q157805
TITLE : BUG: RegistryRebuild Option of Setup Is Not Documented
Neil Pike MVP/MCSE
Protech Computing Ltd
(Please post ALL replies to the newsgroup only unless indicated
otherwise)