March 9, 2011 at 3:54 am
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
March 9, 2011 at 5:31 am
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://qa.sqlservercentral.com/articles/Editorial/72676/[/url]
March 9, 2011 at 5:58 am
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.
March 9, 2011 at 6:03 am
Trick Question and answer
March 9, 2011 at 6:07 am
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
March 9, 2011 at 6:29 am
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.
March 9, 2011 at 6:29 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.
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.
March 9, 2011 at 6:36 am
Jim Sleeman-388184 (3/9/2011)
Trick Question and answer
It certainly wasn't meant to be. Thanks for the feedback though.
March 9, 2011 at 6:37 am
SKYBVI (3/9/2011)
Good questioni 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.
March 9, 2011 at 6:40 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.
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
March 9, 2011 at 6:42 am
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!
March 9, 2011 at 6:44 am
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
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
March 9, 2011 at 6:53 am
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. 😉
March 9, 2011 at 7:18 am
Duncan Pryde (3/9/2011)
SKYBVI (3/9/2011)
Good questioni 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
March 9, 2011 at 7:42 am
SKYBVI (3/9/2011)
Duncan Pryde (3/9/2011)
SKYBVI (3/9/2011)
Good questioni 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