SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving the SQL 2005 System Databases


Moving the SQL 2005 System Databases

Author
Message
GiantMetFan
GiantMetFan
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 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
vince.iacoboni@db.com
vince.iacoboni@db.com
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1411 Visits: 554
Mike,

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

Vince



Steve1014
Steve1014
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 55
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
RLB
RLB
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 140
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
kmichaels
kmichaels
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
webooth
webooth
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 580
Look into moving system databases and it depends upon the version of SQL Server.

http://tinyurl.com/9atbagg



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search