April 15, 2008 at 12:51 pm
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
April 15, 2008 at 1:12 pm
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 : )
April 15, 2008 at 1:24 pm
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)
---------------------------------------------------------------------
April 15, 2008 at 1:42 pm
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?
April 15, 2008 at 2:42 pm
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
---------------------------------------------------------------------
April 15, 2008 at 3:23 pm
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.
April 16, 2008 at 3:03 am
no probs. thanks for posting the full resolution.
---------------------------------------------------------------------
April 17, 2008 at 9:40 am
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
September 30, 2008 at 11:11 pm
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
October 1, 2008 at 12:14 pm
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.
October 1, 2008 at 12:40 pm
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
October 1, 2008 at 12:54 pm
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.
October 1, 2008 at 1:03 pm
Does this help.
http://support.microsoft.com/kb/295732
I am thinking you need to add the dependancy first..leaving other disk off line.
October 1, 2008 at 1:04 pm
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
October 1, 2008 at 1:06 pm
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