Rebuild Master Database in SQL Server Without Backup

It has been observed that many SQL Server administrators, in fact majority of them, backup their user databases regularly, but turn a blind eye to the system databases. While most of them do not understand the value of these databases, some remain in their comfort zone owing to the years of no failure. Whatever be the reason, we will help you to be acquainted with the methods to restore the system databases.

Rebuild Master Database in SQL Server – Reasons

There are a number of reasons due to which, the master database needs to be rebuild. Some of them are:

• At times, user delete some crucial information like linked servers, logins, SQL Server configuration, and other user objects.
• The master database is not coming online and the admin does not have a backup of the master database.
• It can also happen that the master database has been corrupted due to various hardware and software failure and can no longer be used.
• The administrator might want to make a clone of the server or might want to restore the master database to a new instance.

How to Rebuild Master Database in SQL Server?

The following procedure will help you to rebuild master database in SQL Server. The entire process, which we will discuss in this write up, will be divided into three sections for the convenience of the users.

1. Pre-Rebuild Process

Since, only the master database is corrupt, therefore, in order to ensure the authenticity of other system database files, it gets necessary to take their backup. The files that need to be backed up are MSDB Data, MSDB Log, model & model Log In order to do so, follow the below mentioned steps:

• Open SQL Server Configuration Manager.
• From the left pane, select SQL Server Services option. This will list all the services that are currently running.
• Right click on the service and select Stop option for stopping all the services one by one.
• Exit SQL Server Configuration Manager.
• Browse to the location where all the system files of a particular SQL Server instance are stored.

C:\Program Files\Microsoft SQL Server\[INSTANCE NAME]\MSSQL\DATA

Note: Keep in mind this location, as this is where the master database files will be rebuild automatically.

• Cut all the healthy system files, save them in a new folder, and delete corrupted master mastlog, tempdb, & templog files.

2. Rebuilding Process

The steps that are followed for rebuilding are:

• In the command prompt, change the directory location of the server instance to the location where the SQL Server 2016 installation media.
• Open Command Prompt as Administrator.
• Since my installation media is stored in the F: Drive, change the directory location by entering f: and press Enter.
• The next step is to run the following command:
  
  Note: In case your account name contains a blank space, keep in mind to enclose the account name within quotes. Moreover, if you are specifying multiple accounts, separate them with a space.
• When all the system databases are rebuilt, it returns no message in the command prompt. In order to verify whether the process has been completed successfully, examine the summary.txt file. the location of this file is:
  C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs

3. Post-Rebuild Process

• The first step after rebuilding is to restart all the services, which were stopped earlier.
• If you have backups of model and MSDB databases, restore them.
• In case the backup is not present, simply replace the rebuilt MSDB and model files with the files that were backed up in the I. section. (This should be done after stopping the services).

Conclusion

In this blog, we have described the full procedure to rebuild Master Database in SQL Server in the scenario when the backup is not available. We believe that with its help you will be able to rebuild master database successfully.