Hi Everyone,

I am having trouble restoring the master database from one SQL 6.5 server to another SQL 6.5 server. After running a backup of the master database and copying this file to a test server I cannot get the database to restore.

The error message reported by Enterprise Manager is “[SQL Server] LOAD DATABASE must be used in single user mode if trying to restore the Master database." The option to check single-user mode in the database properties is grayed out on the database.

I have reviewed a number of knowledge base articles with no success. All of them state the master database needs to be in single-user mode to be restored??

If I try and run “sp_dboption 'master', 'single user', True” from an ISQL window I receive and error telling me “Msg 15243, Level 16, State 1 The option 'single user' cannot be changed for the 'master' database.”

Can anyone enlighten this newbie on how to restore the master database or why I am receiving this error??

Duane Dyar

-- Anonymous, February 22, 2001

Answers

Open SQL Server books and search for "restore master". There you will find an excellent article which will answer all of your questions.

-- Anonymous, February 22, 2001

Sorry, I also forgot to mention the following....

When I attempt to start the master database in single user mode using the following command from \mssql\binn,

Sqlservr /dd:\mssql\data\master.dat /c /m

The process starts but never finishes. The DOS prompt hangs on the stored procedure ‘sp_sqlregister’. The first couple of times I tried this I cancelled the operation after a bit of time. The last time I tried it I left it running overnight and it never finished.

I currently have two different test systems going that are running it as we speak with no success.

Thanks in advance,

Duane Dyar

-- Anonymous, February 22, 2001

Stop all the services related to SQL Server

MSSQL Server Service SQL Executive MSDTC

Run this syntax at the command prompt : start sqlservr.exe -m (note : only one user connection is available)

once you run the above command, another command prompt screen appears

Open Enterprise Manager, expand your sql server and restore you master database

Once your restore is done start sql server

Backup master database once your back to normal mode

-- Anonymous, July 06, 2001