December 11, 2008 at 7:28 am
I agree with all of the other posters that the question is very misleading. To have a successful log backup one must have the initial full backup.
I suspect that all of those that answered it correctly do not know that point-in-time recovery is possible.
The vague sequence of steps, as written, is one of the major problems when bugs are authored by testers and/or customers. We developers spin our wheels trying to debug a scenario that is not correct. So we mark it as "NR" (Not Reproducable) or send it back to the author for further clarification, which is wasting everyone's time.
In our company, every bug description must have an ordered set of steps, along with any output ("observe" statements in the bug) with a final "Expected" description. This so that the scenario can be replayed to reproduce the situation.
So if the QOD had the detailed ordered sequence of events, it would eliminate the need for those attempting to answer it to be "mind readers" and/or guess at possible scenarios.
E.g., QOD steps:
1. Create a new database
2. Insert 1000 records as a single transaction.
3. Insert 500 records as a single transaction.
4. Take a log backup
5. Attempt to recover database to point-in-time between steps 2 and 3.
Question: Is step 5 successful?
December 11, 2008 at 7:41 am
JohnG (12/11/2008)
...So if the QOD had the detailed ordered sequence of events, it would eliminate the need for those attempting to answer it to be "mind readers" and/or guess at possible scenarios.
E.g., QOD steps:
1. Create a new database
2. Insert 1000 records as a single transaction.
3. Insert 500 records as a single transaction.
4. Take a log backup
5. Attempt to recover database to point-in-time between steps 2 and 3.
Question: Is step 5 successful?
The 5 steps you listed are exactly what I converted the question into in my head. I guess part of the issue is that the QOD tends to have a trick element to it, which leads to vagueness in questions in an attempt at misdirection. I think the reasoning is that if you lay out the steps exactly, the answers would be too easy. I'm not saying I agree with that, I just think that's the rationale.
Knowing that, I have started to avoid mind reading by going exactly by what is written. And I still get half of them wrong. 🙂
Cheers,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 11, 2008 at 7:42 am
Some people are suggesting that we are assuming that the log backup is successfull but that this is not explicitly stated.....
ok - to take that point further - it is not explicitly stated that the "insert 500 rows in one transaction" is successfull either...
therefore if this step had failed then no recovery would be needed
MVDBA
December 11, 2008 at 7:45 am
But of course question is misleading (as well as explanations).
It reminds me a very old joke:
Answer ONLY YES OR NO!!!
Where have you been yesterday from 8 to 9 pm?
(Correct answer is "NO"). Are you in lucky 30% having a correct answer?
December 11, 2008 at 7:51 am
michael vessey (12/11/2008)
Some people are suggesting that we are assuming that the log backup is successfull but that this is not explicitly stated.....ok - to take that point further - it is not explicitly stated that the "insert 500 rows in one transaction" is successfull either...
therefore if this step had failed then no recovery would be needed
To take it even further, what is to say that the database creation was successful?
Side note: In a proper bug description, step 4 (take a log backup) would have the following added:
Observe: Error " whatever " returned from log backup command.
Expected: Successful backup
And you wouldn't get to step 5. But I left out the above two statements as with them you don't really have a QOD which is essentially (as I read it) "can I perform a point-in-time recovery using my log backup"
December 11, 2008 at 8:52 am
My initial response to the question was "No" because the question never stated a full backup was taken. Like many of the others, the idea of a performing a log backup seemed to imply that a full back exists... although as Hugo mentioned it never states the log backup was successful only that a log backup was executed.
Even though I answered "yes", I agree the correct answer is "No". I should not have assumed the full backup or that the log backup was successful.
Maybe the answer should have stated something about log backups failing until the full backup is performed. But in the end the question either taught us or reminded us that the logs are truncated at checkpoint and that no point-in-time recovery is possible until the first full backup. This is a good point to remember when creating databases.
David
December 11, 2008 at 9:01 am
I've answered too many of these incorrectly because I didn't read the question carefully and tried to assume facts that weren't mentioned.
This question didn't mention that a full backup was taken so I didn't assume that one was. So I answered no.
My reasoning had nothing to do with a log backup failing. If you don't have the full, no log backup is going to help you anyway.
December 11, 2008 at 9:52 am
good question steve. I have been thinking of posting a question based on this little 'gotcha' for a while, but hadn't come up with a good wording for it and was slightly reticent because the poor question setter often gets such a pasting. so anyway I knew where you were coming from and got the answer right!
so well done for beating me to it and sorry I didn't save you the effort of thinking it up. Would I have got more points if I had posted the QOD?
---------------------------------------------------------------------
December 11, 2008 at 11:49 am
Gotta agree with the majority on this one - the existence of a full database backup was implied and the success of the log backup was implied. As such, I still think the answer to this is Yes. Oh well, a decent learning experience and I'll take my point from this post.
December 11, 2008 at 11:55 am
Peter Schott (12/11/2008)
... Oh well, a decent learning experience and I'll take my point from this post.
You're still short 2 points as it was a 3 point question!
December 11, 2008 at 11:58 am
Peter Schott (12/11/2008)
Gotta agree with the majority on this one - the existence of a full database backup was implied and the success of the log backup was implied. As such, I still think the answer to this is Yes. Oh well, a decent learning experience and I'll take my point from this post.
To quote dialogue from Cool Hand Luke, "What we have here, is a failure to communicate."
December 11, 2008 at 11:59 am
You have to presume some things in this question. For example, the question didn't state that the INSERTs were committed. The question doesn't even state if the INSERTs were successful. If we presume the log backup is there, that would mean a full backup had to be made. Therefore if an full backup was made, recovery at point in time using the log backup would be successful.
December 11, 2008 at 12:02 pm
Lee Nadelman (12/11/2008)
You have to presume some things in this question. For example, the question didn't state that the INSERTs were committed. The question doesn't even state if the INSERTs were successful. If we presume the log backup is there, that would mean a full backup had to be made. Therefore if an full backup was made, recovery at point in time using the log backup would be successful.
Agreed!
December 11, 2008 at 12:37 pm
Lee Nadelman (12/11/2008)
If we presume the log backup is there, that would mean a full backup had to be made. Therefore if an full backup was made, recovery at point in time using the log backup would be successful.
Hi Lee,
That would still depend on the exact time the full backup is taken. Recovery to a point in time before that full backup is still impossible. If we're going to assume, then at least assume the most probable, which is that the full backup is taken as a response to the error message generated by the first, unsuccessfull attempt to backup the log.
Some people appear to think that this QotD attempts to test knowledge of whether a log backup can be used to restore to some point in time (which would be very meagre for a 3-point question). I think the question instead targets the common myth that full recovery starts as soon as the recovery model is set to full or bulk-logged (or, in this case, as soon as an empty database with that model is created). It tests if you know that a full backup has to be taken after changing the recovery model (or creating the DB) before the switch is really effectuated. And this is a common misconception, esp. with regards to new databases:
"Did you create the database? Good, now make a backup of it"
"Huh? It's new. It's empty. I don't need a backup of an empty database!"
"Yes, you do. You can't recover if you don't make a backup now."
"That just makes no sense! I don't need a steekin' backup to recover an empty database! I'll just create a new one!"
"(sigh) Do you want the short or the full story? Short story is, shut up and just do it. Long story involves lots of boring details about recovery model, log record recycling, LSNs, and some stuff about new databases not being empty thanks to the model database"
😉
December 11, 2008 at 12:45 pm
Since the question does not give enough information, I guess the answer to the question really depends on one's assumptions. This is one of those questions where there should have been a 3rd choice for there's not enough information given to answer this question.
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply