February 20, 2012 at 9:21 pm
Comments posted to this topic are about the item Database Restore
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
February 20, 2012 at 9:45 pm
Quick, straight forward and easy. Love the back to basic questions.
We often forget the core stuff.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 20, 2012 at 10:39 pm
Thanks for easy question 🙂
M&M
February 20, 2012 at 11:36 pm
Good and Easy Question. Thanks. 😀
February 20, 2012 at 11:58 pm
Nice question, but I think you can restore to a point-int-time in the Bulk Logged Recovery Model as long as no bulk logged operations are present in the backup.
MSDN:
Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. The database must be recovered to the end of the transaction log backup.
Recovering to a Specific Point in Time
The statement is a bit open for interpretation, but it makes me believe it should be possible. Any comments?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 21, 2012 at 1:16 am
I agree with Koen--you can do a point-in-time restore in Bulk Logged mode so long as there are no bulk-logged operations at the point you want to do the restore to (it's perfectly fine if there are bulk-logged operations in the backup set as a whole, so long as they're not happening at that point!).
February 21, 2012 at 2:11 am
This was removed by the editor as SPAM
February 21, 2012 at 2:14 am
love it back to basics!!!
February 21, 2012 at 2:51 am
Good and easy question. Thanks.
I'm surprised that 21% believes that you can do a point in time restore of a database in simple recovery.
February 21, 2012 at 3:20 am
The explanation is not true. It says "Only the Full model is capable of supporting a point in time restore.", however bulk-logged recovery is fully capable of supporting point in time restores, and it also requires log backups.
The only limitation on point-in-time restores in bulk-logged recovery model is that a log interval that contains a bull-logged operation cannot be restored partially.
So say we have this scenario and the DB is in bulk-logged recovery
08h00 log backup
09h00 log backup
09h45 minimally logged operation (select into)
10h00 log backup
11h00 log backup
Then that database can be restored to any point in time between 08h00 and 09h00 or any point in time between 10h00 and 11h00. The only limitation is that, because of the minimally logged operation in the 09h00-10h00 log interval, that log backup can only be restored in its entirety (to 10h00) or not at all.
I'd reference an article, but it hasn't been published yet. Refer to Kalen Delaney's SQL Server 2008 Internals, or Books Online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2012 at 4:10 am
Apologies for quoting Microsoft and getting it wrong !! :w00t:
I know that they are not perfect, but I would have thought that they would have got this right in the source I quoted.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
February 21, 2012 at 4:26 am
The quote is correct, but not complete. If it had said "Only the Full model is capable of supporting a point in time restore regardless of operations performed", then it would be more complete.
I think the key reference for the bulk-logged behaviour should be http://msdn.microsoft.com/en-us/library/ms190692.aspx
Two key points
Much of the description of backup under the full recovery model also applies to the bulk-logged recovery model. This topic looks only at considerations that are unique to the bulk-logged recovery model.
and
If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.
So if there are no bulk-logged (minimally logged) operations within a log backup, then the behaviour is the same as in full recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2012 at 4:28 am
Out of interest, what happens if you *TRY* to do a point-in-time restore to somewhere inside a log backup containing bulk-logged operations? It's not like you'll be able to determine this before you attempt the process, after all.
February 21, 2012 at 4:33 am
paul.knibbs (2/21/2012)
Out of interest, what happens if you *TRY* to do a point-in-time restore to somewhere inside a log backup containing bulk-logged operations? It's not like you'll be able to determine this before you attempt the process, after all.
You can determine it before you try. Both the backup header (RESTORE HEADERONLY) and the msdb backup tables have a column called something like HasBulkLoggedData. If that is 1 for a log backup, it's a log backup under bulk-logged recovery and contains some minimally logged operations.
As for trying to restore to point-in-time within that log backup
Msg 4341, Level 16, State 1, Line 2
This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2012 at 4:53 am
Thanks for the clarification Gail.
It's an odd situation, I've learnt from my own QOTD - normally I learn from others.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply