Restoring after TRUNCATE TABLE

  • paul.knibbs (3/9/2011)


    This is odd. I'm sure I've read stuff that implies TRUNCATE TABLE *does* invalidate log backups until the next full backup is run, which means none of the suggested answers was right--yet everyone is saying this is incorrect and that point-in-time restore is still perfectly possible after doing one of these. I'm confused!

    Were you referring to this statement 'backup log with truncate_only'. Running this would invalidate the log backups and we would need to take a full backup again to overcome this. As per my knowledge, a truncate statement wouldn't invalidate log backups.

    M&M

  • mohammed moinudheen (3/9/2011)


    paul.knibbs (3/9/2011)


    This is odd. I'm sure I've read stuff that implies TRUNCATE TABLE *does* invalidate log backups until the next full backup is run, which means none of the suggested answers was right--yet everyone is saying this is incorrect and that point-in-time restore is still perfectly possible after doing one of these. I'm confused!

    Were you referring to this statement 'backup log with truncate_only'. Running this would invalidate the log backups and we would need to take a full backup again to overcome this. As per my knowledge, a truncate statement wouldn't invalidate log backups.

    Apparently there did use to be non-logged operations in SQL Server 7, but TRUNCATE TABLE wasn't one of them. These days there are no non-logged operations as far as I'm aware.

    Link: http://support.microsoft.com/kb/272093

    Also, see Steve's editorial from yesterday: http://www.sqlservercentral.com/articles/Editorial/72676/[/url]

  • 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.

  • Trick Question and answer

  • Good question

    i would like to ask, we can restore at 9:15 right?

    But not after that?

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • If your server blew up and all you had was the 9:15 log backup, that's as far as you could go. If you took another log backup after the TRUNCATE or waiting for the scheduled one to kick off at 9:30, then you could restore to a point in time just before the TRUNCATE. Like I said, I think the question just needed to be a little more clear.

  • 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.

  • Jim Sleeman-388184 (3/9/2011)


    Trick Question and answer

    It certainly wasn't meant to be. Thanks for the feedback though.

  • SKYBVI (3/9/2011)


    Good question

    i would like to ask, we can restore at 9:15 right?

    But not after that?

    Regards,

    Sushant

    No, TRUNCATE TABLE doesn't affect point-in-time restore, so you can restore to any time you like as long as you have the log backups.

  • 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.

    When you're writing a question you try and think of how it will be read, but unfortunately it's very difficult to anticipate all interpretations. I should have probably added something about the time now being 9:45 or something, but you live and learn. 😎

    Duncan

  • It's true. Besides, it's causing a good amount of discussion here about how point-in-time restores work, so even if it wasn't clear up front, people are still learning. Thanks for the question!

  • Great Question! Paul's Myth a day pdf is available for Download. It is well worth the read!

    http://www.sqlskills.com/BLOGS/PAUL/post/Myths-and-misconceptions-60-page-PDF-of-blog-posts.aspx

  • mvelic (3/9/2011)


    It's true. Besides, it's causing a good amount of discussion here about how point-in-time restores work, so even if it wasn't clear up front, people are still learning. Thanks for the question!

    No problem. As long as a questions generates a good discussion, it's served some of its purpose as you say. I'm getting quite into this question-writing lark, so all feedback - positive or "constructively critical" - does help. 😉

  • Duncan Pryde (3/9/2011)


    SKYBVI (3/9/2011)


    Good question

    i would like to ask, we can restore at 9:15 right?

    But not after that?

    Regards,

    Sushant

    No, TRUNCATE TABLE doesn't affect point-in-time restore, so you can restore to any time you like as long as you have the log backups.

    u can restore at 9:30 too, but then wat abt the rows being deleted, if we want to have the data back, then we have to restore it at 9:15 ??

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • SKYBVI (3/9/2011)


    Duncan Pryde (3/9/2011)


    SKYBVI (3/9/2011)


    Good question

    i would like to ask, we can restore at 9:15 right?

    But not after that?

    Regards,

    Sushant

    No, TRUNCATE TABLE doesn't affect point-in-time restore, so you can restore to any time you like as long as you have the log backups.

    u can restore at 9:30 too, but then wat abt the rows being deleted, if we want to have the data back, then we have to restore it at 9:15 ??

    Regards,

    Sushant

    The question was asking if you could restore to any given point in time. The answer is that you can, because TRUNCATE table doesn't affect point-in-time restore.

    Obviously, if you restored to 9:19 you'd have the data, and if you restored to 9:21 you wouldn't. Does that make sense?

Viewing 15 posts - 16 through 30 (of 53 total)

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