Restore

  • SQLRNNR

    SSC Guru

    Points: 281252

    Great question.

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Interesting, that the documentation (or at least the page referenced in this question) never once mentions filegroup restore. I thought that filegroup restore was possible, but (since DBA is not my primary task) decided to check BOL first. I came across the exact page referenced in the answer, noticed no mention of restoring at the filegroup level, so decided to untick this option.

    Ah well. Points lost, but lesson learned - now I hope I'll never have the opportunity to reinforce this knowledge in my job. 😉


    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/

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Hugo Kornelis (1/19/2011)


    Interesting, that the documentation (or at least the page referenced in this question) never once mentions filegroup restore.

    http://msdn.microsoft.com/en-us/library/ms186858%28v=sql.90%29.aspx

    About 1/3 of the way down:

    SQL Server 2005 supports a variety of restore scenarios:

    * Complete database restore

    Restores the entire database, beginning with a full database backup, which may be followed by restoring a differential database backup (and log backups). For more information, see Performing a Complete Database Restore (Simple Recovery Model) or Performing a Complete Database Restore (Full Recovery Model).

    * File restore

    Restores a file or filegroup in a multi-filegroup database. Note that under the simple recovery model, the file must belong to a read-only filegroup. After a full file restore, a differential file backup can be restored. For more information, see Performing File Restores (Full Recovery Model) and Performing File Restores (Simple Recovery Model).

    * Page restore

    Restores individual pages. Page restore is available only under the full and bulk-logged recovery models. For more information, see Performing Page Restores.

    Also http://msdn.microsoft.com/en-us/library/ms178615%28v=sql.90%29.aspx under the heading "<file_or_filegroup_or_page>"

    It's not particularly clear or obvious.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Thanks, Gail.

    So apparently, the documentation is not incomplete, it just hides it's completeness very well 😉


    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/

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721419

    Hugo Kornelis (1/19/2011)


    Thanks, Gail.

    So apparently, the documentation is not incomplete, it just hides it's completeness very well 😉

    I think more that it's poorly written. I miss the "submit a note" item at the top right for the online BOL

  • Hit The Buffers

    SSC Rookie

    Points: 44

    Bit pedantic I know, but Gail is quoting 2005 documentation and the question was 2008 R2 specific. The referenced documentation initially states database, data file and data page as supported data restore levels (which is how I missed file group :crying:) - however, in the 'Restore Scenarios' section, file groups are mentioned under 'Piecemeal restore'. Serves me right for not reading the whole page before answering the question 😀

  • SanDroid

    SSChampion

    Points: 10068

    Steve Jones - SSC Editor (1/19/2011)


    I miss the "submit a note" item at the top right for the online BOL

    So do I. Unfortunate that a large percentage of the submissions where hate mail or links to sites selling p#n%$ enlargement.

  • SanDroid

    SSChampion

    Points: 10068

    GilaMonster (1/17/2011)


    SanDroid (1/17/2011)


    Not certain who is learning.

    Restoring a group of pages can restore an extent.

    There is no RESTORE DATABASE ... EXTENT command. There is a RESTORE DATABASE ... FILEGROUP, a RESTORE DATABASE ... FILE and a RESTORE DATABASE ... PAGE

    That is understood an not relevant in the least to my point.

    The question was the types of restore supported, not the restore commands supported. Wording is everyting in a question and it's answer.

    When you have "database extent corrupted or unavailable" errors you use RESTORE PAGE to resolve it.

    I guess the fact that I had to do this to a database altered my perception of what would be the correct answer.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721419

    SanDroid (1/20/2011)


    That is understood an not relevant in the least to my point.

    The question was the types of restore supported, not the restore commands supported. Wording is everyting in a question and it's answer.

    When you have "database extent corrupted or unavailable" errors you use RESTORE PAGE to resolve it.

    I guess the fact that I had to do this to a database altered my perception of what would be the correct answer.

    If you took that wording to mean how can I extend things to restore an extent, I think you are misreading the question yourself. You are overthinking the process.

    Restoring an extent is not possible. Restoring pages to rebuild an extend is possible, but that's not the same thing. Saying this is the same is playing with semantics.

  • UMG Developer

    SSChampion

    Points: 13482

    Thanks for the question!

  • SanDroid

    SSChampion

    Points: 10068

    Steve Jones - SSC Editor (1/20/2011)


    SanDroid (1/20/2011)


    That is understood an not relevant in the least to my point.

    The question was the types of restore supported, not the restore commands supported. Wording is everyting in a question and it's answer.

    When you have "database extent corrupted or unavailable" errors you use RESTORE PAGE to resolve it.

    I guess the fact that I had to do this to a database altered my perception of what would be the correct answer.

    If you took that wording to mean how can I extend things to restore an extent, I think you are misreading the question yourself. You are overthinking the process.

    Restoring an extent is not possible. Restoring pages to rebuild an extend is possible, but that's not the same thing. Saying this is the same is playing with semantics.

    My point was that resotration of an Extent is something paossible using page restores, just like piecmeal restores offer us other possibilities. This is something that does not seem to be well understood. I apreciate the fact that you do. 😀

  • SanjayAttray

    SSChampion

    Points: 13157

    Tom.Thomson (1/15/2011)


    Nice easy question, nice clean answer.

    But why on earth is it worth 2 points?

    That's because 1% of total answered still are not sure about full backup restores. I understand if some people miss file restores as they are not regular job but, people still are out there that believes FULL backup restore is not possible.

    what types of restores are possible?

    SQL DBA.

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Good question.

    I don't know that we can restore single page as well.

    Thanks for the question & nice discussion about the topic.

    Thanks

  • zymos

    SSCommitted

    Points: 1960

    Nice question and tricky topic to many, especially beginners and it is well explained.

    Thanks.

    URL: http://msdn.microsoft.com/en-us/library/ms186858.aspx

Viewing 14 posts - 16 through 29 (of 29 total)

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