SQL Homework – July 2017
11July 3, 2017 by Kenneth Fisher
For years Russ Thomas (b/t) has done a Monthly DBA Challenge and in fact I’ve used it as insperation a number of blog posts myself. Here is part of his description of it:
Often the task is what I consider a low frequency / high liability event – something the typical DBA might only do once or twice in their career, but if called upon would be highly critical to the business.
For example (and one used by Russ in a presentation I saw once) let’s say that the drive that holds tempdb for your instance is gone. Someone made a change on the SAN and poof. Gone. So what do you do? How do you fix it? Do you panic? Or, just maybe, you’ve tried it before. You may not remember exactly what to do, but you know it can be done. A quick bit of memory searching (or as I call my memory Google) and off you go. Also likely the challenge is just a suggestion to push you to try something new.
Anyway, to make a long story short (Too late!) Russ has decided to retire the challenge and asked if anyone wanted to take it over. So, you guessed it, I’m going to give it a shot. I’m going to put my own spin on it though and it’s going to be SQL Homework. Get it? It fits the theme? Come on! It’s pretty obvious. Right? I’m going to be starting with basic but important tasks and I’ll do my best to build over time.
So here is your homework for July!
- Take a full backup of a database
- Use that backup to restore to an alternate location
- Run a query against that alternate location to make sure everything is working correctly
Here is how the grading will go
- A – You do everything using T-SQL without any form of help.
- B – You get it done using T-SQL (If you needed a lot of help you might save the script somewhere for the future).
- C – You got it done. Maybe using the GUI, maybe using a 3rd party tool. But you got it done!
- D – You tried. Maybe you didn’t get it all done, but you made the effort.
- F – You didn’t even give it 5 minutes it would take to try.
SQL Studies 
Well FWIW …
… I have an SProc that does the BACKUP. It makes sure the backup is in the normal place (so a contractor, for example, taking “a quick backup just in case” doesn’t give us heart failure if we need to do a backup and can’t find his/her backup file (yeah, I know that Contractors ALWAYS do COPY backups …). It has @Parameters available for Database Name, a Comment, and so on; the created backup file is logged and automatically subject to our retention rules.
And I have a RESTORE Sproc too. I can fill in just a few parameters – Database name and “”MyDatabase*.BAK” and get a directory listing, or it will give me the results of an MSDB query of what was recently backed-up [including if the most recent Full Backup, which I now need to restore from, was itself actually the result of a Restore].
That gives me a suggested “fuller fat” EXEC command with all parameters filled in (or more info to help me provide answers). I can restore from just FULL, optional DIFF and if I want the TLogs it will list them all out, with a STOPAT on the last one. The final syntax includes a MOVE and adjustment of Logical Names etc (if required / not matching the [new] database name), and some sample code SYNC logins etc. if the restore is on a different server.
Must be at least 17 years that I have been “improving” those SProcs, but now I’ve got them I appreciate having them.
I’m probably going to get an F though as I couldn’t be bothered do the query to prove the Restore was OK …but in my generated code is a, comment-out, CHECKDB command … So maybe I’ll get a D 🙂
“heart failure if we need to do a backup ”
Grrr .. .sorry, that should be “if we need to do a restore”
🙂 Since the purpose of the homework is to make sure you can actually do a backup & a restore (preferably with code and not just the GUI) I think you are good. My guess is if I sat you down without your SP and asked you to do a backup and then restore it somewhere you could do it without much effort. We will go ahead and give you an A. (Past efforts count)
Thanks, my Mum will be proud 🙂
For extra credit, have you thought about posting your SPs for everyone to use?
I’d love to, but my time is short, and the code would need some work to make it more general (e.g. to remove proprietary stuff such as the logging for stale-file purging after retention period). Sadly I’ve got lots of code like that, which would probably be useful to others, but is intimately tided to some in-house stuff which makes it hard to decouple. A job for when I retire …
I understand completely! A lot of the stuff I’ve done over the years I write for my blog first (to make it more general and bullet proof) and then take to work to use there.
[…] Plus if you ever want to ensure that you know something, try and script it out from scratch. […]
[…] you didn’t see it last month I’ve started doing monthly SQL Homework. The first month was backups, this month it’s […]
[…] very first SQL Homework post was about taking a backup. The vast majority of people who work with databases (there might be some reporting people who are […]
[…] the very first SQL Homework post you were asked to take a backup. In fact it asked you to both take a backup and restore it. Because, I’ll be honest with you, […]