September 30, 2004 at 8:50 am
I have backed up the master, model and msdb from our live server and managed to restore on my test server EXCEPT master. I am also unable to make the master database into 'single user' via the properties > options as it is greyed out.
My live server is NT4 sp6 & sql2000 sp3a
My test server is win2003 and sql2000 sp3a
What am I doing wrong?
Should I have backed up the DBs on the live server in a special way? I need to test this as we are going to move from NT4 to WIN2003.
Any help/advice appreciated.
October 1, 2004 at 2:38 am
When you want to restore the master database you have to set sql server in single user mode using a command line and the following switches.
sqlservr.exe -c -m
Look in BOL under Restoring the Master database from a current backup.
Hope this helps.
Andrew Barton
October 1, 2004 at 9:46 am
Thanks Andrew, I've tried sqlservr.exe -c -m and that failed with the message cannot find errorlog file although it is there in the right folder and with correct permission etc.
So in Query Analyser, I tried ALTER DATABASE MASTER SET SINGLE_USER and that too failed with message saying that SINGLE_USER option cannot be used to set on master.
Through the Enterprise Manager, master database proprerties and option it won't allow me to select single user because it is greyed out.
I've rebuilt the server and re-installed SQL2000 but still cannot get to restore master from my production backup set. Any help appreciated. Regards
October 1, 2004 at 11:59 am
You might want to try stopping the service and Then in the start parameters put /m and restart the service. This will start the instance in single user mode.
October 1, 2004 at 3:14 pm
What is your reasoning for restoring the master database from one server to another, be more specific in what you are trying to accomplish >>> if you are migrating SQL Server to a new OS just reinstall SQL Server. there are some major differences in the OS that you don't want to use a master db created on one and move it to the other, >>> you will be spending a lot of time trouble shooting errors that probably can't be explained or fixed.
Once the install is complete, create your user databases (empty) and restore from backup.
good luck.
October 4, 2004 at 3:38 am
Thanks all for helping to resolve my problem. It worked by stopping the service and then I was able to make master in single user allowing restore.
I needed to restore the master as well as all other sys & user databases both as part of a backup strategy and also test migration NT to win2003. The sys databases are important as they contain references/info about the user databases so I'm not sure if using the master from a fresh sql install would work.
Thanks again and also to site owners for this great resource. Regards
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply