次の方法で共有

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.

Bob Ward
Microsoft

logo-header-sql08-dg

Comments

  • Anonymous
    August 29, 2008
    PingBack from http://blog.a-foton.ru/2008/08/how-to-rebuild-system-databases-in-sql-server-2008/

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

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

  • Anonymous
    September 06, 2008
    The comment has been removed

  • Anonymous
    September 28, 2008
    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

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

  • Anonymous
    July 27, 2009
    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!

  • Anonymous
    February 27, 2010
    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

  • Anonymous
    April 14, 2010
    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?

  • Anonymous
    October 13, 2010
    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.

  • Anonymous
    October 14, 2010
    The comment has been removed

  • Anonymous
    March 14, 2011
    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 FilesMicrosoft SQL Server100Setup BootstrapLog20110314_133108Detail.txt What am I missing ? Regards, Tom

  • Anonymous
    August 08, 2011
    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!

  • Anonymous
    August 30, 2011
    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.

  • Anonymous
    December 01, 2011
    The comment has been removed

  • Anonymous
    April 28, 2014
    Using SQL Server Repair Toolbox is the best option to recover data from corrupt mdf file when backup is not available or very old. Software supports SQL server 2012, 2008 R2, 20008, 2008 & 2000 versions. For more information about the software, click on below link: www.sqlserver.repairtoolbox.com

  • Anonymous
    June 10, 2014
    I have try many method this i think its better. I am sure blog post help you... www.bubblews.com/.../3733174-how-to-recover-ms-sql-server-database

  • Anonymous
    June 22, 2014
    RecoveryFix for SQL database recovery tool to restore lost and damaged data od mdf file. This tool restore mdf and ndf files. Get more detail: www.en.repairsqlserver.net and also read this post recoverymaster.livejournal.com

  • Anonymous
    July 17, 2014
    Very nice easy method you have try for this type of error problem, but I have share some thing in the form for third party solution, here is free and useful software which work on corruption and deletion of MS SQL server database. Go to www.en.sqldatabaserepair.org

  • Anonymous
    October 17, 2014
    SQL database recovery software is provide an easy solution for damaged SQL database. This software is a perfect SQL database recovery software that helps to repair MDF and NDF files from SQL database. You can try this tool from here:-  recovery deleted files.com/sql- database- recovery- software

  • Anonymous
    March 02, 2016
    You can also take the help of MDF Recovery Tool to repair SQL Server Database. For more details about the software, visit: http://www.sqlmdfviewer.org/recovery/

  • Anonymous
    February 09, 2017
    what is the command for default instance