Absolutely agree. Very sound advice.
My other “Absolutely Essential” suggestion on normalisation is “Don’t store the same data in two places”.
So you have a Phone Number on the Customer Table, and you also have a Phone number on some other record which is storing the same data value, or a “copy of it”, or the Operator is entering it in two different places. Some days/months/years from now the phone numbers become different - which one do you trust? how much time do you waste trying to validate, and synchronise, them? IME that would be “a lot”, along with users cursing and losing confidence in the system …
I came at it from the “doing” end too. I haven’t got a single qualification to my name (some here may well say “that’s the problem” ) so I, too, strongly recommend finding a means of “doing” as much as just studying for exams. Whilst maybe not the norm? I would employ someone with experience over someone with qualifications. 80%?? of the syllabus for the qualification’s examination we don’t need in real-life, here, as it effects us, and for the rest we know where to ask, but the bit that we do know we are very very good at because “that’s what we do” every day.
If you want a project here’s my suggestion:
Write a scheduled routine that makes backups. Here’s a spec, based on what our in-house tool does:
A Procedure to make a backup - pass it the Database Name and the type of Backup (Full, Diff, Log). Optional comment. Optional “retention period” (days). ALWAYS use this when you want to make a one-off backup - e.g. just before you do something “that has risk”.
That procedure will ALWAYS store your backup file in a predictable location, and add it to some Metadata that you store about your backups (such as when they can be deleted).
Then create a scheduled backup. Have a table of Database names, and when the last (Full, Diff, Log) backup was made, along with what type of backup you need for that database (basically “Mission Critical, frequent” or “Once a day will do”, and maybe some other “flavours”). Your scheduled task then needs to figure out which backups overdue, need running, and then run them.
Then add a scheduled task that Adds any newly created databases to your Database Table - with a default backup method (perhaps choose “Mission Critical” for that so that, worst case, any newly created database gets the highest quality backups). We explicitly exclude any database name that starts with “RESTORE_” - so that we can make a test restore WITHOUT clogging up the backup system. You might have other nuances - e.g. if the Database name ends in “_TEMP” or “_TEST” …
Then figure out a list of things that could go wrong, and make sure your backup routines take care of them:
- Database changed to Read Only / Offline / something else
- Database changed from FULL Recovery model to Simple (or the other way)
- Database no longer exists (is that something you should Report/Alert/Notify about?)
- Disk full / Target Drive does not exist / all the other horrendous possibilities!
- Database is corrupted - figure that the DBA would like to know about that sooner rather than later!
- Backup file security - we copy Backup Files to an alternative disk/server as soon as they are completed. (They also get copy to Tape/Cloud, but “not immediately”). Our Database Meta Table has info about “where” to make that duplicate copy.
- Automatically restore to DEV or similar - and then run a DBCC CHECKDB on the restored copy - this proves that your Backup did actually work!
- Check that YOUR backup has ALL the appropraite files - nothing is missing from the set - it is common for “someone else” to set up a backup, which then means that your “perfect” backup chain may be reliant on the other people’s backup too. Getting to know about this ONLY when you have a disaster (and then trying to figure out where the heck they stored THEIR backups … particularly if they are only on Tape, which you cannot quickly get restored) is much too late! We get this all the time from some other “well meaning” company that adds some Backup Tool to the server, and detecting it the moment that the Backup Chain was broken has saved our bacon on more than one occasions (the 3rd party can make a COPY_ONLY backup, that doesn’t break the chain at all, so once you tell them they can do that)
A Restore Procedure would be handy too. I want to restore to Friday at 14:56 - what backup files do I need restoring? Automatically generating the script for that saves time (usually at a time when everyone is screaming and time is of the essence). That needs to work both from your meta data about backups (or the data that SQL keeps in MSDB, if you like), but also from the physical backup files that are able to be found on disk
Blimey, I’m exhausted typing that! Our backup routine has evolved over the years, as we’ve needed new features. There are good products out there, so you could use those as a reference. I don’t think that the Microsoft provided Maintenance Plans are suitable for … well “anything” really. They do the job, but they do it in a way that I think solves the wrong problem
Take a look at:
Minion Backup (I like this one, nice and simple to set up)
Ola Hallengren’s backup (I find this one very complex)