Recently, the default instance of Sql Server 2005 on my laptop refused to start. Here is the message in the Windows event log:

The SQL Server (MSSQLSERVER) service terminated with service-specific error 3417 (0xD59).

Here is the message in the Sql Server error log:

The log scan number (253:272:1) passed to log scan in database ‘master’ is not valid. This error may indicate data
corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Time to rebuild the master database, I told myself. I put in the Sql Server installation media, and ran the following in DOS:

setup.exe /qb INSTANCENAME=mssqlserver REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=ReplaceThisWithMySuperStrongPassword

This underscores the importance of backing up your system databases, especially master and msdb. I actually don’t have them backed up, since this is my test box. But master database is the gut of your Sql Server system, it holds logins, meta data about other databases, among other important information, so it is very important you back it up.

To restore master from a backup after a rebuild, you need to add the -m switch as the startup parameter, so server starts in single user mode. Add it through Sql Server Configuration Manager: double click on Sql Server service, go to Advanced tab, then go to Startup Parameters. At the very end of Startup Parameters, add a semicolon, a dash, and the letter m. Restart and you are ready to restore your master from a backup. When you finish restoring master, remember to take away the -m switch.

If you have backups for your msdb database, which contains important system information such as jobs, SSIS packages, Sql mail, etc, you need to restore it also. You will need to stop Sql Server Agent in order to restore msdb

I am still baffled as to why master was failed / corrupted on my laptop.