Here are a few avenues I would investigate. Don't do all of these (some of them are different techniques to accomplish the same purpose), but are worth considering:
1. Examine the SQL Error Log directly
Browse directly to the folder containing the SQL error logs and load the most recent
ERRORLOG into notepad to get more detail on why the SQL instance will not start. Perhaps you will find the problem isn't with the master database at all.
2. Try to start the instance in single-user mode
Here is a full list of startup options for SQL server, including
-m (single user mode) and
-f (minimal configuration mode). Other options allow you to specify the path for the master database, if that is the problem.
If you are able to get the instance started, follow the steps in the MSDN article you linked for restoring the master database, or this detailed walkthrough by Thomas LaRock.
If another application always grabs the single user connection before you can, first disable the SQL Agent so it isn't starting. Second, see the ideas on this question for using the
-m"Application Name" parameter to specify the application name.
master to another instance and copy its files
I've only found one other mention of this undocumented technique, but I used it successfully this past weekend, so it might be worth a try.
If you can't start the instance in single-user mode, but you have another SQL instance running the exact same release and build, try restoring the last known good master database backup from your dead server to the other instance:
- Restore as a different name, of course (
WITH MOVE so you don't overwrite
master on your good server
WITH NORECOVERY. Not sure this is necessary, but made me feel better that I knew the other server wasn't going to alter anything in the restored master
- Set it to offline:
ALTER DATABASE [master_please_god_let_this_work] SET OFFLINE
- Copy the restored MDF and LDF files from the good server to the dead server
- Rename the
mastlog.ldf files as necessary to replace the bad master files with your restored versions
- Cross your fingers and start the instance
- Optional: do a fresh restore of master on the revived server. Not sure this is necessary, since we were pretty careful not to change
4. Rebuild the system databases
If you don't have another instance running the same version, or if you're not comfortable with using the undocumented procedure listed in #3, or if you don't have backups of
master (why do you not have backups??), you can rebuild the SQL system databases from the original install disk:
Setup.exe /ACTION=REBUILDDATABASE /...
When this is complete, you can follow the steps linked previously to restore
master from your last good backup. You will also need to restore a recent backup of
msdb to keep all your jobs, job schedule, and job history.
5. Restore all USER databases to a new (or existing) SQL instance
If you've got another existing instance already running (proper SQL version, enough disk space), I would probably start database restores from the most recent backups while I'm working on the other troubleshooting steps above, just in case I need them.
If your new (or reinstalled) instance has access to the same disk, it is much faster to simply attach them as new databases:
CREATE DATABASE foo
ON (FILENAME = 'D:\data\foo.mdf'),
(FILENAME = 'D:\data\foo_log.ldf')
6. Re-do any changes to
Once you successfully restore
master (via any of the above techniques), you need to investigate any changes that might have been lost, if they were made after the backup you just restored:
- Security changes
- New databases (the files will still be on disk, just attach them)
- Server-wide settings
There is no magic way to find these, you'll have to go back to your own company's documentation trail for these kinds of changes, if you have one.