How to restore master database in a SQL Server 2005 Cluster

  • I did the following:

    a. Change the SQL Server start up parameter to add -m (Single User Mode).

    b. STOP all SQL Server related services.

    c. Restart SQL Server service

    After doing this when i try to connect using SQL Server management studio it says only one connection is allowed. I am not sure which other resource is connected. Please help

    Its a SQL2005 Cluster sitting on top of a windows 2003 clustering service

    There are 2 nodes configured. Any help would be appreciated. Thanks

  • when restoring Master, I usually stop SQL Server and then start it from a command prompt

    from command prompt, navigate to the directory where the sql server executeable resides and start it

    sqlservr -m -c

    or for a named instance sqlservr -m -c -s instancename

    I then start open query analyzer or Enterprise manager and restore (don't open both.. that's two sessions : )

  • I seem to remember reading that SSMS fires up more than one connection so you will have problems using it in single user mode. Execute the restore master command via one of the command line utilities such as SQLCMD or osql (see BOL)

    ---------------------------------------------------------------------

  • Thanks george, in my case. It seems that the clustered windows O/S is taking up the connection. When I tried to run the server in normal mode and ran a trace on the connection. I found that the window o/s is connect to sql (may be due to clusters).

    Also, FYI we use Veritas Netback. Could that be the issue?

  • Ah, the isalive process is getting there first. I think you need to go to cluster admin and take sql resources offline first (to take cluster out of it) and then restart sql is single user mode.

    see http://www.sql-server-performance.com/faq/cluster_restore_master_database_p1.aspx

    the ssms bit might still be true so use SQLCMD if you have to

    ---------------------------------------------------------------------

  • George,

    Thanks a lot for that link. you were true thats why i need to do with a lil tweak. This is what I did:

    A. Bring all the resources of the SQL Server Group in the cluster offline (except for Disk drive and IP Address).

    B. go to command prompt and start sql server using the following command sqlsrvr.exe -m (single user mode)

    C. Connect to sqlcmd using ip address of the sql service sqlcmd -S xxx.xx.xx.xx (ip)

    I was then able to restore my database. Thanks a lot for your prompt responses.

  • no probs. thanks for posting the full resolution.

    ---------------------------------------------------------------------

  • Does anyone here have rebuilt (not restore) system databases successfully? It is still a mystery for me.

    http://www.sqlservercentral.com/Forums/Topic378850-146-1.aspx

  • i have cluster 2005 64 bit . i need to move all system datbases to other directory. here are the steps i follow but it did not work.

    i took sql related service offline from cluster admin.

    i went to surface area configuration and change master databse files to new location

    started sql from command prompt with -m -T3806 flags (single user mode)

    i open QA and updated data and log file for all rest of three systyem datbaes by deattaching and . sattaching and alter datbase command.

    i run sp_helpdb for all db and confirmed new file location for all system datbase.

    i stopped sql server and the started in normal mode and confirm the same again.

    now stoope sql services again and brought sql services from cluster admin

    to my shock when i connected with cluster and chek path for system datbases it was changed back to old one for all system databases.

    yes i did copy all file including resouce dtabse to new location

    now the problem how i should have changed it that virtual server show my new location when i connect to virtual sql server

    any clue? please advise.

    :crazy: :alien:

    Umar Iqbal

  • A. Bring all the resources of the SQL Server Group in the cluster offline (except for Disk drive and IP Address).

    B. go to command prompt and start sql server using the following command sqlsrvr.exe -m (single user mode)

    C. Connect to sqlcmd using ip address of the sql service sqlcmd -S xxx.xx.xx.xx (ip)

    I was then able to restore my database. Thanks a lot for your prompt responses.

    When you say able to restore - did you use sqlcmd script to do a restore of the master or

    at this stage did you manage to get up SSMS and do it through GUI

    Just curious.

  • Hi Tracey,

    I am not sure if that was for me or not. But I am not restoring I am moving files. thats what I want to do to move files. I think that if I restore master in cluster with differnt file location it should work as alternative for moving master database file seperately. I use QA instead of sqlcmd

    :crazy: :alien:

    Umar Iqbal

  • So all your data files (master, model ) are DISK F

    You are moving these to DISK G

    So when you do this and bring up SQL it still looks at DISK F

    Did the DISK G get added as a dependancy on the cluster and i guess it is how do you

    change the path of Cluster to go to DISK G instead of DISKF

    Sorry never done wish i could be of more use.

  • Does this help.

    http://support.microsoft.com/kb/295732

    I am thinking you need to add the dependancy first..leaving other disk off line.

  • Thanks Tracey,

    yes thats exactly what I am doing . yes both drives are in dependecy and I can see them. I am changing the master data and log file location from surface area configuration. I tried chaging it from registry also. its a virtual server so When i start sql back from command prompt on node 1 i can see that master database files are on new disk but when i start it from cluster admin it show me old disk location. Somewhere. Somehow cluster admin is not taking the changin i am making when i start star sql from command prompt. i am not sure but i thnk if i take a back of master and restore it with different location "new one" and bring cluster up will it show me new location or it will do the same thing as move system file is doing.

    :crazy: :alien:

    Umar Iqbal

  • Hmmm it got to be in the Cluster part itself as it going to wrong disk - now i wonder where this is set up.

    Can i suggest you do not turn on the OLD DISK and see what happens.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply