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 Monday, October 6, 2008 9:09 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 968, Visits: 971
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
Post #581477
Posted Tuesday, October 7, 2008 8:02 AM


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
Hi there,
Thanks. I will try that and let you know.




Umar Iqbal
Post #581819
Posted Sunday, October 12, 2008 7:19 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, July 31, 2014 7:04 PM
Points: 724, Visits: 1,002
How did you get on - be good to get a finally write up .... just incase have to do it some day.
Post #584543
Posted Sunday, November 23, 2008 3:32 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:47 PM
Points: 587, Visits: 1,998
Hi Deepali,

could you pls explian me how to connect to sqlcmd after starting the sql server instance in single user mode

Thanks
Post #607230
Posted Sunday, November 23, 2008 5:07 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
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.
Post #607242
Posted Thursday, June 11, 2009 1:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 2,121, Visits: 1,448
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
Post #733298
Posted Friday, March 12, 2010 10:56 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 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:
[url=http://msdn.microsoft.com/en-us/library/ms345408%28SQL.90%29.aspx][/url]

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?
Post #881980
Posted Monday, June 17, 2013 8:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 5:01 AM
Points: 16, Visits: 51
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;
Post #1464155
Posted Tuesday, June 18, 2013 3:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 1,320, Visits: 2,572
Please note, 3 years old post.


Sujeet Singh
Post #1464539
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse