Summary: Master database is the primary database that stores system information. In this article, we will discuss how to rebuild and restore a master database in SQL Server. We’ll also mention a third-party SQL repair software that can repair and restore corrupt SQL database in just a few steps.
Contents
In SQL Server, there are system databases, like master database, msdb, and tempdb that are used by SQL Server for internal use. The master database is the main database in SQL Server. It stores system information, like logins, linked servers, databases, etc. If this database fails, the SQL Server will completely fail.
Below, we?ll see how to repair, restore, and rebuild the master database in SQL Server.
Repair the Database using Setup Installer
The SQL Server installer has an option to repair the database. This option will allow you to repair the SQL Server database, including the master database. To use this option, follow these steps:
- Run the setup.exe in your SQL Server installer folder.
Go to Maintenance and select the Repair option.
Select the instance to repair and press Next.
Finally, press the Repair button.
Restore the Master Database
If you have a backup of the database, you can use it to restore the database. However, the master database is a special database and it cannot be restored as other databases. You need to first run the SQL Service in single-user mode. To do this, open the SQL Server Configuration Manager.
Go to SQL Server Services and select the Service instance that you want to work with. Right-click the instance and click Stop.
Right-click the instance again and select Properties.
Go to the Startup Parameters, specify the -m parameter, and press the Add button.
Here, -m is used to set the instance to single-user mode.
Optionally, you can use the -f start option to set the instance with minimal configurations.
If you prefer to set the SQL Server instance in single-user mode using the command line, run the following commands.
NET STOP MSSQLSERVERNET START MSSQLSERVER /mThe first line is to stop the SQL Server. Use the correct SQL Server instance name. Here, MSSQLServer is used as an example.
The second line will start the SQL Server instance in single-user mode.
Once the database is in single-user mode, you can connect with the SQLCMD and restore the database by using the following command:
SQLCMD -S MSSQLServer -E -d masterHere, MSSQLSERVER is the server?s name, -E is to connect with Windows Authentication, -S is to provide the instance name, and -d is to specify the database to connect.
Finally, run the below restore command to restore the database.
restore database master from disk = 'c:\stellarbackups\master.bak' with replace;Rebuild the Master Database
There is an option to rebuild the database using the command line. In the SQL Server installer, look for the setup.exe and run the following command:
setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS=sa /SAPWD= MyP983?#3 /SQLCOLLATION=Latin1_General_CI_ASHere, MSSQLServer is the instance name, SQLSYSADMINACCOUNTS is the system administrator user, SAPWD is the password, and CollationName is the collation used.
An Alternative Solution to Repair and Restore SQL Database
Another solution is to use a third-party SQL database repair tool, such as Stellar Repair for MS SQL. This software can repair corrupt SQL server databases. The MDF and NDF files are used to store the information. This software restores these files if they are corrupted. This software is compatible with all SQL Server versions. The software is intuitive and simple to use. Just follow the below steps:
- Launch the software and browse to the location of the master database file. To know the master database location, you can use the Find button in the application.
Once the application finds your master.mdf file, select it, and press the Repair button. You can also check the Include Deleted Records checkbox to recover deleted records.
- You can also export the data to different formats, like Excel, CSV, and HTML.
Note: It is important to have your SQL Server service stopped to repair the file. If the file is in use, an error message will be displayed.
Conclusion
The master database is basically the main system database in SQL Server that stores server properties. In this article, we learned how to repair the master database using the installer. In addition, we learned how to rebuild and restore the master database. We also mentioned a SQL repair tool, named Stellar Repair for MS SQL that can easily repair SQL Server databases and restore all the database objects.
