Monday, 2 April 2012

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;


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;


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.



1 comment:

  1. I've just done this on a cluster for the first time. The SQL Server Resource needs to be taken offline before you run the command. This is just the resource, not the resource group or services and applications if you are on Windows 2008!