October 1, 2008 at 1:20 pm
Something in here may help.
http://choosing-a-blog-url-sucks.blogspot.com/2007/11/sql-server-2005-fail-over-cluster.html
Gosh now im curious.
Got to get my team to build me a test cluster so you can do this kind of thing every now and again.
Let me know what you find out.
October 1, 2008 at 1:21 pm
IF the disk where you are moving the Master database is iunder SQL Server Dependency and you have properly given path in surface configration with -d and -l parameter and removed the old parameters , then bring the SQl server offine from Cluster adminstartor and moved both mdf and ldf for master database to new location and then bring the Server online , there should be no problem as i usually follow the above procedure for cluster and haven't see any problem .
October 2, 2008 at 7:24 am
Thanks Tracey and Nary,
Ok this is just did . Just to make sure its not disk thing issue. I tried moving it from one directory to another directory in the same disk instead of other disk. But I get the same issue.
Again the problem is that in sql 2005 64 bit cluster chanigng the master database location file directory to other directory on same disk or other disk not working. you can see it new path when you login to node and start sql in single user mode after changing the parameter eith in surface area configuration or registry but when you turn off this instace and start from cluster admin. you see old path when you run sp_helpdb against master.
Tracey,
i dd not try removing files from original location i just copy them to new location. I am sure that if i did remove them from original location server would start when i start it from cluster admin since it looks for master files in old direction.
:crazy: :alien:
Umar Iqbal
October 2, 2008 at 8:16 am
check this article.
http://www.sqlservercentral.com/articles/Administration/63568/
It explains in details how to move db files.
October 2, 2008 at 12:45 pm
Hi Info_s,
Moving database is not problem . but in 2005 64 bit cluster it give error. please read the initial thread
:crazy: :alien:
Umar Iqbal
October 6, 2008 at 9:09 pm
Umar,
What you'll want to do is this:
1. Remove the dependencies on the disks that you moving between.
2. Remove the cluster resources from Cluster Administrator completely.
3. Re-create the cluster resource that you are moving to.
4. Bring the "new" resource online (resource created in step 4).
5. Make SQL Server dependent on that resource.
6. Bring SQL back online.
I've had issues in the past where I had a cluster resource created, I added a new disk and tried to re-use a letter and use the existing cluster resource that was attached to that letter, and it didn't like that. After that experience, I always completely remove the cluster resources and re-create them after my work within the "Disk Management" section of Computer Management.
The reason you can start SQL within the command line utility is because the server must be seeing the correct drives, but the cluster isn't. It can't hurt to re-create the resources.
Best of luck,
SK
October 7, 2008 at 8:02 am
Hi there,
Thanks. I will try that and let you know.
:crazy: :alien:
Umar Iqbal
October 12, 2008 at 7:19 am
How did you get on - be good to get a finally write up .... just incase have to do it some day.
November 23, 2008 at 3:32 pm
Hi Deepali,
could you pls explian me how to connect to sqlcmd after starting the sql server instance in single user mode
Thanks
November 23, 2008 at 5:07 pm
I take you got it working ....how did you finally do it - this be good to know especially on the cluster incase you have to move the system files in a disaster recovery exericse.
June 11, 2009 at 1:27 pm
Hi Umar,
As this is quite an old thread I expect you've got your cluster issue sorted now (or given up ;-)), but I've recently been doing the same thing.
I didn't need to take the drastic steps that SK recommends - there's no need - but you need to make sure that when you start SQL Server in master-recovery mode (using the /f and /T3068 parameters), you run an ALTER DATABASE command to logically MOVE the mssqlsystemresource data and log files to the new location and then physically move the files.
You should then be able be able to change the startup parameters in SQL Server Configuration manager and restart the SQL Server Service via Cluster Administrator. If that doesn't work, copy the master and mssqlsystemresource data and log files back to their original location, start SQL Server (via Cluster Admin), make the changes again, shut down SQL Server (again via Cluster Admin) and restart. The new startup parameters should then be picked up.
This problem is caused by Registry entries on the cluster getting out of synch with the startup parameters. I can testify that it can be a complete pain!
Regards
Lempster
March 12, 2010 at 10:56 am
I have this same issue, can't figure out why Sql Server does not have this documented.
2 node Sql Server 2005 64 bit enterprise.
I have moved all databases without issue using:
Now I could not connect using this method for a cluster setup. It works moving master and resource when not clustered. I was getting connection errors when attempting to move the resource using sqlcmd an the net start service part. So I found this and other links in this forum. I seems to be the closest to reality that I can find.
So here is my steps that I will attempt for this cluster to move master and resource databases:
1)From Cluster Administrator: stop the cluster service for the failover node
2)From Cluster Administrator: take the SQL Server resource offline
OS Move master data and log files to new location below.
3)From SQL Server Configuration Manager: add the startup parameters -f;-T3608
Original configuration manager startup parameters:
-dE:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eE:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lE:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
To:
-dM:\MSSQL\MSSQL.1\MSSQL\Data\master.mdf;-eM:\MSSQL\MSSQL.1\MSSQL\LOG\ERRORLOG;-lM:\MSSQL\MSSQL.1\MSSQL\Data\mastlog.ldf;-f;-T3608
4)From Cluster Administraror: bring the SQL Server resource online again
5) Run from command line (This is where it failed before, crossing fingers):
sqlcmd -sMSSQLSERVER
6) From sqlcmd:
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'M:\MSSQL\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'M:\MSSQL\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf');
GO
7) OS move the files in step 6 to new location
8) From sqlcmd:
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
go
exit
9) From SQL Server Configuration Manager: remove the startup parameters -f;-T3608
-dM:\MSSQL\MSSQL.1\MSSQL\Data\master.mdf;-eM:\MSSQL\MSSQL.1\MSSQL\LOG\ERRORLOG;-lM:\MSSQL\MSSQL.1\MSSQL\Data\mastlog.ldf
10) From Cluster Administrator: take the SQL Server resource offline
11) From Cluster Administrator: start the cluster service for the failover node
12) From Cluster Administraror: bring the SQL Server resource online again.
Anyone who got there setup working, what do you think?
June 17, 2013 at 8:01 am
Follow below steps for restoring master database in SQL Server 2008 R2 failover cluster
1.Put SQL Server in Single user mode in SQL Server Configuration manager
(Put -m in SQL Server startup parameters and Offline SQL Server Agent)
2.Restart SQL Services from Failover cluster manager and make SQL Server Group online
3.Open CMD with Administrator privileges
4.Type NET START MSSQL$name(named instance) in command prompt or type
NET START MSSQLSERVER (Default instance)
5.Type SQLCMD -S instance name (named instance) in command prompt or type
SQLCMD (Default instance)
6.Run Restore command RESTORE DATABASE master FROM DISK = '’ WITH REPLACE;
June 18, 2013 at 3:54 am
Please note, 3 years old post.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply