Pau Lo
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
>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
Can you elaborate for someone who doesnt manage any SQL instances the types of server configurations stored in those DB?
Thanks for your pointers
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.
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.
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.
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.
ASKER
Wasnt aware of books online i work in a risk dept as opposed sql dba but will check it out
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.