Point-in-time restore

  • Comments posted to this topic are about the item Point-in-time restore

  • Good one! I had to refer BOL for this one because we don't use Bulk-logged mode, but it was fun learning something new today. Thank-you!

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • I messed up. Very good question.

    M&M

  • Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Nakul Vachhrajani (2/15/2011)


    I had to refer BOL for this one because we don't use Bulk-logged mode.

    Sadly, my BOL diving didn't go deep enough. I got to here: http://msdn.microsoft.com/en-us/library/ms189275(SQL.100).aspx, which states twice that it's not supported - you have to go a bit further to find where it says that it is.

  • Having not used Bulk options, I managed to do enough digging to find out it was a conditional yes, just chose the wrong one. Thought you would be okay as long as there was no bulk operations since last full backup.

    I guess not :ermm:

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • DugyC (2/16/2011)


    Having not used Bulk options, I managed to do enough digging to find out it was a conditional yes, just chose the wrong one. Thought you would be okay as long as there was no bulk operations since last full backup.

    I guess not :ermm:

    You would indeed be ok, but the answer said "Yes, but only if.." which I intended to mean that if there had been no bulk operations since the last full backup you would be ok, but otherwise not.

    I was thinking of a situation where you might have multiple log backups since the last full backup, several of which might contain bulk operations. As long as the log backup you want to restore to a point in time with doesn't have any, you can restore to that particular point in time. Of course, if none of them have any bulk operations in them you can use any of them to restore to a point in time.

    I apologise for any confusion caused - but as you can imagine, wording something to be completely unambiguous is not as easy as it seems!

    Thanks for the feedback

    Duncan

  • It's a good question, but maybe next time you should take a notice: using Server 2005 and above.

  • Nice question!! I had to search for the correct answer.

  • palotaiarpad (2/16/2011)


    It's a good question, but maybe next time you should take a notice: using Server 2005 and above.

    The question is valid equally for SQL Server 2000/2005/2008. I'm not sure if the exact STOPAT syntax was available in SQL Server 7 (point-in-time recovery was, so I assume yes?), but in any case there was no BULK_LOGGED recovery model as such. Instead there was the db option "select into/bulkcopy" which could be set to "ON/TRUE" to achieve the same effect. However, since the question refered to bulk-logged and recovery models it could be assumed that it related to SQL 2000 and later.

    Cheers,

    Duncan

  • It is always a good question when I have to do a little reading to get the answer. Thanks.

  • Well, thanks for the clarification. I was studying for the MCTS exam using 'The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit: Database Implementation and Maintenance' by Mark Horninger (2009) which stated it clear as mud:

    The Bulk-Logged recovery model is similar to Full recovery model, except that nonlogged operations are performed as nonlogged. This provides a performance advantage for Bulk-Logged operations. However, if a Bulk-Logged operation has occurred since the last full backup, you will not be able to recover any changes made since the last full backup. The Bulk-Logged recovery model does not support point-in-time recovery.

  • Was really the question framed correctly?

    Thanks.

  • Patrick2525 (2/16/2011)


    Well, thanks for the clarification. I was studying for the MCTS exam using 'The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit: Database Implementation and Maintenance' by Mark Horninger (2009) which stated it clear as mud:

    The Bulk-Logged recovery model is similar to Full recovery model, except that nonlogged operations are performed as nonlogged. This provides a performance advantage for Bulk-Logged operations. However, if a Bulk-Logged operation has occurred since the last full backup, you will not be able to recover any changes made since the last full backup. The Bulk-Logged recovery model does not support point-in-time recovery.

    Wow - that's wrong on two counts. First is that even if bulk-logged operations have occurred since the full backup, you can still recover to the end of a log backup, and second is the bit you highlighted.

    Although I haven't done that exam myself as I upgraded directly from 2005 to 2008 MCITP, most of the Admin exams I've done have contained a fair amount of backup and recovery questions, so it does pay to know your stuff.

    Duncan

  • Sourav-657741 (2/16/2011)


    Was really the question framed correctly?

    Can you be more specific?

    Duncan

Viewing 15 posts - 1 through 15 (of 43 total)

You must be logged in to reply to this topic. Login to reply