How to Rebuild System Databases in SQL Server 2008
In SQL Server 2005, we introduced a different method than in previous versions to rebuild system databases (affectionately known as "rebuild master"). You were required to use the setup.exe program with command line switches.
This is no different in SQL Server 2008 but 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 link) but this information is not correct so I'll outline how to do this here in this blog post. We will also get our documentation updated to reflect this information.
The syntax for using setup.exe to rebuild the system databases is as follows:
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=<instance name> /SQLSYSADMINACCOUNTS=<accounts>
where
<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
If you have SQL configured for Mixed Authentication Mode use the same syntax except you must also provide the /SAPWD parameter to specify the SA password. If you don't, you will get an error.
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.
What if I've applied a hotfix or update for SQL Server?
As with SQL Server 2005, if for any reason you rebuild system databases or repair the resource database, you should apply your latest update even if you restore backups of system databases. Theoretically you could restore system databases that were backed up after applying your latest updates but verifying they all sync up can be tricky so I recommend you apply your latest updates.
What about msdb. Can I use instmdb.sql to rebuild it?
At the current time for SQL Server 2008 using instmsdb.sql is not something that is supported or has been tested. We are looking into ways to rebuild msdb without having to rebuild all system databases, but for now you must use setup as I have described above to rebuild msdb. You can still restore msdb from a backup but if you don't have a valid backup, you will need to rebuild the system databases. I will post any updates to this topic to this blog as I discover them.
There may be some other interesting aspects to rebuilding system databases that will come up as we support customers for SQL Server 2008. I'll update this blog post with these findings.