Many SQL Server administrators regularly maintain their backup but sometimes do not understand database values, mostly to remain in a comfort zone and to avoid any database failure, but accidents happen. To overcome certain actions, I want to share some methods to restore the system databases or to rebuild it.
Let's learn some reasons behind rebuilding the Master Database in SQL Server.
Deletion of some crucial information like logins, SQL Server configuration, linked servers, and other user objects.
- The admin does not have a backup of the master database.
- The master database is corrupted due to hardware/software failure.
- The administrator might want to restore the master database into a new instance.
- The administrator might want to clone the database.
Now, how to rebuild Master Database in SQL Server?
Follow the procedure as explained to rebuild the master database in SQL Database Server. The complete process is discussed here which is divided into three sections for the convenience of the users.
Pre-Rebuild Process
If the master database is corrupt, in order to ensure the authenticity of other system database files, it becomes necessary to do a backup. The files that you need to backup are MSDB Data, MSDB Log, model & model Log. In order to do so, follow the below-mentioned steps.
- Open SQL Server Configuration Manager.
- Select SQL Server Services from the left pane of the Manager.
- Right-click on the service to stop the services one by one.
- Exit SQL Manager.
- Go to the location where all the system files of a particular SQL Server instance are saved; like C:\Program Files\Microsoft SQL Server\[INSTANCE NAME]\MSSQL\DATA
Note
Remember location, as this will be the location where our rebuilt master database will be stored automatically.
- Cut all the healthy system files, save them in a new folder, and delete corrupted master mastlog, tempdb, & templog files.
The Rebuilding Process
Follow the steps,
- Open Command Prompt.
- Change the directory location; where the SQL Server 2016 is installed.
- Now run the following command.
When all the system databases are rebuilt, it will return no message in the command prompt. To verify whether the process was completed successfully, examine the summary.txt file at the following location.
C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs
Post-Rebuild Process
Now, restart all the services which were stopped earlier.
If you have the backup of the model and MSDB databases, then restore them.
In case there is no backup, simply replace the files with the backed up files in the first section.