Now we've been working on restoring the system databases first (of course) to the new instance and came up across this problem. Our production server has 2 drives - k: for data files and l: for log files. Our test box for the restores is set up with only a c: drive.
all this is entirely possible, you do need to know the clustered system layout beforehand. This is something you should have documented already, detailing service accounts\groups, IP addresses, network names, disks, etc!
If you want to recreate the cluster you re deploy a new cluster using same names, IP, patch level, etc then restore matching master, model and msdb using the following;
1/ Ensure you have to hand or know the location of valid and matching SQL server backups for the following databases,
Note: The TEMPDB is recreated from the Model database each time the SQL Server instance starts.
Do not attempt to restore disparate backups as this could cause at minimum, system collation issues. The backups must exist on a shared drive visible to the SQL Server instance.
2/ RESTORING MASTER DATABASE
Take offline all clustered SQL Server services for the instance you are maintaining, this is done via Windows “Cluster Administrator”.
3/ START SQL SERVER IN SINGLE USER MODE
The SQL Server instance needs to be started in single user mode to be able to restore the master database. This is done by executing SQLSERVR.EXE at the command prompt within the SQL Server instance Binn directory with a set of command line parameters. You may find the executable path by opening Windows services and viewing the properties of the SQL Server service you are maintaining.
Open a command prompt and execute SQLSERVR.EXE as shown below. (For a default instance, omit the instance name entry on the command line).
sqlservr -c -m -sinst1
Pressing return will start the SQL Server instance,
4/ Now open a new command window leaving the existing one active (do not minimise). The new command window will be used to restore the master database via the SQLCMD prompt as shown below,
sqlcmd -SMyServer\inst1 -E
This is a combination of the virtual network name and the named instance. Had I created a default instance it would be named simply MyServer.
Remember the network name is simply a virtual computername!!
To restore the database, use the following syntax (where ? is the shared drive letter, !!!!! are the paths and ****** is the filename). It is important to specify the REPLACE parameter to overwrite the existing database!
RESTORE DATABASE MASTER FROM DISK = '?:\!!!!!\!!!!!!\*******.bak' WITH REPLACE
After pressing return you are required to enter your next command. To execute the SQL statement above, type “GO” followed by return.
Restoring the Master database should automatically exit the running SQL Server instance in the first command window. If it doesn’t, stop the server by clicking into the command window and hitting CTRL-C.
5/ BRINGING THE INSTANCE BACK ONLINE
After restoring the Master database and closing the SQLSERVR.EXE command window you must now go into Cluster Administrator and bring the SQL Server service back online. Do not bring the SQL Server Agent or the SQL Backup Agent services online if you intend to restore the Model and MSDB databases.
6/ RESTORING THE MODEL AND THE MSDB DATABASES
Restore these databases as usual via SQL Server Management Studio. Ensure the SQL Server Agent and the SQLBackup Agent services are offline in “Cluster Administrator” first.
After restoring the Model and MSDB databases you must restart the SQL Server instance using Cluster Administrator.
7/ WHAT ABOUT TEMPDB?
The TEMPDB is re created from the Model database each time the SQL Server instance is started. You do not backup or restore this database!
-----------------------------------------------------------------------------------------------------------"Ya can't make an omelette without breaking just a few eggs"