Restoring after TRUNCATE TABLE

  • Nice question, thanks!

    Though I probably would have asked about it in the BULK LOGGED recovery mode, as that would have made it more likely that a TRUNCATE TABLE might be a bulk operation that would prevent a point-in-time restore. (Within that one log backup.)

  • This should be an easy two points, but I have encountered far too many people that think there is no recovery possible upon truncating a table. It is very hard to reverse that thinking - even after showing them examples.

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Good question. Please don't hesitate to submit more. I speak for the 62% who read the question as you intended and answered correctly. Our knowledge was reinforced. The folks who answered incorrectly but understood the question as worded really learned something today!

  • wware (3/9/2011)


    Good question. Please don't hesitate to submit more. I speak for the 62% who read the question as you intended and answered correctly. Our knowledge was reinforced. The folks who answered incorrectly but understood the question as worded really learned something today!

    I couldn't ask for a nicer comment than that. Thanks!

  • I answered wrong but it was a good question. I brainfarted like a few others and went after the 'data', not the log... so you could restore anytime up till 9:20 in my mind. Would have helped if I had simply read the question twice, 'neh?

    Ah well, thanks. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • nice question.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • mohammed moinudheen (3/9/2011)


    Thanks for the question Duncan. I had a feeling that this question was more at testing our understanding of the truncate statement than the recovery models.

  • Duncan Pryde (3/9/2011)Hi - I think you may have overthought things a bit - it wasn't meant to be a complicated or trick question, but just to find out if people thought that TRUNCATE TABLE somehow affected the validity of the transaction log. It doesn't.

    Thanks for the feedback though, maybe I should have made the question clearer somehow.

    Duncan

    Acchkkk!!! I over thought it too! :w00t: Just as I hit the answer I thought, " I'm over thinking this, aren't I?" Next time. 😎

  • Nice trick question!

Viewing 9 posts - 46 through 53 (of 53 total)

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