//
you're reading...
SQL Server

How to Rebuild System Databases in SQL Server

In SQL Server 2005, a new method of rebuilding the system databases was introduced known as “rebuild master”. This involved running the setup.exe program with command line switches.

SQL Server 2008 utilizes the same process to rebuild the system databases however 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 ms144259).  I’ll outline how to use this command line option in this blog post. 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:

Step 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 100Setup BootStrapRelease directory. 2. Run setup.exe with the following syntax from a Windows command prompt:

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

For Mixed Authentication Mode use the same syntax except you must also provide the /SAPWD parameter to specify the SA password.

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.

Step 2: When setup has completed rebuilding the system databases, it will return to the command prompt window. Any messages related to syntax error or parameter problems wll be displayed. If no error messages are present then examine the “Summary” log file to verify it was completely successful.

Step 3: The summary logs are located in the directory where logs are stored for setup (100setup bootstraplogs), open up a file called Summary.txt. This file represents the most recent summary of any execution of setup. Otherwise If you run setup for any other reason after rebuilding the databases you will have to look for a folder inside the logs directory that matches the datetime when you ran setup to rebuild the system databases. You can always utilize the 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

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:                    2009-01-29 08:29:10

End time:                      2009-01-29 08:32:25

Requested action:              RebuildDatabase

No DVD required!

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 but are located on installation folder on your local computer in BINNtemplates.

C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBinnTemplates

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:                    2009-01-29 08:30:42

End time:                      2009-01-29 08:31:32

Requested action:              RebuildDatabase

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 FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBinnTemplatesmaster.mdf is missing.

Configuration log:             C:Program FilesMicrosoft SQL Server100Setup BootstrapLog20090129_083014Detail.txt

To fix do either of the following:

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:

setupsql_engine_core_inst_msiPFilesSqlServrMSSQL.XMSSQLBinnTemplate

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

Comments are closed.

%d bloggers like this: