March 9, 2011 at 7:46 am
Koen Verbeeck (3/8/2011)
Ol'SureHand (3/8/2011)
Not sure I get the point of the trick question.It should be clear that you can restore to ANY point in time (at each 15 minute interval in this example). However, restoring to the 9:30 state will not recover your TRUNCATEd table ... so was that thrown in as a red herring ?
If so, it got me ...
The question asked: Is it now possible to restore to a given point in time?
It is possible, so the answer is certainly yes. The question didn't ask if it was possible to restore that specific table to any given point in time.
Anyway, nice question.
Thanks Koen. I was wondering if I had went to sleep of if this question was worde teribly to make more than one answer possible.
Glad to know I'm awake.
March 9, 2011 at 7:48 am
Good question. I too over thought it.
March 9, 2011 at 7:57 am
This was removed by the editor as SPAM
March 9, 2011 at 8:04 am
Duncan Pryde (3/9/2011)
Gobikannan (3/8/2011)
I learned something form this question., Nice question.Thanks, glad you liked it.
Duncan
Duncan,
I learned something also.
I learned I am not the only person that was confused by your question.
I do want to thank you for giving me a new interview question to ask.
Anyone that answers this question like you would not be considered for a position where I work. :w00t:
A good DBA always considers data loss when answering any question.
People that are not RDBMS professionals depend heavily on those that are to answer questions like this with data loss always in mind. With that in mind, three of the answers could be argued as correct given the wording of the question.
Maybe if we had not just re-written our DR plan and I was fresh out of SQL classes I would have answered differently.
Thanks for the guessing game. I guess today I was not lucky. :laugh:
March 9, 2011 at 8:16 am
It's clear to me, and if you think there is some implication that you are restoring to some specific point in time, you are reading something into the question that is not there. The question asks simply if you can restore to a point in time, and you can, based on the transaction log backups. The truncate statement is listed to see if you think this affects your log backup/recovery, which it doesn't.
I'm sure you've read something about this on the Internet, but it's not true.
March 9, 2011 at 8:16 am
SanDroid (3/9/2011)
Duncan Pryde (3/9/2011)
Gobikannan (3/8/2011)
I learned something form this question., Nice question.Thanks, glad you liked it.
Duncan
Duncan,
I learned something also.
I learned I am not the only person that was confused by your question.
I do want to thank you for giving me a new interview question to ask.
Anyone that answers this question like you would not be considered for a position where I work. :w00t:
A good DBA always considers data loss when answering any question.
People that are not RDBMS professionals depend heavily on those that are to answer questions like this with data loss always in mind. With that in mind, three of the answers could be argued as correct given the wording of the question.
Maybe if we had not just re-written our DR plan and I was fresh out of SQL classes I would have answered differently.
Thanks for the guessing game. I guess today I was not lucky. :laugh:
I'm not sure who is more confused - you by my question, or me by your post!
Thanks for the feedback though - I think.
Duncan
March 9, 2011 at 8:24 am
mvelic (3/9/2011)
Well, I got this wrong but I think I would have worded it differently. If you don't take a tail of the log backup after the TRUNCATE TABLE (or wait for the next log backup at 9:30), then you really can only restore up to 9:15. I think with just a little more description the question could have been more clear.
Actually, that's how I got the right answer. I thought of the tail of the log backup (which I thought the question was probing for knowledge of) and picked Yes. I did not even get worried about the truncate statement thinking that even if the truncation was NOT logged for some reason, my data and all of it's changes were still captured in the full and trans log backups and current log. I saw the truncate as a red herring also.
Anyway, had to think about it for a minute to be sure I picked right. Thanks for a great question!
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
March 9, 2011 at 8:27 am
if the TRUNCATE TABLE command is logged, why can't you run it on a table that is published for replication?
March 9, 2011 at 8:31 am
SoftwareArtiste (3/9/2011)
mvelic (3/9/2011)
Well, I got this wrong but I think I would have worded it differently. If you don't take a tail of the log backup after the TRUNCATE TABLE (or wait for the next log backup at 9:30), then you really can only restore up to 9:15. I think with just a little more description the question could have been more clear.I completely agree. I answered the way that I did not because of the TRUNCATE TABLE statement, but because of the backup schedule. Assuming you were trying to restore after the TRUNCATE TABLE statement, but before the next backup, you could only restore up until 9:15.
I totally agree.
Given the wording of the question, the TRUNCATE TABLE was the trigger to perform the restore (your reaction time as a DBA in an emergency is under scrutiny, and data loss (by deleting the contents of an entire table) would constitute an emergency)--so the restore should have happened anywhere between 9:20 and 9:30 (excluding start and end time). And without backing up the log tail restore will only include everything till 9:15.
Anyway, this teaches me to make more assumptions; otherwise good question and even better discussion.
(In retrospect, the choices of answers given are leaning towards the explanation given...not towards my interpretation...)
Thanks,
Michael
March 9, 2011 at 8:34 am
SKYBVI (3/9/2011)
Good questioni would like to ask, we can restore at 9:15 right?
But not after that?
Regards,
Sushant
I was expecting someone else to point this out, but I didn't see it so...
You can restore your data right up to any point in time, up to 9:19 before the truncation or to 9:21 after the truncation and so on BECAUSE you have a full backup and trans log backups up to the last trans log before your point in time restore, in this case 9:15, and the important part that many DBA's are unaware of, the CURRENT log, which you backup (tail of the log) BEFORE you start your restore process, depending on the type of restore you are doing, which is a whole other topic when talking about tail log backups and "partial" restores 🙂
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
March 9, 2011 at 8:47 am
alen teplitsky (3/9/2011)
if the TRUN CATE TABLE command is logged, why can't you run it on a table that is published for replication?
Essentially, TRUN CATE TABLE simply invalidates all of the pages related to the table in one operation with no row-level checking. Since replication acts on row-level processing, a table-level command like this is not allowed. Same reason you can't do it on tables with foreign keys and it won't fire any triggers. Also the same reason it's generally so much faster than DEL ETE
[Sorry for the extraneous spaces -- stupid IPS can't tell the difference between a comment and an attack]
March 9, 2011 at 9:02 am
Steve Jones - SSC Editor (3/9/2011)
It's clear to me, and if you think there is some implication that you are restoring to some specific point in time, you are reading something into the question that is not there. The question asks simply if you can restore to a point in time, and you can, based on the transaction log backups. The truncate statement is listed to see if you think this affects your log backup/recovery, which it doesn't.I'm sure you've read something about this on the Internet, but it's not true.
Thanks Steve. That's sums up my intentions when writing the question.
March 9, 2011 at 9:10 am
Over-thought this one and missed it. I should have gone with my gut.... Doh!
March 9, 2011 at 9:10 am
sknox (3/9/2011)
alen teplitsky (3/9/2011)
if the TRUN CATE TABLE command is logged, why can't you run it on a table that is published for replication?Essentially, TRUN CATE TABLE simply invalidates all of the pages related to the table in one operation with no row-level checking. Since replication acts on row-level processing, a table-level command like this is not allowed. Same reason you can't do it on tables with foreign keys and it won't fire any triggers. Also the same reason it's generally so much faster than DEL ETE
[Sorry for the extraneous spaces -- stupid IPS can't tell the difference between a comment and an attack]
that's why i think this is a bogus question. technically everything in SQL is logged or whatever because the system has to execute it and it has to know what to execute against. but practically this is not a logged operation.
i clicked the SQL Skills link expecting some cool info on how to recover the lost data, but no such luck
March 9, 2011 at 10:00 am
alen teplitsky (3/9/2011)
sknox (3/9/2011)
alen teplitsky (3/9/2011)
if the TRUN CATE TABLE command is logged, why can't you run it on a table that is published for replication?Essentially, TRUN CATE TABLE simply invalidates all of the pages related to the table in one operation with no row-level checking. Since replication acts on row-level processing, a table-level command like this is not allowed. Same reason you can't do it on tables with foreign keys and it won't fire any triggers. Also the same reason it's generally so much faster than DEL ETE
[Sorry for the extraneous spaces -- stupid IPS can't tell the difference between a comment and an attack]
that's why i think this is a bogus question. technically everything in SQL is logged or whatever because the system has to execute it and it has to know what to execute against. but practically this is not a logged operation.
i clicked the SQL Skills link expecting some cool info on how to recover the lost data, but no such luck
It is a logged operation, just not row-level-logged. All of the information needed to restore is there (i.e, to restore before the truncate, you roll forward the transaction log to the point before the truncate happened.) The question was concerning restoring to any point in time, which is still possible. If it were NOT a logged operation, it would not be possible, because restoring to after the truncate would not include the truncate.
For more information about point-in-time restore, a good starting point is here: http://msdn.microsoft.com/en-us/library/ms190982.aspx
Viewing 15 posts - 31 through 45 (of 53 total)
You must be logged in to reply to this topic. Login to reply