Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to restore master database in a SQL Server 2005 Cluster Expand / Collapse
Author
Message
Posted Tuesday, April 15, 2008 12:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 22, 2011 4:07 PM
Points: 12, Visits: 175
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
Post #485250
Posted Tuesday, April 15, 2008 1:12 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:33 PM
Points: 764, Visits: 1,581
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 : )



Post #485257
Posted Tuesday, April 15, 2008 1:24 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:27 AM
Points: 5,895, Visits: 13,068
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)

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

Post #485263
Posted Tuesday, April 15, 2008 1:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 22, 2011 4:07 PM
Points: 12, Visits: 175
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?
Post #485275
Posted Tuesday, April 15, 2008 2:42 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:27 AM
Points: 5,895, Visits: 13,068
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


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

Post #485299
Posted Tuesday, April 15, 2008 3:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 22, 2011 4:07 PM
Points: 12, Visits: 175
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.
Post #485320
Posted Wednesday, April 16, 2008 3:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:27 AM
Points: 5,895, Visits: 13,068
no probs. thanks for posting the full resolution.

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

Post #485481
Posted Thursday, April 17, 2008 9:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 10, 2014 8:13 AM
Points: 1,278, Visits: 2,204
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
Post #486560
Posted Tuesday, September 30, 2008 11:11 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:30 PM
Points: 119, Visits: 346
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.




Umar Iqbal
Post #578854
Posted Wednesday, October 1, 2008 12:14 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:04 PM
Points: 724, Visits: 1,002
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.
Post #579299
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse