Point-in-time restore

  • Duncan Pryde

    SSCertifiable

    Points: 7956

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

  • Nakul Vachhrajani

    SSChampion

    Points: 10160

    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
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • M&M

    SSC-Insane

    Points: 21679

    I messed up. Very good question.

    M&M

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Great question, thanks.

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

  • Andrew Watson-478275

    SSCarpal Tunnel

    Points: 4568

    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.

  • DugyC

    Hall of Fame

    Points: 3804

    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]

  • Duncan Pryde

    SSCertifiable

    Points: 7956

    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

  • palotaiarpad

    SSCarpal Tunnel

    Points: 4891

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

  • Surii

    Default port

    Points: 1477

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

  • Duncan Pryde

    SSCertifiable

    Points: 7956

    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

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

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

  • Patrick2525

    Mr or Mrs. 500

    Points: 547

    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.

  • SQL_Hunt

    SSC-Dedicated

    Points: 33267

    Was really the question framed correctly?

    Thanks.

  • Duncan Pryde

    SSCertifiable

    Points: 7956

    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

  • Duncan Pryde

    SSCertifiable

    Points: 7956

    Sourav-657741 (2/16/2011)


    Was really the question framed correctly?

    Can you be more specific?

    Duncan

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

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