There are two types of databases that are supported by SQL Server: System database and User database. The system databases are used by the SQL Server, SQL Server services, and functions while the user databases are used to store data of user’s choice, the data for which SQL Server is being set up.
There are five types of system database: master, msdb, model, resource, and tempdb. Here, we are going to discuss the Master database of SQL Server, its importance and possible solution to troubleshoot if it gets corrupted.
SQL Server Master Database: An Introduction
Master database contains the primary configuration details of SQL Server. The system database tables along with the Master DB is known as system tables that records the server parameters as well as detailed information about every user and database.
The master database gets stored in the physical file called master.mdf file and its corresponding transaction logs gets saved into master_log.ldf file. Generally, this database is stored at default location and is small in size. For this reason, sharing the drive with the SQL Server instance will not cause any performance issues. If the master database gets corrupted, system database will not start along with the user database.
How to detect that Master DB is Corrupt?
Before we actually move to the topic on how to recover a corrupt master database, we will have an idea on how to discover that the DB is actually corrupt. Here, we break a master DB to exemplify its consequences.
Let us have a hypothetical scenario that the enterprise confronted a power surge and when the server was rebooted, it failed to start with error registered in the event log.
How to Fix Corrupt Master Database?
Here we are going to discuss three options to resolve MS SQL Server master database corruption issue. You can opt any of them according to convenience and availability of resources for their proper execution.
1: Rebuild the master Database
To repair the system database of including master DB, run SQL Server setup.exe. Open Command Prompt and move to \servers folder. The syntax for rebuilding the database in SQL 2005, the syntax used is:
Start /wait setup.exe /qn INSTANCENAME=
Note: The parameter “qn” will suppress the dialog boxes and errors while processing the command. Since the model, msdb, and master database reside on same disk, it is likely that a disk failure may lead to loss of all three system databases, The process mentioned above will help to rebuild all three DB together.
2.Restore Database from Backup
To restore master database, it is necessary to have full backup of the master.mdf and master.ldf file available. Before starting up the restoration process, it is important to start SQL in single user mode. For this, follow the steps mentioned below:
- Open “SQL Server Configuration Manager” and then click on “SQL Server 2005 Services”.
- Now, select the SQL Server instance, right-click on it and choose “Properties”.
- On the window that gets opened, click on “Advanced” tab. For Startup Parameters box, prefix the parameter “-m;” before already existing parameters.
Make sure that the added parameter is removed one the required task is done. Once this is done, you can connect to SQL Server using “sqlcmd” and run following command to restore the database:
RESTORE DATABASE master FROM DISK = ‘C:\BackupLocation\master.bak’ WITH REPLACE;GO
3. Recover Master Database .mdf File
Another option that can be adopted to start up SQL Server in case of master database corruption is MDF Recovery tool that helps to restore SQL Server Master Database into healthy form. There are solutions that give the benefit of recovering system databases (including master.mdf) database. The best part about these software applications is they give a solution repair system database for any SQL Server which means even if you upgrade or degrade SQL edition, they will be with you as helpful DPR plan.
Rebuild, Recover, or Restore: Which is the Best Option?
In our day to day life, we use our mobile phone that stores so many contacts in it. This saves us from memorizing them and of course do not demand to write all these contacts. If the cell phone is lost, you can get back to normalcy only when you get a new phone. That means you have to manually add all the contacts to the phone because the contact details are lost with the old phone.
A master database plays the same role in SQL Server as contacts in the mobile phone. If you lose access to it, all information needed to start up the day-to-day functioning will be lost. Rebuilding master database is like getting a new phone where you have to manually add information about user-accounts (login details), the permissions assigned to them, the previous configuration details and much more. On the contrary, if the database is restored using backup, it is always positive point DB will be received in the same state as it was before.
Conclusion:
The master database stores the all the metadata about the SQL database be it configuration details, login details, file location, information about pointers and much more. With the master database in corrupt or inconsistent state, it is not possible to start SQL Server and run any query. The above mentioned solutions are tried-and-tested for their successful master database recovery and can be adopted.
EmoticonEmoticon