Post reply

Restore Multiple pages

  • April 2, 2009 at 10:47 am

    #201776

    for a single page to restore it is:

    RESTORE DATABASE database PAGE='1:189'

    FROM DISK='path'

    GO

    How do you restore multiple pages if you have corruption in multiple pages?

  • April 2, 2009 at 10:54 am

    #970933

    Or is it that you can restore multiple pages this way:

    RESTORE DATABASE database PAGE='1:189'

    FROM DISK='path'

    GO

    RESTORE DATABASE database PAGE='1:190'

    FROM DISK='path'

    GO

    RESTORE DATABASE database PAGE='1:191'

    FROM DISK='path'

    GO

    and then restore a transaction log?

  • April 2, 2009 at 11:25 am

    #970949

    RESTORE DATABASE database PAGE='1:189, 1:190, 1:191'

    FROM DISK='path'

    GO

    The maximum number of pages that may be restored in a single restore statement is 1000. See the page in Books Online titled "Performing Page Restores"

    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

  • April 2, 2009 at 11:29 am

    #970955

    Great ! thanks a lot Gail

  • April 6, 2009 at 9:34 pm

    #973054

    hi,

    i've doubt how can i detect that the particular page is corrupted.

    🙂

  • April 7, 2009 at 3:00 am

    #973164

    That's what CheckDB's there for. Preferably run regularly.

    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

  • April 7, 2009 at 4:40 am

    #973205

    vrabhadram (4/6/2009)

    hi,

    i've doubt how can i detect that the particular page is corrupted.

    By running regular DBCC commands as part of your maintenance plan.

    You could also check suspect_pages table in the msdb database, each row represents a suspect page

  • April 7, 2009 at 7:22 am

    #973327

    Krishna Potlakayala (4/7/2009)

    You could also check suspect_pages table in the msdb database, each row represents a suspect page

    Yes, but that assumes that SQL's encountered the suspect page. If checkDB is never run, it may be weeks or months after the corruption occurred that a query encounters the bad page and writes into suspect_pages. By that time the preferred methods of corruption won't be an option because last clean backup is far too old to restore from.

    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

Viewing 8 posts - 1 through 7 (of 7 total)

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