Rebuilding System Databases
System databases must be rebuilt to fix corruption problems in the master, model, msdb, or resource system databases or to modify the default server-level collation. This topic provides step-by-step instructions to rebuild system databases.
Before You Rebuild the System Databases
When the master, model, msdb, and tempdb system databases are rebuilt, the databases are dropped and re-created in their original location. If a new collation is specified in the rebuild statement, the system databases are created using that collation setting. Any user modifications to these databases are lost. For example, you may have user-defined objects in the master database, scheduled jobs in msdb, or changes to the default database settings in the model database.
Perform the following tasks before you rebuild the system databases to ensure that you can restore the system databases to their current settings.
Record all server-wide configuration values.
SELECT * FROM sys.configurations;
Record all service packs and hotfixes applied to the instance of SQL Server and the current collation. You must reapply these updates after rebuilding the system databases.
SELECT SERVERPROPERTY('ProductVersion ') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('ResourceVersion') AS ResourceVersion, SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime, SERVERPROPERTY('Collation') AS Collation;
Record the current location of all data and log files for the system databases. Rebuilding the system databases installs all system databases to their original location. If you have moved system database data or log files to a different location, you must move the files again.
SELECT name, physical_name AS current_file_location FROM sys.master_files WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));
Locate the current backup of the master, model, and msdb databases.
If the instance of SQL Server is configured as a replication Distributor, locate the current backup of the distribution database.
Ensure you have appropriate permissions to rebuild the system databases. To perform this operation, you must be a member of the sysadmin fixed server role. For more information, see Server-Level Roles.
Verify that copies of the master, model, msdb data and log template files exist on the local server. The default location for the template files is C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Templates. These files are used during the rebuild process and must be present for Setup to succeed. If they are missing, run the Repair feature of Setup, or manually copy the files from your installation media. To locate the 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.
Rebuild System Databases Procedure
The following procedure rebuilds the master, model, msdb, and tempdb system databases. You cannot specify the system databases to be rebuilt. For clustered instances, this procedure must be performed on the active node and the SQL Server resource in the corresponding cluster application group must be taken offline before performing the procedure.
This procedure does not rebuild the resource database. See the section, "Rebuild the resource Database Procedure" later in this topic.
To rebuild system databases for an instance of SQL Server:
Insert the SQL Server 2008 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release.
From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using the Windows Vista operating system with User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [/ SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
/QUIET or /Q
Specifies that Setup run without any user interface.
Specifies that Setup re-create the system databases.
Is the name of the instance of SQL Server. For the default instance, enter MSSQLSERVER. For a named instance, enter the name using the format server_name\instance_name.
Specifies the Windows groups or individual accounts to add to the sysadmin fixed server role. When specifying more than one account, separate the accounts with a blank space. For example, enter BUILTIN\Administrators MyDomain\MyUser. When you are specifying an account that contains a blank space within the account name, enclose the account in double quotation marks. For example, enter "NT AUTHORITY\SYSTEM".
[ /SAPWD=StrongPassword ]
Specifies the password for the SQL Server sa account. This parameter is required if the instance uses Mixed Authentication (SQL Server and Windows Authentication) mode.Security NoteThe sa account is a well-known SQL Server account and it is often targeted by malicious users. It is very important that you use a strong password for the sa login.
Do not specify this parameter for Windows Authentication mode.
[ /SQLCOLLATION=CollationName ]
Specifies a new server-level collation. This parameter is optional. When not specified, the current collation of the server is used.ImportantChanging the server-level collation does not change the collation of existing user databases. All newly created user databases will use the new collation by default.
For more information, see Setting and Changing the Server Collation.
When Setup has completed rebuilding the system databases, it returns to the command prompt with no messages. Examine the Summary.txt log file to verify that the process completed successfully. This file is located at C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Logs.
After rebuilding the database you may need to perform the following additional tasks:
Apply the latest service pack and any applicable hot fixes.
Restore your most recent full backups of the master, model, and msdb databases. For more information, see Considerations for Backing Up and Restoring System Databases.
If you have changed the server collation, do not restore the system databases. Doing so will replace the new collation with the previous collation setting.
If a backup is not available or if the restored backup is not current, re-create any missing entries. For example, re-create all missing entries for your user databases, backup devices, SQL Server logins, end points, and so on. The best way to re-create entries is to run the original scripts that created them.
We recommend that you secure your scripts to prevent their being altered by unauthorized by individuals.
If the instance of SQL Server is configured as a replication Distributor, you must restore the distribution database. For more information, see Backing Up and Restoring Replicated Databases.
Move the system databases to the locations you recorded previously. For more information, see Moving System Databases.
Verify the server-wide configuration values match the values you recorded previously.
Rebuild the resource Database Procedure
The following procedure rebuilds the resource system database. When you rebuild the resource database, all service packs and hot fixes are lost, and therefore must be reapplied.
To rebuild the resource system database:
Launch the SQL Server Setup program (setup.exe) from SQL Server 2008 distribution media.
In the left navigation area, click Maintenance, and then click Repair.
Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.
On the Select Instance page, select the instance to repair, and then click Next.
The repair rules will run to validate the operation. To continue, click Next.
From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.
Troubleshooting Rebuild Errors
Syntax and other run-time errors are displayed in the command prompt window. Examine the Setup statement for the following syntax errors:
Missing slash mark (/) in front of each parameter name.
Missing equal sign (=) between the parameter name and the parameter value.
Presence of blank spaces between the parameter name and the equal sign.
Presence of commas (,) or other characters that are not specified in the syntax.
After the rebuild operation is complete, examine the SQL Server logs for any errors. The default log location is C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Logs. To locate the log file that contains the results of the rebuild process, change directories to the Logs folder from a command prompt, and then run findstr /s RebuildDatabase summary*.*. This search will point you to any log files that contain the results of rebuilding system databases. Open the log files and examine them for relevant error messages.
Revised the cluster information to state that the SQL Server resource in the corresponding cluster application group must be taken offline before performing the procedure.
Added a requirement in the definition of /SQLSYSADMINACCOUNTS to enclose account names in double quotation marks when the name contains a blank space.