In SQL Server 2005, a new method of rebuilding the system databases was introduced known as “rebuild master”. This involved running the setup.exe program with command line switches.
SQL Server 2008 utilizes the same process to rebuild the system databases however the command line switches have changed some and the process behind the scenes to rebuild the system databases (master, model, and msdb) is also a bit different.
Currently the SQL Server 2008 Books Online only mention an option for setup called /REBUILDDATABSES (See this ms144259). I’ll outline how to use this command line option in this blog post. The syntax for using setup.exe to rebuild the system databases is as follows:
Here are the details about how to use this syntax:
Step 1. Find setup.exe either from your original media or the “local” setup.exe as found in the directory where you have installed SQL Server in the 100Setup BootStrapRelease directory. 2. Run setup.exe with the following syntax from a Windows command prompt:
For Windows Authentication Mode use this syntax:
setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=<instance name> /SQLSYSADMINACCOUNTS=<accounts>
<instance name> is either the name of your named instance or MSSQLSERVER for the default instance
<accounts> are Windows groups or individual accounts to provision as sysadmin
For Mixed Authentication Mode use the same syntax except you must also provide the /SAPWD parameter to specify the SA password.
If you want to rebuild the system databases with a different collation than what you used to install SQL Server, you would need to supply the /SQLCOLLATION parameter. If you don’t supply this parameter, then SQL Server will rebuild the system databases with the collation you selected when you installed SQL Server.
Step 2: When setup has completed rebuilding the system databases, it will return to the command prompt window. Any messages related to syntax error or parameter problems wll be displayed. If no error messages are present then examine the “Summary” log file to verify it was completely successful.
Step 3: The summary logs are located in the directory where logs are stored for setup (100setup bootstraplogs), open up a file called Summary.txt. This file represents the most recent summary of any execution of setup. Otherwise If you run setup for any other reason after rebuilding the databases you will have to look for a folder inside the logs directory that matches the datetime when you ran setup to rebuild the system databases. You can always utilize the findstr.exe from the command prompt like the following:
findstr /s RebuildDatabase summary*.*
This search will point you to any summary files that are for rebuilding system databases
Here is an example “top portion” of a Summary setup log file when rebuilding system databases was successful:
Final result: Passed
Exit code (Decimal): 0
Exit message: Passed
Start time: 2009-01-29 08:29:10
End time: 2009-01-29 08:32:25
Requested action: RebuildDatabase
No DVD required!
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 but are located on installation folder on your local computer in BINNtemplates.
C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBinnTemplates
However, If these files are missing, then setup will fail and your summary log may look something like this:
Final result: Failed: see details below
Exit code (Decimal): -2068643839
Exit facility code: 1203
Exit error code: 1
Exit message: Failed: see details below
Start time: 2009-01-29 08:30:42
End time: 2009-01-29 08:31:32
Requested action: RebuildDatabase
Feature: Database Engine Services
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x8C77B9A8@1306@25
Configuration error description: The file C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBinnTemplatesmaster.mdf is missing.
Configuration log: C:Program FilesMicrosoft SQL Server100Setup BootstrapLog20090129_083014Detail.txt
To fix do either of the following:
1) Use the Repair feature of Setup (Available from the Maintenance option of the SQL Server Installation Center installed your machine)
2) Manually copy the necessary file(s) yourself. On your media source find the directory of your platform (x86, x64, or ia64). Then go to the following directory: