How do you restore the master database on a SQL Server cluster?
I have learned a lot from your articles on SQL Server clustering. We are moving towards a clustering environment very soon, but there is one problem that I can’t solve before we go live. Restoring a Master database in a clustering environment is a big problem. This is especially important in a disaster scenario where the Master database goes corrupt and we need to restore from a previous backup of Master database. To restore a Master database, this requires the server to be started in a single user mode, and that’s where the problem comes in, since Cluster Administrator is the only place to do any restarts of the server, etc. Surprisingly I couldn’t find anything on this subject on the net, and the DELL engineers we asked were clueless as well.
You would think that an important topic like this would be widely covered, but since it is not, here’s the relatively easy and straight-forward solution for SQL Server 7.0 and SQL Server 2000. Just follow these steps.
Identify which node SQL Server is currently running on, such as node1.
From Cluster Administrator, take the SQL Server resources off-line.
Go to the node running SQL Server (see step 1) and start it from the command prompt using:
sqlservr.exe -c -m
Restore the master database using Query Analyzer or Enterprise Manager.
Quit SQL Server from the command prompt.
Using Cluster Administrator, restart the SQL Server services.
Now you are good to go.