Rebuilding System Databases

Today’s post is something I thought I would share as there is an awful lot of incorrect commands I have seen (and some even tried) in the past around rebuilding system databases.  Now first off let me explain that rebuilding system databases is no straight forward task.  I would avoid it at all costs if the server is currently in use, but if you absolutely have to refer to this Microsoft article for additional help.

The Server in question was built by someone other than a Database Administrator who from the config clearly didn’t have much knowledge of SQL Server.  To help them out I made a whole host of recommendations but the biggest of which was to change the collation.  I hate collations, it isn’t their fault they do a mighty fine job but can cause me and most of the other DBA’s I know so many problems especially when referencing objects across databases, instances and servers.

The version of SQL Server was SQL Server 2008 R2 Standard edition and was installed with a named instance, the command I used is below;

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceNameHere /SQLSYSADMINACCOUNTS="domain\user" /SAPWD="strongsapassword" /SQLCOLLATION=LATIN1_GENERAL_CI_AS

Open command prompt as an administrator and browse to the directory below (on the volume on which SQL Server is installed);

Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2

Execute the above command obviously replacing;

• InstanceNameHere 

• domain

• user 

• strongsapassword 

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 on the volume on which SQL Server is installed in the below directory;

Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log

There should be a Summary.txt and a folder named in the format of yyyymmdd_hhmmss which will correspond to the date and time you ran setup.  This contains much more detail information about the setup process, if you run into any issues this is the first place I would start to look.

Cheers

Chris