All the system-level configuration settings and login account information of SQL server are stored in the corresponding SQL Master Database files. It contains information about other databases that are present in SQL server. Master database consist 2 files – master.mdf and mastlog.ldf. Also other information details which are included in master database are processes, locks, remote logins, etc. Since it contains records of all the files existing in database, SQL Server will not even start if the master database is – corrupt or unavailable.
Rebuild Master Database from Backup
You can rebuild SQL Master Database by using an existing backup.
To restore the backup in order to rebuild the Master Databasefollow these steps:
Note:First to proceed for restore task, you must have backup of the master database file on and Server instance should be running in single user mode.
- Right click on the database then goto Task then Restore ->Database MDF from the system files.
- Select Taskoption.
- Select the Backupoption and click Restore.
Rebuild Corrupted Master Database
The process of rebuilding the master database is divided into 3 sections-Prerequisites, Rebuilding andPost-rebuild tasks.
PREREQUISITES
For rebuilding the master database in their current settings, following tasks should be performed before you start rebuilding.
1. First record the server configuration values
select * from sys.configurations ;
2. In order to reapply the updates after rebuilding master database record all the hotfixes and service packs applied to server.
3. Keep record the location of all log and data files stored in the database. This should be done because rebuilding process saves all the files in their original location. So in case you have moved your files to other location you can do the same after rebuilding if you have the record of these files’ location.
4. Locate the backup of the master database.
5. Make sure that you are permitted to rebuild the master database. For this you must be a member of sysadmin fixed server role.
6. Make sure that copies of log, master and model files exist on the local server. The templates files are used during the rebuilding process and should be available. Location of template files is:
C:\ProgramFiles\MicrosoftSQLServer\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates
I. REBUILDING PROCESS
The task should be performed in active mode and the SQL Server resource should be taken in offline mode before starting.
- You can insert and run the SQL Server 2014 installation media. Other method is to run the command prompt and change the location of the directory to the location of setp.exe file present on the local server. The default location of the file on server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.
- In a command prompt window enter the given commands. The command prompt should be run as administrator. If you are using Windows operating system in which User Account Control is enabled, running setup may require more privileges.
Setup/QUIET/ACTION=REBUILDDATABASE/INSTANCENAME=InstanceName/SQLSYSADMINACCOUNTS=accounts[/SAPWD=StrongPassword] [/SQLCOLLATION=CollationName]\
NOTE: Ensure that the following points are kept strictly considered:
- When specifying multiple accounts in /SQLSYSADMINACCOUNTS=accounts, separate the accounts with a blank space.
- It is suggested to specify a strong password for sa account as it is vulnerable to get hacked.
- If we change the server-level collation, it does not change the location of existing database. New databases will occupy new space by default.
A. After the setup process has finished rebuilding the database, it returns to the command prompt with no message. Verify the rebuilding process has been completed by examining the Summary.txt log file.
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Logs.
II POST-REBUILD PROCEDURE
After the rebuilding is done you need to perform the following procedure.
- Restore the recent full backup of the master database. If the backup is not available or the current backup is not updated, recreate the missing entries. The most suitable way to recreate missing entries is by running the scripts that created the entries.
- Restore the distribution database if the instance of your SQL server is configured as a replication distributor.
- Move the master database to the previously recorded location.
- Verify whether the previously recorded values and server-wide configurations values match with each other.
NOTE: In case you have changed the server collation do not restore the master database as it may replace the new server collation with the original server collation.
Limitations of Rebuilding Master Database
There are certain limitations that apply when you want to know how to rebuild SQL master database. If a new location is provided to the rebuild statement, the master database is rebuilt at that location. Also any user defined changes made in the master database are lost during the rebuilding process. When the Master database is rebuilt, it is recreated and saved at its original location.
