How to rebuild and restore Master database in SQL Server?
Master Database which stores critical information about configuration, login and pointers to your database is the quintessential part of your SQL Server. Any changes or corruption in this master database would ultimately restrict you from starting your SQL Server properly. Being such an important, it is advised to always have an eye over your Master database to save yourself from any disastrous situation.
- You may not be able to start your SQL Server properly.
- Input or Output errors are generated.
- Database Consistency Checker Utility generated the report.
- Earliest possibility of Segmentation fault.
This corruption in Master Database could be due to various possible reasons among which power surge is very common which compels the SQL Server to reboot but certain times after rebooting it finds problem even in starting SQL Server. Error log could be checked in this scenario to know the reason of corruption.
In order to handle such situation of damaged Master Database, you are needed to reinstall SQL server from scratch and hence require a lot of your patience and time. The whole process of reinstalling SQL server could be divided into first rebuilding the master database, starting the SQL server and then restoring the backup. But before all of this it is advised to take a full backup of your master database to avoid any odd scenario.
- To rebuild the master database use Rebuild Wizard Utility (Rebuildm.exe) which is available at location "\Program Files\Microsoft SQL Server\80\Tools\BINN".
- Double click on the utility to open it.
- A window will appear which will ask for the location of files of your database server. Also fill in the desired collation settings.
- You are also needed to provide data directory from the SQL CD or in case you copied it direct to your local hard disk.
- Click on the ‘Rebuild’ option once all the information is filled and verified.
- Once the process is finished, a brand new master database will be build for you by this utility.
- Start SQL Server in single-user mode by typing this command in command prompt window “sqlservr.exe –c –m”.
- Now open SQL Server Configuration Manager and go to Startup Parameter. Here it will ask to specify the parameter, specify “-m” in the box and click on add. Apply the changes and rebuild the database.
- Write the below syntax “Start /wait setup.exe /qn INSTANCENAME= < InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 sapwd=< NewStrongPassword>” and rebuild the corrupted database.
- Open SQL Server Configuration Manager and then go to SQL Server 2005 Services.
- Right-click on the SQL Server instance and select ‘Properties’ option from there.
- Go to the Advanced tab and then select SQL Startup Parameter. Specify “-m” which will add it to already existing parameters.
- Once the task is completed remove this prefix and run the following command to connect to the SQL Server “RESTORE DATABASE master FROM DISK = ‘C:\BackupLocation\master.bak’ WITH REPLACE;GO”.