Restore

  • free_mascot

    One Orange Chip

    Points: 27168

    Comments posted to this topic are about the item Restore

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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.

  • HanShi

    SSC-Dedicated

    Points: 33358

    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.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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.

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286985

    I learned something new today as well. Thanks and everyone have a great weekend.

  • m mcdonald

    Hall of Fame

    Points: 3180

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

  • Dana Medley

    SSCertifiable

    Points: 6764

    Ed Wagner (6/6/2014)


    I learned something new today as well. Thanks and everyone have a great weekend.

    +1



    Everything is awesome!

  • RLilj33

    SSCrazy

    Points: 2153

    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!;-)

  • abjames

    Old Hand

    Points: 332

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

  • SQLRNNR

    SSC Guru

    Points: 281252

    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

  • Ken Wymore

    SSCoach

    Points: 16642

    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!

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • TomThomson

    SSC Guru

    Points: 104773

    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 18 total)

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