CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

How to Rebuild System Databases in SQL Server 2008

How to Rebuild System Databases in SQL Server 2008

Rate This
  • Comments 15

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.

Bob Ward
Microsoft

logo-header-sql08-dg
Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post
  • PingBack from http://blog.a-foton.ru/2008/08/how-to-rebuild-system-databases-in-sql-server-2008/

  • You might have read an earlier blog entry about my problems to remove a Data Collector (DC) information

  • You might have read an earlier blog entry about my problems to remove a Data Collector (DC) information

  • I am unable to start up my SQL SErver service with the message in the error log that I need to rebuild my master database. (Something about log scans being invalid) after mucking with a Crystal REports install. I suspect that the SQLServerTypes.MSI might have had somethign to do with this since it didn't behave well.

    I have run the command line as recommended above and am getting the message "The state of your SQL Server installation was not changed after the setup execution". I can't find any more details in teh summary or detail log file and just don't know what to try next.

    Any ideas?

    thanks

    julie lerman

  • i typed in the code you have at the top, but when i open the database the collation is still SQL_Latin1_General_CP1_CI_AS. i rebuilt the db with SQL_Latin1_General_CP1_CS_AS as the collation. i ahd a look at the summary file and it stated collation is SQL_Latin1_General_CP1_CS_AS??? i dont understand can someone explain please

    thanks

    helloise

  • (修正)タイトルが「msterDB」となっていたので「masterDB」に変更。(けろみおさん、ありがとうございます。) 「 Tibor Karaszi : Rebuild master in SQL

  • hello,

    My msdb on my production SQL Server 2008 was marked suspect. Is there any way at all to rebuild just msdb using the databases in the /Template Data directory? Can I potentially detach the current msdb and attach to the copy in this directory? If I do have to reubuild all my production system databases, what do I need to know when I am done?

    Thanks!

  • Thank you very much for suggesting we just copy the template files to SQL Server directory.

    It worked like a charm.

    I was looking for a simple way instead of reinstalling and calling up Technet support.

    Thanks very much

  • Are the system databases in the Templates directory updated when you install a service pack?  I'm looking at copying the system dbs into my data directory to fix a corrupt SQL 2008 SP1 instance. Will I need to apply the service pack again?

  • This worked perfectly for me.  All I had to do was recreate the logins and attach the databases after it was done.  I could have scripted the users before running setup but there were only a few so it only took a couple of minutes to get them back in.

  • Thanks - this worked a treat for me once I figured out I needed to execute the command from a cmd prompt that I had "Run as" Administrator.

  • I run the following command:

    setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=username@<domain_name>

    but it keeps failing with the error:

    Detailed results:

     Feature:                       Database Engine Services

     Status:                        Failed: see logs for details

     MSI status:                    Passed

     Configuration status:          Failed: see details below

     Configuration error code:      0xD3BEBD98@1211@1

     Configuration error description: The specified user 'username@<domain_name>' does not exist.

     Configuration log:             C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20110314_133108\Detail.txt

    What am I missing ?

    Regards,

    Tom

  • I have a 3 node cluster set up with one instance with the wrong collation. do i need to run anything from the 2 passive nodes? thanks!

  • We are planning on upgrading our WIndows 2003/SQL Server 2005 servers to Windows 2008 R2/SQL Server 2008. I would like to know if there is a way to migrate my scheduled SQL jobs contained within the msdb database from 2005 to 2008.

  • If you're using Sql Server 2008 R2, try running the setup.exe in \Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2 instead.

    You can tell there might be an issue/mismatch if the version in the command line isn't matched with the version of the running sql server -- for example the Management Studio says it's version 10.50.1600 whereas running the wrong setup.exe shows a version of 10.00.2731.00 .

Page 1 of 1 (15 items)