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
Yes i do …It like 3 months back i also don’t know about the importance of Master Db but now i know so yes i take backup’s of master db and i think this article helps a lot of people to understand about this.
Thanks..
Comment about user stored procedures in master db. We are running specialized server-side trace for auditing instead using c2 audit option. We used sp_procoption to set stored procedure to autorun if SQL server is restarted. Sp_procoption requires the stored procedure to be in the master db. Is this an acceptable exception or is there a better way?
Hello Sir,
Nice information for Master database backup.
One question is
is there any difference in MSDB database
in sql2000 and sql2008 ?
Hello there.
Yest it is. There are new features in SQL 2008 that also use msdb database. First which comes to mind is multi-server administration.
best regards,
calin
I never backup the system/master databases. I wrote a detailed instruction how to recover user databases and how to setup sql server. I also stored scripts for user credentials and system configuration, so any other admin in our house could easily setup a new sql server or recover the current one. No need for master database backup here ;)
Hi!
In first place, i want to thank you for your blog. I’m a jr DBA (started less than a month ago!) and i have been reading your blog since the begining. I find it very useful and easy to understand. In fact, I always have the shorcut and the sql cheat sheet at hand.
Regardint the post… I’ve included a backup of all the system databases in the Weekly maintenance plan.
Greetings ^^
Thanks for really nice post.
I read your blogs as soon as I turn my computer ON in the morning. This blog is really helpful to me. I just started as Jr DBA almost 1.5 month before. I read your blogs all the time. And it helps me directly or indirectly. Thanks for all these information.
I will definitely discuss with Sr DBAs about taking backup of master database.
Thanks,
Nice article. The backup of master database often gets overlooked, it is important have a backup plan for the master database, here we do have a backup plan for the master database.
Sir you are great!
No backup for system databases is foolish imho. Who wants to recreate all thess users and passwords and jobs by hand or by scripts. Only fool ;) ;) ;) or peson with full of spare time.
I backup my system databases automatically every week because I’m lazy. Also manually backup after changes like for example: a lot of user accounts creation.
Thank you very much for sharing your experience with us.
Hi!
We work with DB’s in ERP’s we commercialize.
The program it self, have a Maintenance Plan where we choose what DB’s we want, how often, and where to save.
But it doesn’t offer the possibility to choose System DB’s.
Since until the very last version, they haven’t a proprietary Service to do the Backups, they depend of SQL Agent.
With the change to SQL 2005 and because Express version doesn’t have, we have been forced to look for other solutions.
So by now we have at least 1 time by day backup of all DB’s (System and User DB).
In fact, I loved this system, because when the User create in the ERP a new DB, they can be stupid enough to not choose a Maintenance Plan for the DB, and with ExpressMaint I have sure at least one time by day ALL DB’s are backup.
So even in clients who have SQL Paid version’s I always use ExpressMaint in Schedule Task .
But my question is, since the ERP doesn’t create SQL Logins, it have they “master” DB where stores the users login, passwords, privileges and configurations, do I really need to backup Master and other SysDB’s ?
Just discover your blog, and I really enjoy. A must to all of us who need to work with SQL Server.
can we maintain lot of databases in one single database..
i heard this fact.. master database can contain lot of info. abt other databases in server, like this can we store all the databases in one database.. plz reply me .. early…
Nice article, however, I have a maintenance plan in place for full backup of all dbs in the instance every night.
@lakshman
Could you be more specific about your question? If you want to consolidate the objects and data of all databases to a single database, it can be possible. However, it can be impacted on Performance and Security front. It will be nice if you re-phrase your question.
Thanks.
Nice article. I agree with you backing up the master database its a must. The same counts for the MSDB database.
Nick article!
We do backup our master databases – daily. Our sql servers are not active during the early morning hours, so we have plenty of time to do daily full backups of every database.
I lost my master database recently, and I do now keep backups.
To be honest, it’s not too much effort to rebuild it in our case.
I do wonder if this is actually easier than the hoops you might have to jump through to restore your master database.
I recommend people looking into this as you might get a nasty surprise if you just think you’ll be able to restore the database again and off you go…
If anyone has found an updated procedure on how to backup the master in SQL 2008 R2, please share it here. For the time being, there’s no such procedure out there.
best regards,
calin
No Sir,
Not at all. As U know.
Our DBAs create stored procedures in the master database, but do not feel that it is a big deal. They are utility type processes (e.g. usp_Drop_Impact, usp_What_SQL_is_Running, etc.). Do you think it is okay for them to be there?
It wont be much proble. However it is better to keep in the respective databases