Rebuilding the master Database

The master database can be rebuilt using the Rebuild Master utility if:

• A current backup of master is not available.
  
• The backup cannot be restored because an instance of Microsoft® SQL Server™ cannot start due to severe damage to master.

When master has been rebuilt, a current backup of master can be restored or the user databases, backup devices, SQL Server logins, and so on can be re-created using SQL Server Enterprise Manager or the original scripts used to create those entries.

Important  The Rebuild Master utility rebuilds master completely. Because the msdb and model system databases are rebuilt as well, it will normally be necessary to restore backups of those databases.

The general steps required to rebuild master completely if no backup is available are:

• Run the Rebuild Master utility to rebuild the system databases.
  Important  The compact disc or shared network directory containing the SQL Server installation software is required to rebuild the master database.
• Re-create any necessary backup devices.
  
• Reimplement security operations.
  
• Restore msdb if necessary.
  
• Restore model if necessary.
  
• Restore distribution if necessary.
  
• Restore or attach user databases if necessary.

When master has been re-created and any changes have been reapplied, back up master immediately.

To rebuild the master database

To rebuild the system databases you need to run the setup command from the Command prompt and follow the following procedure:

1. Click Start, click Run, type cmd, and then click OK.
2. Run the following command to rebuild the system databases:

start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=Instance_Name REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=StrongPassword

For example:

start /wait D:\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=p@ssw0rd

You don't need your DVD anymore!

A new feature different from previous versions of SQL Server is that the system databases files we use to rebuild the current system databases do not come from the original installation media. So where do they come from? The come from the installation folder on your local computer in BINN\templates. On my machine where I installed SQL Server 2008 RTM on a clean machine with the default instance, this full path was at:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Templates

In this directory you will find master, model, and msdb database and log files that were copied from your installation source as part of setup.

So the logic in rebuilding the system databases is to get these files and copy them into your DATA directory to obtain your new system databases. This is a very nice enhancements for rebuilding system databases because you are not required to have your DVD or original install media.

Link : http://msdn.microsoft.com/en-us/library/aa213831%28v=sql.80%29.aspx