June 6, 2014 at 12:46 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 6, 2014 at 2:55 am
Thank you for the post, free_mascot, interesting one. (couple of days ago, was searching the google for back/restore articles any newly written... and I came across one where this word "piecemeal" was mentioned, though I did not read it what it was about, but my first impression was some kind of made up name... and weird for me to find an entry in the local_help index... well ...:w00t: )
In the local help, at first it says
"This topic is relevant only for databases in the Enterprise edition of SQL Server that contain multiple files or filegroups; and, under the simple model, only for read-only filegroups."
and then...
"Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model. "
failed to co-relate them. One day I will see these in action, I suppose, as restoring FGs... in a decent and consistent way... should become a default practice.. 🙂
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
June 6, 2014 at 3:33 am
I was in doubt, because I found it difficult to get the intention of the question (perhaps because I'm not native English speaking). Did I need to take the question literally ("database needs to go offline when starting a restore") or do I need to take the question in general ("database needs to be offline during all the restore sequenses).
So I chose the wrong answer....
The linked article states:
•Online piecemeal restore scenario
In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.
June 6, 2014 at 4:14 am
HanShi (6/6/2014)
I was in doubt, because I found it difficult to get the intention of the question (perhaps because I'm not native English speaking). Did I need to take the question literally ("database needs to go offline when starting a restore") or do I need to take the question in general ("database needs to be offline during all the restore sequenses).So I chose the wrong answer....
The linked article states:
•Online piecemeal restore scenario
In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.
(just trying to add in something..)
I guess because of this, as in it says "Online piecemeal restores can involve deferred transactions", so possibly involves higher risk because if more than one FG exists and only PRI FG is restored. In offline there is no risk as the db is offline and user or app cannot access and a DBA can make sure that before it goes online everything is prefect. So I think, by default they go Offline and in very rare circumstances (which I am not sure) they choose Online.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
June 6, 2014 at 4:21 am
This was removed by the editor as SPAM
June 6, 2014 at 5:32 am
I learned something new today as well. Thanks and everyone have a great weekend.
June 6, 2014 at 6:51 am
HanShi (6/6/2014)
I was in doubt, because I found it difficult to get the intention of the question (perhaps because I'm not native English speaking). Did I need to take the question literally ("database needs to go offline when starting a restore") or do I need to take the question in general ("database needs to be offline during all the restore sequenses).So I chose the wrong answer....
The linked article states:
•Online piecemeal restore scenario
In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.
+1
Found the article before hand and answered the same way...
June 6, 2014 at 7:29 am
Ed Wagner (6/6/2014)
I learned something new today as well. Thanks and everyone have a great weekend.
+1
June 6, 2014 at 7:45 am
HanShi (6/6/2014)
I was in doubt, because I found it difficult to get the intention of the question (perhaps because I'm not native English speaking). Did I need to take the question literally ("database needs to go offline when starting a restore") or do I need to take the question in general ("database needs to be offline during all the restore sequenses).So I chose the wrong answer....
I had the same question as you, HanShi, and I am a native English speaker. 🙂
I had to do some reading and was able to find the topic in BOL. The 5th paragraph, 3rd sentence reads "During the piecemeal-restore sequence, the whole database must go offline."
See the topic, "Online piecemeal restore scenario". I believe the process is: Entire database goes offline, Primary filegroup is restored, Primary filegroup comes back online, Subsequent filegroups can be brought online one at a time.
I have not performed a piecemeal restore, so those more familiar - please make the necessary annotations!;-)
June 6, 2014 at 8:24 am
RLilj33 (6/6/2014)
HanShi (6/6/2014)
I was in doubt, because I found it difficult to get the intention of the question (perhaps because I'm not native English speaking). Did I need to take the question literally ("database needs to go offline when starting a restore") or do I need to take the question in general ("database needs to be offline during all the restore sequenses).So I chose the wrong answer....
I had the same question as you, HanShi, and I am a native English speaker. 🙂
I had to do some reading and was able to find the topic in BOL. The 5th paragraph, 3rd sentence reads "During the piecemeal-restore sequence, the whole database must go offline."
See the topic, "Online piecemeal restore scenario". I believe the process is: Entire database goes offline, Primary filegroup is restored, Primary filegroup comes back online, Subsequent filegroups can be brought online one at a time.
I have not performed a piecemeal restore, so those more familiar - please make the necessary annotations!;-)
Seems like lots of us a) have never done a piecemeal restore and b) don't read the entire topic when searching the online help. I guess I should have searched for "piecemeal" and then searched within the page for "offline" to get the right answer. 🙂
June 6, 2014 at 10:21 am
RLilj33 (6/6/2014)
HanShi (6/6/2014)
I was in doubt, because I found it difficult to get the intention of the question (perhaps because I'm not native English speaking). Did I need to take the question literally ("database needs to go offline when starting a restore") or do I need to take the question in general ("database needs to be offline during all the restore sequenses).So I chose the wrong answer....
I had the same question as you, HanShi, and I am a native English speaker. 🙂
I had to do some reading and was able to find the topic in BOL. The 5th paragraph, 3rd sentence reads "During the piecemeal-restore sequence, the whole database must go offline."
See the topic, "Online piecemeal restore scenario". I believe the process is: Entire database goes offline, Primary filegroup is restored, Primary filegroup comes back online, Subsequent filegroups can be brought online one at a time.
I have not performed a piecemeal restore, so those more familiar - please make the necessary annotations!;-)
The wording of the question did leave plenty of room for confusion.
But yes, the piecemeal restore is as you said.
The database is offline momentarily while the primary filegroup is restored. This is a good reason to leave your primary filegroup very small.
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
June 6, 2014 at 11:55 am
I had no idea that piecemeal restores were a real option. I thought it was slang for some sort of other restore process. Learned something new today. Thanks for the question!
June 7, 2014 at 8:10 am
RLilj33 (6/6/2014)
I believe the process is: Entire database goes offline, Primary filegroup is restored, Primary filegroup comes back online, Subsequent filegroups can be brought online one at a time.I have not performed a piecemeal restore, so those more familiar - please make the necessary annotations!;-)
That is correct.
And concerning an earlier question: piecemeal restore is (a) only available if you use multiple filegroups, and (b) for databases in simple recovery the additional requirement is that at least some of those filegroups are readonly (and only those filegroups can benefit from the piecemeal restore process).
So a simple recovery database with only read-write filegroups cannot benefir from this feature.
June 7, 2014 at 1:59 pm
I too wondered what the question was intended to mean. Out of various options I selected one of the wrong ones - namely, "is online peicemeal restore (as opposed to offline piecemeal restore) unsupported". The BOL page referrred to in the explanation states that online piecemeal restore is supported in Enterprise edition and not in other editions (probably a BOL error given that evaluation and developer editions normally contain all the enterprie edition stuff), so the answer is "No it isn't unsupported", ie translated as "no" for what I thought the question meant. But the question actually intended was presumably "Does the database have to go offline at some point in an online piecemeal restore", and in all fairness to the question's author that's what I should have realised it meant when I first read it - and I don't have the excuse of not having native-level capability in English.
Tom
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply