none
mdf file corruption on primary replica RRS feed

  • Question

  • Suppose I have a database have two file group . one of the file in the second filegroup is corrupt in the primary replica .

    I failover the AG to another replica .

    So now is it possible to restore the corrupt file only ?

    what if that's the same case happen in secondary replica ? can restore the single corrupt file from backup and do the recovery ?

    Saturday, March 28, 2020 10:51 AM

All replies

  • what about if the file is remove in primary replica ?
    Sunday, March 29, 2020 6:23 AM
  • In this case I think  you would need  to re-create AAG

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 29, 2020 6:34 AM
  • Here is my steps. where there file in the filegroup deleted. … I failover the replica to 2nd replica

    then in the original replica ….

    ALTER

    DATABASE[mytestdb] SETHADR off;----




    ALTER

    DATABASEmytestdb setoffline

    use

    master

    RESTORE

    DATABASEmytestdb file='mytestdb5'FROM  DISK='E:\dbbackup\mytestdb_full_backup_10.bak'WITHreplace,norecovery     --- Restore full backup one file only

    use

    master

    RESTORE

    DATABASEmytestdb FROM  DISK='E:\dbbackup\mytestdb_full_backup_10.bak'WITHreplace,norecovery     --- Restore full backup from the original primary


    GO



    restore

    logmytestdb from  disk='E:\dbbackup\mytestdb_log_backup_10_1.bak'withnorecovery --- Restore log backup from the original primary


    restore

    logmytestdb from  disk='E:\dbbackup\mytestdb_log_backup_10_2.bak'withnorecovery    ---log backup from current primary replica

    and there is error

    Processed 0 pages for database 'mytestdb', file 'mytestdb' on file 1.

    Processed 0 pages for database 'mytestdb', file 'mytestdb2' on file 1.

    Processed 0 pages for database 'mytestdb', file 'mytestdb5' on file 1.

    Processed 5 pages for database 'mytestdb', file 'mytestdb_log' on file 1.

    Msg 3456, Level 16, State 1, Line 462

    Could not redo log record (171:25999:1), for transaction ID (0:0), on page (1:6), allocation unit 6488064, database 'mytestdb' (database ID 7). Page: LSN = (171:25933:10), allocation unit = 6488064, type = 16. Log: OpCode = 7, context 24, PrevPageLSN: (171:25958:1). Restore from a backup of the database, or repair the database.

    Msg 3013, Level 16, State 1, Line 462

    RESTORE LOG is terminating abnormally.


     

    alter

    databasemytestdb sethadr availabilitygroup=[MYAG17]

    another error

    Msg 1412, Level 16, State 211, Line 465

    The remote copy of database "mytestdb" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

    Sunday, March 29, 2020 11:11 AM
  • anyone has the idea ?
    Sunday, March 29, 2020 4:28 PM
  • Hi sakurai_db,

    you may need do it on the current primary replica first. After closing the synchronization and remove the DB from HA, perform the restore operation on the primary and secondary replica, and finally put the database back into HA and start synchronization again.

    Best Regards,
    cris

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 30, 2020 9:38 AM
  • Here is my steps. where there file in the filegroup deleted. … I failover the replica to 2nd replica

    then in the original replica ….

    So which means we should not failover to 2nd replica ? and there will be error when joining back the original replica database after restore the missing file ??? but why?? I don't know why can't do that ?
    Monday, March 30, 2020 4:48 PM
  • Hi sakurai_db,

    To restore a database in AOAG, you need to first remove the db from AG on the current primary replica Regardless of failover,finally you can re-add it .
    refer to:
    https://techyaz.com/sql-server/alwayson/restore-database-participating-alwayson-availability-group/

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 31, 2020 1:59 AM
  • I can perform to restore the corrupt file on primary . and also perform restore the corrupt file on secondary

    But I there is error when I failover the db to secondary , and try to restore and join back the previous primary db to ag ..

    Tuesday, March 31, 2020 5:57 PM
  • Hi sakurai_db,

    Do you refer to the operation steps in this post.
    Please note that to recovery after the restore on the primary replica( WITH RECOVERY), and do not recovery after restore on the secondary replica( WITH NORECOVERY), and add the database to the AG on the primary replica.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 1, 2020 1:31 AM
  • have you see my steps below and see what actually the problem ?

    Here is my steps. where there file in the filegroup deleted. … I failover the replica to 2nd replica

    then in the original replica ….

    ALTER

    DATABASE[mytestdb] SETHADR off;----




    ALTER

    DATABASEmytestdb setoffline

    use

    master

    RESTORE

    DATABASEmytestdb file='mytestdb5'FROM  DISK='E:\dbbackup\mytestdb_full_backup_10.bak'WITHreplace,norecovery     --- Restore full backup one file only

    use

    master

    RESTORE

    DATABASEmytestdb FROM  DISK='E:\dbbackup\mytestdb_full_backup_10.bak'WITHreplace,norecovery     --- Restore full backup from the original primary


    GO



    restore

    logmytestdb from  disk='E:\dbbackup\mytestdb_log_backup_10_1.bak'withnorecovery --- Restore log backup from the original primary


    restore

    logmytestdb from  disk='E:\dbbackup\mytestdb_log_backup_10_2.bak'withnorecovery    ---log backup from current primary replica

    and there is error

    Processed 0 pages for database 'mytestdb', file 'mytestdb' on file 1.

    Processed 0 pages for database 'mytestdb', file 'mytestdb2' on file 1.

    Processed 0 pages for database 'mytestdb', file 'mytestdb5' on file 1.

    Processed 5 pages for database 'mytestdb', file 'mytestdb_log' on file 1.

    Msg 3456, Level 16, State 1, Line 462

    Could not redo log record (171:25999:1), for transaction ID (0:0), on page (1:6), allocation unit 6488064, database 'mytestdb' (database ID 7). Page: LSN = (171:25933:10), allocation unit = 6488064, type = 16. Log: OpCode = 7, context 24, PrevPageLSN: (171:25958:1). Restore from a backup of the database, or repair the database.

    Msg 3013, Level 16, State 1, Line 462

    RESTORE LOG is terminating abnormally.


     

    alter

    databasemytestdb sethadr availabilitygroup=[MYAG17]

    another error

    Msg 1412, Level 16, State 211, Line 465

    The remote copy of database "mytestdb" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

    Wednesday, April 1, 2020 8:20 AM
  • Hi sakurai_db,

    I noticed in your script that when you restore the log, the first backup you restored was from the original primary replica, and the second restore was from the current primary replica. These two backups did not form a continuous log chain, and this may be the cause of the error.

    Best Regard,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 1, 2020 10:00 AM
  • why it's not continuous ?

    'E:\dbbackup\mytestdb_full_backup_10.bak'    << Full backup from primary

    'E:\dbbackup\mytestdb_log_backup_10_1.bak' << Log backup from primary

    mdf corrupt and failover to secondary

    restore mdf file from 'E:\dbbackup\mytestdb_full_backup_10.bak'  << restore to previous primary

    restore log from 'E:\dbbackup\mytestdb_log_backup_10_1.bak'  << restore log to previous primary

    backup log 'E:\dbbackup\mytestdb_log_backup_10_2.bak' << in current primary

    restore log 'E:\dbbackup\mytestdb_log_backup_10_2.bak' << in previous primary

    Thursday, April 2, 2020 2:35 AM
  • Hi sakurai_db,

    why not perform a full and transaction log backups in current primary, and then restore to the previous primary with norecovery.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 2, 2020 2:44 AM
  • sometimes we need to save time..... restoring a file will be faster than restoring a whole database.
    Friday, April 3, 2020 4:13 AM
  • so anyway to solve this problem ?

    Sunday, April 5, 2020 10:37 AM