Rebuilding the Master

Posted: March 4, 2009 in Sqlserver

If the instance can’t start due to a damaged master database, it can be rebuilt. However, this will revert all the system databases to their original state, requiring additional steps to restore each of the system databases. Additionally, all of the user databases will need to be restored to allow the rebuilt master database to learn of them.

There are two instances when the master must be rebuilt:

§  A current backup of the master database is not available.

§  The SQL Server instance won’t start due to a corrupt master.

 

 

Note 

 

The Rebuildm.exe program has been discontinued. Notice this does not read “deprecated.” If you have experience with a previous edition of SQL, you may have used rebuildm, but it is not available in SQL Server 2005.

Once the master database is rebuilt, it can be restored from a recent master database backup.

The tool used to rebuild the master database in SQL Server 2005 is setup.exe. Setup.exe is used to rebuild, verify, and repair an SQL Server instance, and to rebuild system databases. The most common use is to rebuild a corrupt master database, since a corrupt master will prevent the instance from starting. To rebuild the master, setup is run from the command line.

A corrupt master database is a little challenging to identify. The error log records what’s going on when an instance starts and is a good place to start.

 Remember, the error log is located in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Log folder.

The following listing is a partial listing of an error log of an instance that won’t start.

 

2007-03-21 03:06:31.29 Server    Microsoft SQL Server 2005 – 9.00.3050.00

(Intel X86)

      Mar 2 2007 20:01:28

      Copyright (c) 1988-2005 Microsoft Corporation

      Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-03-21 03:06:31.29 Server      (c) 2005 Microsoft Corporation.

2007-03-21 03:06:31.29 Server      All rights reserved.

2007-03-21 03:06:31.29 Server      Server process ID is 156.

2007-03-21 03:06:31.29 Server      Authentication mode is MIXED.

2007-03-21 03:06:31.29 Server          -d C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\DATA\master.mdf

2007-03-21 03:06:31.29 Server          -e C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\LOG\ERRORLOG

2007-03-21 03:06:31.29 Server          -l C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2007-03-21 03:06:31.32 Server Error: 17113, Severity: 16, State: 1.

2007-03-21 03:06:31.32 Server Error 2 (The system cannot find the file

specified) occurred while opening file ‘C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSLQ\DATA\master.mdf to obtain configuration . . .

. . .

This error log shows we have a problem with the master database preventing the instance from starting. It must be rebuilt.

 

Note:  If the master is corrupt, preventing an SQL Server instance from starting, use the setup.exe program from the command line to rebuild it.

Single User Mode

Under certain circumstances you may be able to recover a damaged master database or other system database by starting SQL Server in single user mode. Consider single user mode an advanced tool that shouldn’t be used lightly.

 

Note:  Single user mode should not be used for routine maintenance. It’s an advanced tool that should only be used in extreme situations. Single user mode effectively locks out all users from the database.

To access single user mode, both the SQL Server service and the SQL Server Agent service must be stopped. With both those services stopped, you can open a command-line prompt and change the directory to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BINN. This path is for the first instance installed (MSSQL.1). If you were trying to access a different instance, you’d change the path accordingly.

At this point enter the following command to launch SQL Server in single user mode:

sqlservr – m

Following is a partial listing of the error log showing that the server was started in single user mode:

2007-03-22 06:22:30.50 Server   Microsoft SQL Server 2005 – 9.00.3050.00

(Intel X86)

      Mar 2 2007 20:01:28

      Copyright (c) 1988-2005 Microsoft Corporation

      Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-03-22 06:22:30.50 Server      (c) 2005 Microsoft Corporation.

2007-03-22 06:22:30.50 Server      All rights reserved.

2007-03-22 06:22:30.50 Server      Server process ID is 6024.

2007-03-22 06:22:30.50 Server      Authentication mode is MIXED.

2007-03-22 06:22:30.50 Server      Logging SQL Server messages in file

‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG’.

2007-03-22 06:22:30.50 Server      This instance of SQL Server last reported

using a process ID of 2792 at 3/22/2007 6:22:08 AM (local) 3/22/2007 10:22:08

AM (UTC). This is an informational message only; no user action is required.

2007-03-22 06:22:30.50 Server      Registry startup parameters:

2007-03-22 06:22:30.50 Server         -d C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\DATA\master.mdf

2007-03-22 06:22:30.50 Server         -e C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\LOG\ERRORLOG

2007-03-22 06:22:30.50 Server         -l C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2007-03-22 06:22:30.50 Server      Command Line Startup Parameters:

2007-03-22 06:22:30.50 Server         -m

2007-03-22 06:22:30.53 Server      SQL Server is starting at normal priority

base (=7). This is an informational message only. No user action is required.

The complete listing is displayed at the command prompt. Each of the databases in the server instance will be started. It will take some time for the startup to complete.

At this point you can use the setup.exe program to rebuild the master database. Open another command prompt, and identify the path where the setup command is located. In my system, the installation CD is in the D: drive, and the setup command is located in the Servers directory. The basic syntax is

 

Start /wait d:\servers\setup.exe /qn instancename = instanceName reinstall =

SQL_Engine REBUILDDATABASE=1 sapwd = sapassword

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s