I've come across a few interesting DR scenarios lately, (interesting in the sense that the resolution wasn't immediately obvious, and fortunately the answer didn't start out with "update your resume...")
The situation that comes to mind is a customer who was doing all the right things.
He had a disaster recovery plan. He had written scripts to implement the steps so there wouldn't be mistakes in the heat of the moment. HE WAS TESTING HIS PLAN (kudos) when he discovered the issue here:
His plan included backing up all user databases, as well as the system DBs, and then using a 3rd-party backup program to protect the system as a whole, providing one-button bare metal restore. Sounds like a great plan, right? What could go wrong?
The plan and the SQL scripts worked perfectly when he tested them out on a running system. Unfortunately, when he tested the end-to-end bare metal restore, SQL wouldn't come up. There was an error message about Master being corrupt which prevented the instance from starting.
No problem! We've got a backup of Master right here. Just restore it and we're on the road to recovery... Well, almost. The catch is that you have to have the instance running in single-user mode in order to restore master. That means it has to be running. In order for the instance to run, you need at least some viable copy of Master in place. Catch 22, right?
Fortunately, there is a solution. Setup provides a mechanism to recreate the system datbases without reinstalling all of the SQL binaries, but it's not obvious how to find it in BOL. The magic incantation turns out to be:
start /wait <CD or DVD Drive>setup.exe /qn INSTANCENAME=<Instance> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>
From there, you'll have a running instance with no user databases and all default settings. You can then restore master, msdb, model, and the rest of your databases and be on the road.
For our friend, there was an additional complication: He was running Express. The reason that this complicated matters is that Express is normally installed by downloading the compressed installer, which expands itself, runs the installation, and then cleans up the install files. Unfortunately, with the command above, MSI is going to look for the .MSI files in the location that SQL was installed from, which no longer exists, since it was cleaned up...
So, there are a couple of extra steps: First is to download the Express kit, and extract the contents to a local directory. This is done by saving the SQLEXPRESS.EXE file and NOT running it from the download prompt. Instead, after the download completes, manually run the program with the -x switch. i.e. D:ExpressdirSQLEXPRESS.EXE -x
This results in the setup files being extracted, but no other steps are taken. Now we need to instruct MSI to use these files and not the remembered location when rebuilding the databases. This is done by adding the REINSTALLMODE=vomus option to the command:
start /wait <CD or DVD Drive>setup.exe /qn INSTANCENAME=SQLExpress REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> REINSTALLMODE=vomus
And it's just that easy!
Next time I'll talk about a more disastrous situation and the desperate measures required to deal with it.
Microsoft
