Post reply

restoring from a database mirror

  • June 1, 2009 at 6:53 pm

    #204959

    Hi Folks,

    If a page in a data file becomes corrupted could you restore from a database mirror if you have one setup rather than going from backup - I assume you could as my understanding is that the sql statement is what get shipped to the mirror - not the data.

  • June 2, 2009 at 2:10 am

    #1003224

    In 2008, SQL can 'repair' damaged pages from the mirror automatically. Enterprise edition only.

    Books Online:

    A database mirroring partner running on SQL Server 2008 Enterprise or later versions automatically tries to resolve certain types of errors that prevent reading a data page. The partner that is unable to read a page requests a fresh copy from the other partner. If this request succeeds, the unreadable page is replaced by the copy, which usually resolves the error.

    If you're using standard edition, I think you'd have to use backups (though you could take a backup of the mirror and use that). Doesn't have to be the entire database, a single page restore should work.

    Not all pages types can be repaired from the mirror and not all page types are eligible for single page restore.

    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

  • June 19, 2009 at 1:38 pm

    #1012642

    bodhilove (6/1/2009)

    Hi Folks,

    If a page in a data file becomes corrupted could you restore from a database mirror if you have one setup rather than going from backup - I assume you could as my understanding is that the sql statement is what get shipped to the mirror - not the data.

    I dont think there is a restore option available from mirrored database...though you can do below...

    1. switch failover to mirror

    2. break mirroring

    3. Take new backup and configure mirroring again (from mirror db backup)

    4. switch failover again

    Hope it helps...Prakash

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

Viewing 3 posts - 1 through 2 (of 2 total)

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