Rebuilding Master Database in SQL Server (SQL Server 2000 \ 2005 \ 2008 and above)

MS SQL Server requires master & resource databases to start the instance. Without these critical databases SQL Server will not start. Master database corruption is a rare event. However it is important to always include master database in the backup strategy to ensure there is always a latest copy / backup of the most critical database available so that it can be used in case of disaster recovery.

Rebuild Master Database in SQL Server 2000:

In this tip, I would like to show you how to rebuild the master/system database in SQL Server 2000. Recently, I faced a problem related to the master database. My instance was unable to start due to master file corruption.

How do you know if your master database is corrupt?
Before we discuss how to recover and rebuild your master database in the event of a failure, we need to look at how you can tell if it's corrupt. 
Let's pretend that your company had a power surge and your SQL Server rebooted. Upon reboot, SQL Server would not start. If you check the error log (Figure A), you'll see that the master database is either corrupt or missing. Now that you know what message to look for, let’s see how to recover a master database.Figure A

Recover your master database
Step 1: Find the Rebuild Wizard(Rebuildm.exe) in the Server
Location in SQL2000:

C:\Program Files\Microsoft SQL Server\80\Tools\BINN\Rebuildm.exe

Step 2: Start by double-clicking Rebuildm.exe to shown in Figure B.Figure B

On this screen, you can specify the collation settings of your database server and the location of your data files during your original install. To make the latter easier and faster, copy the x86 directory from the SQL CD to your hard drive and point to the local copy. Once you have verified all of this information, click Rebuild. You'll then be prompted to confirm the operation, as shown in Figure C.Figure C

Click Yes.
  

Once the process is completed, you'll see a message telling you that the rebuild was successful. You now have a brand new master database and are ready to restore your master database.


Step 3: First, Start SQL Server in single-user mode by opening up a command prompt
RUN=>CMD

Note:from the C: \Program Files\Microsoft SQL Server\MSSQL\BINN\directory C:\Program Files\Microsoft SQL Server\MSSQL\Binn> sqlservr.exe -c -m This will start SQL Server and the output will be directed to the console. Here's a sample of what will be displayed on your console screen (it is also in the errorlog): 2001-04-29 15:44:08.43 spid3 SQL Server started in single user mode. Updates allowed to system catalogs. 2001-04-29 15:44:08.46 spid3 Starting up database 'master'.

The results are shown in Figure D.

Figure D

After you start SQL Server in single-user mode, you can restore your master database from a backup. You can restore it using either the Query Analyzer or SQL Enterprise Manager. If you're using Query Analyzer, run the query shown Below.
Step 4: To restore the master database, you will need to execute the following steps:

If you're using Enterprise Manager, right-click on the master database, choose All Tasks | Restore Database, and browse to where your device is located, as shown in Figure F. Click OK twice, and you have successfully restored your master database.Figure F

Once you've restored your master database, exit single-user mode and restart SQL Server in normal operation mode.

If for some reason your restore operation does not work, you can try an alternative method. Simply rebuild the master database and attach all of your databases that reside in the data directory. You can attach the databases using Enterprise Manager or Query Analyzer. In Enterprise Manager, right-click on Databases and choose Attach Database, as shown in Figure G. In Query Analyzer, the sample script in Listing A shows how to attach your databases.

1. Add the backup device:

   sp_addumpdevice 'disk', 'master_backup',  'c:\tmp\sql_backup\master_backup.dmp' g0

2. Restart SQL Server. Once master has been restored, SQL Server will automatically shut down. Bring it up just as you normally would.

=================================================================================

Rebuild Master Database in SQL Server 2005:

When system databases are rebuilt, all database objects and data in master, model and msdb system databases are removed. Rebuilding the master database installs all system databases to their initial location.

Step 1:To rebuild the system databases you need to run the setup command from the Command prompt and follow the following procedure:

1. Click Start, click Run, type cmd, and then click OK.
2. Run the following command to rebuild the system databases:

start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=Instance_Name REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=StrongPassword

For example:

start /wait D:\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=p@ssw0rd

OR

START /WAIT <media drive and path>\setup.exe /qn INSTANCENAME=<INSTANCE NAME> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<STRONG SA PASSWORD>

Note: <INSTANCE NAME> is just the SQL Server INSTANCE name or “MSSQLSERVER” if the default instance.

Setup will take few minutes to complete. Once complete check <SQL SERVER>\DATA folder and verify the databases are present. Also, review the SUMMARY.TXT file for any issues. The file is located in C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG folder.

Sample Summary.txt file:

Step 2:Test the server by starting the instance in single-user mode and connect using SQLCMD

C:\>net start MSSQLSERVER /m 
[note: use MSSQL$<instance name> for a named instance]

C:>\sqlcmd -S <server name>

Log out of the server and shut down the instance
C:\>net stop MSSQLSERVER

Step 3:Restore MASTER Database

Restore MASTER database using your latest SQL Server full database backup. This MUST be run in single user mode. 

In the previous step we logged out and shutdown the instance. Start the SQL instance in single-user mode via command prompt, connect using SQLCMD and restore master database. See steps below.

C:\>net start MSSQLSERVER /m 

C:>\sqlcmd -S <server name>

1 - restore database master from disk='C:\TheDBAVault\Backup\SystemDB\master.bak' with replace
2 - GO

SQL Server instance will automatically shutdown after restore. With the successful restoration, the instance is in the configuration as of the last MASTER database backup. If there were any instance-wide changes after the last backup (any user created database or logins created, will have to be recreated manually, users databases will have to be reattached)

Complete the system database restoration process. Restore MODEL and MSDB databases from the last good backup. MSDB is important because it contains ALL job scheduling information and history, Maintenance Plan data and history, backup and restore history, and DBMAIL settings among other things. It can also contain SSIS package stores.Step 4: What if I've applied a hotfix or update for SQL Server?

As with SQL Server 2005, if for any reason you rebuild system databases or repair the resource database, you should apply your latest update even if you restore backups of system databases. Theoretically you could restore system databases that were backed up after applying your latest updates but verifying they all sync up can be tricky so I recommend you apply your latest updates.

Notes:

• Put the media (CD or DVD) on the same location from which you originally installed the instance of SQL Server 2005.
• The /qn switch makes all Setup messages, including error messages, to be written to Setup log files. The main log is located at %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt
• The /qb switch can be used instead of the /qn switch. /qb allows the display basic setup dialog boxes and all error messages.
• SAPWD is needed to specify a new password for the System Administrator account. Microsoft recommends de use of a strong password.
• The parameter INSTANCENAME is used to specify the instance name. Use MSSQLSERVER for the deafult instance.

=================================================================================

Rebuild Master Database in SQL Server 2008 / Rebuild Master Database in SQL Server 2008R2:

Before You Begin
Limitations and Restrictions

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.

Prerequisites

Perform the following tasks before you rebuild the system databases to ensure that you can restore the system databases to their current settings.

1. Record all server-wide configuration values.
   SELECT * FROM sys.configurations;
   

2. 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;
   

3. 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'));
   

4. Locate the current backup of the master, model, and msdb databases.
5. If the instance of SQL Server is configured as a replication Distributor, locate the current backup of the distribution database.
6. 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.
7. 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\MSSQL11.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 or x64) and then navigate to setup\sql_engine_core_inst_msi\Pfiles\SqlServr\MSSQL.X\MSSQL\Binn\Templates.

Error: Recently, one of my instances was corrupted and I was unable to restart SQL Server. I did the following steps to get it back. First I checked the SQL Server ERRORLOG. Please go through the following steps.

Step 1: Check the error log where it is showing master was corrupted.

Step 2: Rebuild system databases from command prompt. Go to the following path and run setup as follows:

Path:

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

Syntax:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName] OR SETUP  /ACTION=REBUILDDATABASE  /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMIN

Eg:

SETUP  /ACTION=REBUILDDATABASE  /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=SERVERNAME\USERNAME /SAPWD=P@ssw0rd /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Step 3: Once the above command runs successfully, check in the summary.txt file as follows and look at the Requested action.

Step 4: Start SQL Server service.

Step 5: Connect to the instance. But you cannot see any user defined databases. The rebuilding process has created a fresh master database so there are no other databases and all previous configuration values are lost.

Step 6: Now we can restore the master database to get the previous configuration settings. Go to command prompt and run server in single user mode as follows:

Step 7: Connect to the instance and take new query to restore master database. Once we restore master database, then we can get all the previous configuration values including user defined databases.

Step 8: Click on Connect button and connect to the instance as follows:

Step 9: Restore master database as follows

Step 10: Restart SQL Server instance in multi user mode and connect to SQL Server Management Studio. We can see all user databases as follows:

Troubleshooting

Sometimes, the rebuild process may fail if there is problem with files present in Binn\Templates folder. Once you check summary.txt file, let's say it is saying one message as follows:

Configuration error description: The file C:\Program Files\Microsoft SQL Server \
MSSQL10_50.MSSQLSERVER \MSSQL\Binn\Templates\master.mdf is missingIn the above scenario, we have to copy file (master.mdf) from SQL Server dump or DVD into Binn\Templates folder and retry.