I have received following questions many times about master database. I will list all the questions here and answer them together.
What is the purpose of Master database? Should we backup it?
Which database is must have database for SQL Server for startup?
Which are the default system database created when SQL Server 2005 is installed for the first time?
What happens if Master database is corrupted?
Answers to all of the questions are very much related. The master database is system database and it contains information about running server’s configuration. When SQL Server 2005 is installed, it usually creates master, model, msdb, tempdb resource and distribution (last two depends on version of SQL Server) system database by default. The only Master database is the one which is absolutely must have a database. Without this database SQL Server cannot be started. This is the reason it is extremely important to backup Master database.
If Master database is corrupted it should be restored from the latest backup, considering SQL Server was able to start with a damaged Master database. Sometimes it is corrupted so much it cannot start SQL Server at all and it cannot be restored. SQL Server should rebuild using command prompt and restored from the latest backup once again.
I am very much interested to know the feedback of readers who have asked questions regarding this issue.
If any database is corrupted it should be restored from the latest backup, considering SQL Server was able to start with a damaged database. Sometimes when this database is corrupted so much it cannot start SQL Server at all and it cannot be restored. SQL Server should rebuild using command prompt and restored from the latest backup once again.
Backup of the this database should be made for:
- Changing server-level configuration settings;
- Changing database-level configuration settings; and
- Changing any logon accounts details.
Where does master data management fall within a data governance approach? Master data management is one area within the data governance process that focuses on the management of reference and master data. Master data management is combined with a fair amount of business process and I will leave the process aspect for another author. An example of a business process within master data management would be the decision on which data source is considered the truth when a discrepancy in master data arises between two source systems.
Reference: Pinal Dave (http://blog.SQLAuthority.com)