How to Restore SQL Server Master Database?
In SQL Server, administrators depend on the healthy master database to work uninterruptly. The Master database in SQL Server stores all the crucial information like configuration details in a file (.MDF file). So, this information must be preserved by having backups regularly. However, this importance of backup is neglected knowingly or unknowingly by the administrators. Loss of any important data from Master database like parallel transaction logs, users and their login details, pointers information can impact the smooth working in SQL Server.
SQL administrators can identify their Master Database is no longer healthy if it has become inaccessible with input or output errors, power failures, or the report is generated with Database Consistency Checker Utility. The end results for these kinds of symptoms are the Master Database has been corrupted and needs to be rebuilt.
There can be other reasons as well apart from corruption to rebuild the SQL Master Database such as –
- User has deleted the important information like SQL Server configuration, user logins, linked servers, and user objects.
- User needs to perform an exact copy of the Master Database and restore it to a new instance in case of devoid of its backup.
So far the importance of SQL Master Database is learnt, now coming up to the solution to recover it back can be rebuilding it and restoring to the SQL Server. SQL administrators with some technical hand in knowledge can perform it manually which we understand in the next section.
Solution to Rebuild Master Database in SQL Server:
An SQL administrator or user with such rights can perform this Master Database rebuild process. Perform the steps sequentially to rebuild the database.
Some prior measures need to be taken before starting the actual rebuild process. As we know Master Database is corrupt, so we need to take the backup of other system database files to ensure their backup in case of any data loss. Follow the given steps to backup system database files like MSDB data, MSDB log, Model log, etc.
- Launch the SQL Server Configuration Manager. Click on the SQL Services option from the left pane. All the services which are running currently get enlisted.
- Select each service one-by-one, right click on it and select the Stop option in order to stop the services.
- Once done, exit the SQL Server Configuration Manager.
- Now browse in the system for this location:
C:\Program Files\Microsoft SQL Server\[INSTANCE NAME]\MSSQL\DATA
From Details, right click FullLoadImagesOnFileLoad and click Modify.
Note: This is the location where all the system database files are stored and also Master Database file that is going to rebuild will get stored.
- Now reaching to this location, cut all the healthy system files from here and paste it to a new folder at different location.
Delete the corrupt Master Database log files from the location.
Now, as the backup of system database files have been taken, let us understand how to perform the rebuild process. Just execute the below steps to perform the rebuild of Master Database of SQL Server.
- Run Command Prompt as the administrator.
- You need to change the directory location of the server instance to the location of SQL Server installation media. For that type the drive name with colons where SQL Server installation media is located and press enter.
- Now that location is changed, run the given command –
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
User need to provide the Instance name of SQL Server, account name, password and collation name. For default instance name, user can use MSSQLSERVER. For adding multiple accounts, separate them with a space. And if a single account includes space, place the account name within inverted commas.
- If after running this command, no message or error comes as output, then rebuild of database is successful. Check it by browsing the location - C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs and verifying the summary file in text format.
Restore the rebuilt Master Database in SQL Server
The rebuild of Master Database of SQL Server is performed. Now the user’s task is to restore that in SQL server in order to work on it. Here are the steps which will help you in the restore.
- Go to the SQL Server Configuration Manager, select SQL Services option and restart the services which were stopped before by right clicking one-by-one.
- Restore the backups (if present), otherwise first stop the running services again and replace the new rebuild MSDB and model files with those backed up files (backed up before starting the rebuild process).
The Master Database is again restored to the SQL Server in a healthy state.
The manual way to rebuild and restore the Master Database to SQL Server is discussed with complete procedure. The procedure needs some technical knowledge, good efforts and time as well. Failure in executing the commands can cause failure of procedure and data loss as well.
For the busy SQL Server users, there is another proficient way of restoring corrupt SQL Server master database with minimum efforts. The robust SQL Database Recovery tool can recover any type of SQL database file with minimum efforts and requirements. Only corrupt SQL database is needed with SQL Server details is required for the recovery. Users can take experience with the trial version and take self decision.
The blog explained the rebuild and restore process of Master Database to SQL Server. This manual solution can be tried by the users suffering from inaccessible SQL Server database. A second option to save time and efforts in recovering the SQL Database, the professional SQL database recovery software is also suggested for the help of users.