Restoring master DB from a backup in sql server 2005

We recently had some issues with the RAID getting corrupted on one of our production SQL Servers after some repeated power outages caused by our giant UPS failing. As a result we needed to rebuild the RAID from scratch and restore from a full drive backup from Net Backup. Unfortunately Net Backup didn't get all the files on the data drive, most importantly (at least for the purpose of this article!), master.mdf was missing. Fortunately we had a backup of master, but SQL Server won't start without master.mdf, and master.bak doesn't really give you that.
Yes, I realize there are other articles out there about this, but I didn't find any of them to be exactly what I needed, so someone else might find use in mine.
1. So, first step is to rebuild the master DB to a default version, so we can get SQL Server started and restore the master.bak file we made:
a. Run the following command: "M:\ENGLISH\SQL2005\DEVELOPER\SQL Server x64\Servers\setup.exe" /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=
i. Other articles online will tell you to use "start /wait" at the beginning of that command, but I had 0 success with that
ii. M:\ENGLISH\SQL2005\DEVELOPER\SQL Server x64\Servers\ is the path for installing a 64bit Developer edition from the MSDN CD. Yours may differ, but it's CRUCIAL that it be the same as the original path. To find the original path:
1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\
2. Then look through all of the InstallProperties\DisplayName until you find Microsoft SQL Server 2005 (I started at the above location in the registry then searched till I found the right one)
a. Make sure it's the actual server instance, not one of the other various components
3. Then use the value in the InstallSource key
b. Once you've gotten the above command to run successfully, you'll be getting a fresh copy of your system DBs in the original data location. If you want to monitor setup.exe while it performs this activity use your Task Manager.
c. Once this has completed, confirm that SQL Server works by starting it from the SQL Server Configuration Manager
2. Now we need to restore Master from our backup:
a. Start the SQL Server in single-user mode
i. From a DOS prompt: \sqlservr.exe -m
ii. -m is the switch for Single User Mode.
iii. On the server I'm restoring = E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
iv. It won't appear like the process is complete in the normal sense when the SQL Server is running, if you're like me and are used to seeing the command prompt again after you run something from the command line, but once the messages stop scrolling in the DOS window the SQL Server instance should be started.
v. Confirm the above by looking for sqlserver.exe in Task Manager
b. Restore the backup
i. Personally I scripted this all via Management Studio after I rebuilt the "vanilla" master.mdf, then restarted the SQL Server instance in single-user mode. That way I also had the query window open so I could just run the statement twice (first time it reestablishes connection to the SQL Server) and run it
ii. Here's the command for our install:
RESTORE DATABASE [master] FROM DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master\master_backup_200907082100.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
iii. Once this has executed, it will stop the SQL Server instance, giving the following messages:
11 percent processed.
20 percent processed.
31 percent processed.
40 percent processed.
51 percent processed.
60 percent processed.
70 percent processed.
81 percent processed.
90 percent processed.
100 percent processed.
Processed 432 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
c. Start the SQL Server service normally via the SQL Server Configuration Manager
i. If you see an error like this:
1. Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\MSSQLSERVER ]. Error: 0x5
2. Try rebooting the SQL Server as it may not have gracefully released control of the ports SQL Server needs the last time it stopped.
d. Restore any other system databases first
i. Most likely you'll need msdb, possibly model
e. Restore any other DBs you may need
f. Ensure that you don't need to reinstall any service packs and patches

Troubleshooting Researching or Tracking Down Failed SQL Server Logins

I found such a dearth of usable information on this when I just searched for it on Google, that I thought I would write a post detailing the process I figured out.
For starters, I'm getting "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: xxx.xxx.xxx.xxx]" repeatedly on one of my SQL Servers. It's logged in a variety of places, namely the Application Log in Event Viewer, with the following info:
Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 6/12/2009
Time: 10:17:21 AM
User: NT AUTHORITY\ANONYMOUS LOGON
Computer:
Description:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: XXX.XXX.XXX.XXX]

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:


And in the SQL Server Log, with this info:
Date 6/12/2009 9:45:21 AM
Log SQL Server (Current - 6/12/2009 9:45:00 AM)

Source Logon

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: XXX.XXX.XXX.XXX]

And a paired row stating:
Date 6/12/2009 9:45:21 AM
Log SQL Server (Current - 6/12/2009 9:45:00 AM)

Source Logon

Message
Error: 18456, Severity: 14, State: 11.


While there are a couple of different things you can glean from these error messages, such as why it's failing (do a lookup based on the State. In this particular case State 11 means that the login is good, but the connection failed). You can see the state descriptions below:

State	Error description
1	Account is locked out
2	User id is not valid
5	User id is not valid
7	The login being used is disabled
8	Incorrect password
9	Invalid password
11-12	Login valid but server access failed
16	Login valid, but not permissioned to use the target database
18	Password expired
27	Initial database could not be found
38	Login valid but database unavailable (or login not permissioned)

But that's not really what I wanted. I wanted the login attempts to stop. So I started profiler with a connection to the server with the failed logins. I started with a blank template, and added a single event:

Use Audit Login Failed under Security Audit, with all the columns.
Then look for the entry for the failed login:

It's kind of hard to see in the screenshot, but basically the important info you're looking for is in the ApplicationName, ClientProcessID, HostName, LoginName and/or SessionLoginName, and TextData.
In my particular case there wasn't any data in the ApplicationName, which could go a long way toward helping you figure it out. And I've seen suggestions online to use profiler to find this. Hopefully you'll be able to solve your problem using this column. The next column is what was really key for me:
ClientProcessID
Use this to go to the Task Manager on the client machine (found in the TextData column). If you only have an IP, use nbtstat -a or whois from the command line to find the machine name. Add the PID to the columns displayed in the Task Manager by choosing View->Select Columns from the menu:

Then check PID from the list that appears:

Then sort the Task Manager list by PID by clicking on it, and scroll down to the PID that matches the ClientProcessID column from Profiler:

In my case it was RpScheduler. This will lead you right to the offending service or client app. In my case I just disabled the service on the client machine because I don't need it. In other instances you may need to change the login info for the service, or the client application. This is really about finding the app, not so much about troubleshooting what you find.

Hopefully this helps someone, and feel free to comment with suggestions!