SQL Server rebuild System Database (to: http://msdn.microsoft.com/zh-cn/library/dd207003.aspx)

You must regenerate the system database to repair the damage problem in the master, model, MSDB, or resource system database or modify the default server-level sorting rules. This topic provides step-by-step instructions on how to regenerate a system database.

Before the system database is regenerated

When the master, model, MSDB, and tempdb System databases are regenerated, these databases are deleted and re-created at their original locations. If a new sorting rule is specified in the regenerate statement, This sorting rule is used to create a system database. All modifications made to these databases will be lost. For example, changes to the default database settings in the user-defined objects in the master database, scheduled jobs in the MSDB, or the model database will be lost.

Execute the following tasks before re-generating the system database to ensure that the system database can be restored to their current settings.

1. Record the configuration values for all server ranges.
   Copy

   SELECT * FROM sys.configurations;

2. Record all service packs and patches applied to the SQL server instance and the current sorting rule. These updates must be re-applied after the system database is regenerated.
   Copy

   SELECTSERVERPROPERTY('ProductVersion ') AS ProductVersion,SERVERPROPERTY('ProductLevel') AS ProductLevel,SERVERPROPERTY('ResourceVersion') AS ResourceVersion,SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,SERVERPROPERTY('Collation') AS Collation;

3. Record the current location of all data files and log files in the system database. Rebuild the system database to install all the system databases to its original location. If you have moved the system database data files or log files to another location, you must move these files again.
   Copy

   SELECT name, physical_name AS current_file_locationFROM sys.master_filesWHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

4. Find the current backup of the master, model, and MSDB databases.
5. If you configure an SQL server instance as a replication distributor, find the current backup of the distribution database.
6. Make sure that you have the permission to regenerate the system database. RequiredSysAdminMembers of the fixed server role can perform this operation. For more information, see server-level roles.
7. Verify that there are copies of the master, model, and MSDB data template files and log template files on the local server. The default location of the template file is c: \ Program Files \ Microsoft SQL Server \ mssql10_50.mssqlserver \ MSSQL \ binn \ templates. These files are used during the re-generation process and must exist if you want to make the installation successful. If these files are missing, run the "Repair" function of the installer or manually copy them from the installation media. To find these files on the installation media, navigate to the appropriate platform directory (x86, x64, or IA64), and then navigate To Setup \ SQL _engine_core_inst_msi \ pfiles \ sqlservr \ MSSQL. X \ MSSQL \ binn \ templates.

System database re-Generation Process

The following process re-generates the master, model, MSDB, and tempdb System databases. You cannot specify the system databases to be regenerated. For a cluster instance, this process must be performed on the active node. This process does not regenerate the resource database. See the "Resource Database regeneration process" section after this topic.

Regenerate the SQL Server 2008 instance system database:

1. Insert the installation media of SQL Server 2008 to the disk drive, or change the directory to the location of the setup.exe file from the command prompt on the local server. The default location on the server is c: \ Program Files \ Microsoft SQL Server \ 100 \ setup Bootstrap \ release.

2. In the Command Prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter parentheses. The privilege to run the installer when Windows Vista is used and User Account Control (UAC) is enabled. The command prompt must be run as an administrator.

   Setup/quiet/Action = rebuilddatabase/InstanceName = InstanceName/sqlsysadminaccounts = accounts/[sapwd = strongpassword] [/sqlcollation = collationname]

   Parameter Name	Description
   /Quiet or/Q	Specify to run the installer without any user interface.
   /Action = rebuilddatabase	The specified installer will recreate the system database.
   /InstanceName =Instance name	Is the name of the SQL server instance. For default instances, enter MSSQLServer. For named instances, useSERVER_NAME \ instance_nameFormat.
   /Sqlsysadminaccounts =Account	Specify to addSysAdminA Windows group or a single account in a fixed server role. When multiple accounts are specified, separate them with spaces. For example, enterBuiltin \ administrators mydomain \ myuser. When you specify an account with spaces in its name, double quotation marks are used to enclose the account. For example, enterNt authority \ System.
   [/Sapwd =Strong passwords]	Specify SQL ServerSAThe password of the account. This parameter is required if the instance uses the Hybrid Authentication (SQL Server and Windows Authentication) mode. Security description:SAAn account is a well-known SQL Server account and is often targeted by malicious users. Therefore,SAStrong passwords are very important for logon users.   Do not specify this parameter for Windows Authentication mode.
   [/Sqlcollation =Sorting rule name]	Specify a new server-level sorting rule. This parameter is optional. If not specified, the current sorting rule of the server is used. Note: Changing server-level sorting rules does not change the sorting rules of existing user databases. By default, all newly created user databases use new sorting rules.   For more information, see set and change server sorting rules.

3. After the system database is regenerated, the installer returns to the command prompt without displaying any messages. Check the summary.txt log file to verify that the re-generation process is successful. This file is located in c: \ Program Files \ Microsoft SQL Server \ 100 \ setup Bootstrap \ logs.

Re-generated task

After the database is regenerated, you may need to perform the following additional tasks:

• Apply the latest service pack and any applicable patches.
• Restore the latest full backup of the master, model, and MSDB databases. For more information, see precautions for backing up and restoring system databases.

  Important:
  If you have changed the server sorting rules, do not restore the system database. Otherwise, the new sorting rule is replaced with the previous sorting rule settings.

   

  If no backup is available or the restored backup is not the latest, re-create all the missing entries. For example, re-create a user database, backup device, SQL Server login name, endpoint, and other missing entries. The best way to recreate these entries is to run the original script for creating them.

Security description:
We recommend that you protect scripts to prevent unauthorized users from modifying the scripts.

 

• If you configure an SQL server instance as a replication distributor, you must restore the distribution database. For more information, see back up and restore a Replicated Database.
• Move the system database to the location where you previously recorded it. For more information, see mobile system databases.
• Verify that the server-range configuration value matches the value you recorded previously.

Resource Database re-Generation Process

The following process will regenerate the Resource System database. When the resource database is regenerated, all service packs and patches will be lost and must be re-applied.

Regenerate the Resource System database:

1. Start the SQL Server installer (setup.exe) from the SQL Server 2008 distribution medium ).

2. In the left-side Navigation Pane, click"Maintenance"And then click"Repair".

3. Installer support rules and file routines will run to ensure that necessary components are installed on your system and that the computer can verify the rules through the installer. Click"OK"Or"Installation"To continue the operation.

4. On the "select instance" Page, select the instance to be repaired, and then click"Next".

5. The repair rule is run to verify the repair operation. To continue, click"Next".

6. In"Prepare for Repair"Page, click"Repair". The "finish" page indicates that the repair operation has been completed.

Resend error troubleshooting

Syntax and other runtime errors are displayed in the Command Prompt window. Check whether the following syntax errors exist in the setup statement:

• The slash mark (/) is missing before each parameter name (/).
• The equal sign (=) is missing between the parameter name and the parameter value ).
• There is a space between the parameter name and the equal sign.
• There are comma (,) or other characters not specified in the syntax.

After the regenerate operation is complete, check whether any errors exist in the SQL server log. The default log location is c: \ Program Files \ Microsoft SQL Server \ 100 \ setup Bootstrap \ logs. To find a log file that contains the result of the re-generation process, change the directory from the command prompt to the "logs" folder, and then runfindstr /s RebuildDatabase summary*.*. This search will guide you to find all log files that contain the system database re-generated results. Open the log file and check for any error messages.