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 «««45678

Moving the SQL 2005 System Databases Expand / Collapse
Author
Message
Posted Thursday, February 25, 2010 6:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2012 7:38 AM
Points: 8, Visits: 40
I saw this article thinking that it would help or have a hint on how to get my situation to work. I commented here, because this script would not work for me if I used it. I understand what needs to take place with the resource database for 2005. Whether or not Vince's script works was never my question.
I again have gotten the web page to work which this script is based on if you look at it! Now, if you look at the document on web page:
http://msdn.microsoft.com/en-us/library/ms345408%28SQL.90%29.aspx

ALL, steps work for ALL databases, at least for me, when you are performing this on a NON-clustered Sql Server 2005 server. The web page nor this chain never mention any difference for whether or not this can be performed on a cluster.

Now, for me, this step fails when performed on a Sql Server 2005 64-bit Enterprise Edition SP3 Cluster:

...
7.) Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.

* For the default (MSSQLSERVER) instance, run the following command.

NET START MSSQLSERVER /f /T3608

8.) Using sqlcmd commands or SQL Server Management Studio, run the following statements. Change the FILENAME path to match the new location of the master data file. Do not change the name of the database or the file names.

I use this statement for step 8:
D:\>D:\MSSQL\90\Tools\binn\sqlcmd.exe -sMSSQLSERVER
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.


I have started a chain in the administration forum. I post it as a question more than a comment. If you have a solution to how to get this to work on a cluster, then please comment there. To this point no one has commented to that post.
Thanks
Post #872645
Posted Thursday, February 25, 2010 6:36 AM
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, November 13, 2014 11:08 AM
Points: 716, Visits: 467
Mike,

Sorry, never have had occasion to work with a clustered SQL Server, so I'm not one who can help.

Vince



Post #872669
Posted Tuesday, March 22, 2011 5:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 2, 2014 1:46 PM
Points: 4, Visits: 48
Excellent script! Worked great but I had to execute it twice. One important tip: insert the "pause" DOS command before any "exit" commands, otherwise your command window will close before you get a chance to read enough of the output. I had opened a cmd window and then executed it inside of that, thinking that when it finished it woud leave me in the original cmd window, but unfortunately it closed that as well when it exited and I had nothing to look at.

Before the DOS window closed, I also got an error that my account couldn't access the database while in single user mode. Then I realized what else was accessing the server: don't forget to close SQL Management Studio if you have it open, lol.

I commented everything out that didn't refer to the mssqlsystemresource database and ran it again (after closing SQL Mgmt Studio) and it completed successfully.

Thanks again for a wonderful script! But please resubit a version that doesn't close the window when it exits (or at least inseart the pause commands).

Steve
Post #1082403
Posted Friday, September 2, 2011 7:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 6:06 AM
Points: 68, Visits: 123
I thought I saw a post stating that someone had issues on a 64-bit system, but I don't recall seeing a post addressing it. Has anyone tried the script on a 64-bit system? Did you have any problems?

Thanks,
Bob
Post #1169221
Posted Tuesday, September 18, 2012 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 29, 2012 10:42 AM
Points: 1, Visits: 10
Hopefully someone still watches this. I ran this and everything looked great and moved as expected but the services did not start. When you attempt to start the services for this instance, I get the following in the logs:

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\mssql\MSSQL$WO\MSSQL.7\MSSQL\DATA\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.

That path is the old path and mssqlsystemresource.ldf and .mdf files are both in the new path as expected. Why is it still looking for it in the old path? I need to get his instance back up and running and don't know what I can do now.

Thanks.
Post #1360779
Posted Tuesday, September 18, 2012 4:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 11, 2013 1:09 PM
Points: 80, Visits: 580
Look into moving system databases and it depends upon the version of SQL Server.

http://tinyurl.com/9atbagg




Post #1361049
« Prev Topic | Next Topic »

Add to briefcase «««45678

Permissions Expand / Collapse