system databases data and ease of rebuild

I have the read that the master DB is crucial for an effective MSSQL backup and restore policy – but when looking at why people said it stores login information? Excuse my ignorance but if it’s just a username and password is that a real big deal to just fresh install MSSQL and create the same or a new set of usernames and passwords? Or am I missing something? It doesn’t sound a massive issue.

Also someone mentioned “agent jobs”, what kinds of things are these agent jobs doing? And how hard are they to setup from scratch to match those preconfigured before? Is it easy to re-setup or a nightmare if you don’t have a backup?
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Agent jobs, etc are stored in msdb, rather than master.
The master database has the logins, and other server level objects (linked servers, endpoints, and the server configuration.)  If you don't mind re-linking/fixing the sids between users and logins, and possibly fixing dbos (realizing that both the procedures I linked are deprecated, I just haven't yet committed the new ALTER methods to memory), and recreating the other objects which would be lost, I suppose it isn't a massive issue.  (Where I have a couple hundred dbs, and many hundred logins, it would be a little bit of a nightmare... mostly because I wouldn't have a list of all the logins.  I count on that list from the master db.  :-) )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
You must back up the master database.  It contains too much unique, vital info to do anything else.  If you need to recover the instance, you'll require that backup.

Msdb as well, for almost all sites.  It, too, contains too much data to ignore.

You may not need to back up the model db, although I strongly recommend it anyway just in case it gets customized.
0
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Concur.  Where you don't have a backup of the master db, you're recreating a new instance, and dropping in database backups, and attempt to scramble to recover what you can.  How bad is that recovery?  "Depends."  But the Master database usually isn't very large (orders of magnitude smaller than user DBs).  Save yourself the trouble now, and just plan to back it up.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pma111Author Commented:
>the server configuration

Can you elaborate for someone who doesnt manage any SQL instances the types of server configurations stored in those DB?

Thanks for your pointers
0
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Nearly everything which is set for the instance is stored in master: memory configuration, default index fill factor, default file locations, query defaults (parallelism, etc), auditing settings.  I didn't previously know this, but while looking to see if I could find a list of settings, and what would be required to rebuild the master database, I spotted a note in the MSDN that if the master database has to be rebuilt, it rebuilds all the system databases.

Keep in mind, you aren't backing up these databases just to recover from catastrophic server failure.  Although it's unusual, *knocks.on.wood* individual databases can and do become corrupt... and that can include the master database.  There are a lot of corruptions where the 'correct' and 'best' course of action is to recover from a backup.  The information in the master database doesn't change a lot (relative to changes in user databases), and taking the backup is super easy and quick, especially compared to the possible challenge of rebuilding/recreating.
0
Scott PletcherSenior DBACommented:
You do have "Books Online", right?

In the index, find "master database [SQL Server]", then underneath that click on "About".  You'll see some of the critical things stored in master.

But even for just the logins and passwords, how else would you be sure you had a complete and accurate list except by backing up master?  You've got to remember, by default, anyone can change their own password at any time.
0
pma111Author Commented:
Wasnt aware of books online i work in a risk dept as opposed sql dba but will check it out
0
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
The two references I linked earlier are from Books Online.  Many versions of SQL ago, Microsoft decided to stop making paper copies of SQL available.  I can't remember if it was version 4 or 6 that I was last able to get the bookshelf worth of references for SQL.  They moved to an electronic version of the documentation which was distributed with the software.  As updates to the software kept coming up, there became a problem that the documentation which would get installed with SQL would be out of date.  Back a few versions of SQL ago, Microsoft decided to stop distributing the electronic books on the DVD.  You can still get the older versions from Microsoft Download.  They do make the 2012 and 2014 documentation available if you absolutely can't get to the Internet.  Otherwise, the content of the SQL Books Online have been absorbed into MSDN and Technet.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.