?
Solved

system databases data and ease of rebuild

Posted on 2014-08-11
8
Medium Priority
?
281 Views
Last Modified: 2014-08-21
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?
0
Comment
Question by:pma111
  • 4
  • 2
  • 2
8 Comments
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 1000 total points
ID: 40253196
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 40253213
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
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 40253233
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Author Comment

by:pma111
ID: 40255083
>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
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 40255287
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40255905
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
 
LVL 3

Author Comment

by:pma111
ID: 40256257
Wasnt aware of books online i work in a risk dept as opposed sql dba but will check it out
0
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 40256396
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question