Recently we've had to interview some SQL Server DBA candidates for our team, and we were looking for the type of open-ended technical questions that would draw out interviewees and let us get a sense of their thought process. A surprisingly simple question came up that seems to work well - it makes the candidate think through and explain some internals of SQL Server in a way that I think reveals whether or not they have a handle on fundamentals about the transaction log and backups. The question is simple:
You have a full backup that kicked at 7:00 pm last night. You have to restore the data in the database to 7:00 pm last night. Can you do it with that full backup file? If so, why does it work? If not, why not? And how do you get it back to that 7:00 pm state?
The "why" bit is the most interesting part. I like a candidate that gets the answer wrong, but with the right reasoning about the log, better than one who get's it right but can't explain why. And the worst answer is obviously, "Sure - a full backup just has all the data as of the time it started!"
What's your favorite question?