setup.exe
/QUIET
/ACTION=REBUILDDATABASE
/INSTANCENAME=instance_name
/SQLSYSADMINACCOUNTS= accounts
[/SAPWD=password]
[/SQLCOLLATION=collation_name]
Here are the details about how to use this syntax and how it works:
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 100\Setup BootStrap\Release directory. So on my machine, I changed directory to C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release.
2. Run setup.exe with the following syntax from a Windows command prompt:
If you have SQL configured for Windows Authentication Mode use this syntax:
setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=
where
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.
3. When setup has completed rebuilding the system databases, it will return to the command prompt with no messages (It always first prints out the version). If you have any syntax problems or issues with parameters you will see these errors in the command window. If you don't see any errors, then you will need to examine the "Summary" log file to verify it was completely successful.
4. If you immediately go to the directory where logs are stored for setup (100\setup bootstrap\logs), you can open up a file called Summary.txt. This file represents the most recent summary of any execution of setup. If you run setup for any other reason after rebuilding the databases before you look at the summary.txt file you will have to look for a folder inside the logs directory that matches the datetime when you run setup to rebuild the system databases. This may not be something that is simple to do if you have run setup several times so a tip here is to use 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 because this is the string printed in the Summary setup log file when using setup for this purpose. Here is an example "top portion" of a Summary setup log file when rebuilding system databases was successful:
Overall summary:
Final result:
Passed
Exit code (Decimal): 0
Exit message: Passed
Start time: 2008-08-29 08:09:10
End time: 2008-08-29 08:10:25
Requested action: RebuildDatabase
Here are a few interesting notes to consider about how this feature works:
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.
However, If these files are missing, then setup will fail and your summary log may look something like this:
Overall summary: 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: 2008-08-29 08:30:42 End time: 2008-08-29 08:31:32 Requested action: RebuildDatabase
Later down in the summary log file you will see the details like the following example:
Detailed results: 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 Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf is missing. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20080829_083014\Detail.txt
The details show the problem. master.mdf is missing from the templates directory. The way to resolve this is to either
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:
setup\sql_engine_core_inst_msi\PFiles\SqlServr\MSSQL.X\MSSQL\Binn\Template
Once you have copied the file into the templates directory, re-run setup with the syntax I've described above.
What about the Resource Database?
This feature does not attempt to restore the resource database files mssqlsystemresource.mdf or mssqlsystemresource.ldf. In fact, these files are considered part of the software so are now installed in the BINN directory. Therefore, to rebuild these database files you would need to run Repair from the Installation Center.
