During a recent consultancy project, I was asked to review a Database Backup plan. While going through the plan, I noticed that there was no backup for the master database. When I questioned this, the DBA informed me that it was not necessary. I was startled and couldn’t resist explaining to him that the master database contains all the logon accounts details, as well as all the system-level database configuration. He was a little astounded and asked me to tell him at what intervals he should backup the master database. The discussion that followed was very thought provoking and I would like to share the major points with you:
- The master database is the most important database of all and the most recent version of the backup should be available in the case of disaster.
- Backup of the master database should be made for:
- Changing server-level configuration settings;
- Changing database-level configuration settings; and
- Changing any logon accounts details.
On further observation of his master database, I found that he has some system maintenance Stored Procedure created in the master database. This should not be the case. The master database should not contain any user-created objects. Users should not be required to modify or create anything in the master database. The real purpose of the master database is for the SQL Server to maintain itself. Users only need to make backups and restore when needed.
Do you backup your master database? Please leave your comments here.
Reference : Pinal Dave (https://blog.sqlauthority.com)
23 Comments. Leave new
Hello Pinal Sir,
I’ve configured jobs through agent for scheduling back ups for different versions of development databases. But, i was not giving very much importance to the system db’s back up (specifically to the master db). After going through the article, the same alerted me of how important that master db/sys db back up’s plays vital role in DBA life. I did scheduled 1 job for doing this activity. Thanks & nice article.
Regards,
VSVaidya.