I’ve recently attended a class regarding SQL 2005 Administration and thought I would post a helpful bit of info that helped us get through a problem in class. Since even the instructor had problems with this, I figured it would be worth sharing.

If your Master Database in SQL becomes corrupt it is possible that you will not be able to launch SQL successfully. When this happens you will need to replace the bad copy of the Master Database with a new copy in order to get SQL running again. Below are the steps in order to get this working. I will point out some problem area’s or gotcha’s along the way.

• You will need the SQL 2005 install CD first of all. Once you have it you can try copying the files to your desktop or just leaving it in the CD tray while you type the following command:start /wait setup.exe /qn INSTANCENAME=”MSSQLSERVER” REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=”XXXX”

  Where XXXX is the name of the password.
  INSTANCENAME=”MSSQLSERVER” for the default instance of SQL 2005
  or
  INSTANCENAME=”MSSQL$INSTANCENAME” for a named instance of SQL 2005.
  
  **NOTE**
  If you have problems running the command above, please make sure that you are not using an eval or developer CD. Also, you may have to use the same installation point as when you originally installed SQL. For example, if you installed SQL from a remote server, you may have to copy the files back to that remote server and run them from the same path, usually something like \\SERVER\SHARE\SQL 2005 . There is a way to go into the registry and change a key that contains the path, however, that in turn breaks another key and starts a chain reaction of keys that need to be fixed. The easiest way is to just use the original path.

 

• Once the command has been ran successfully the rebuild process will start and will complete within about 5 minutes. You can verify that the DB has been recreated by browsing to the Data folder that holds the .mdf and .ldf files and sorting by the modify date.

• When the rebuild is complete, you may now connect to the SQL server using SSMS (SQL Server Management Studio). You may notice that when you expand the database tree that your user created DB’s are missing. That is because the master DB held the info regarding what databases were present. Don’t worry, they are still there. You just need to re-attach the databases from their location most likely in the same Data folder that the master DB resides in. Once the DB’s have been re-attached, the new Master DB has record of them and they will now show up in the DB tree.

  Alternatively, if your Master DB was corrupt but not bad enough to keep you from starting SQL, there is an easier way to fix your problem. This is assuming that you have recently backed up your Master DB and have access to the backup file.

 

• To restore the Master DB from backup, you must first stop SQL Server. Then type the following command to restart SQL in single user mode which is required to restore the Master DB:NET START MSSQLSERVER /c /m

  **NOTE**
  MSSQLSERVER is for the default instance. A named instance would be MSSQL$INSTANCENAME

  Then follow normal restore procedure to restore you backup of the good Master DB. Once the restore has completed, you may notice that SQL has stopped. You just need to restart it normally and you should be good to go!

Hope this has been helpful for you guys!