none
Online database with there are mdf/ndf are currupt RRS feed

  • Question

  • Suppose I am having a database with 2 filegroup

    primary and secondary .

    The secondary is read-only .

    If the file in the secondary filegroup is corrupt/delete. …. The database is now in recovery_pending .

    how to make the database online again , using the primary filegroup only . ….??

    Friday, March 27, 2020 10:52 AM

Answers

All replies

  • If it is an Enterprise Edition of Microsoft SQL Server you can do an online restore of files/filegroups/...

    https://docs.microsoft.com/de-de/sql/relational-databases/backup-restore/online-restore-sql-server?view=sql-server-ver15


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Friday, March 27, 2020 1:14 PM
  • the file is in this state (Recovery Pending) when one file in the secondary file group corrupt.

    Friday, March 27, 2020 3:44 PM
  • Let summarize my steps

     


    --- Make available


    backup

    databasedata22 todisk='E:\dbbackup\data22_backup9.bak'


    backup

    logdata22 todisk='E:\dbbackup\data22_backup9_log1.bak'



    --- take database offline and delete the file


     


    use

    master

    alter

    databasedata22 setoffline


     


    ---- Restore file upto lastest \   



    use

    master

    RESTORE

    DATABASE[Data22] file='Data22_1'FROM  DISK=N'E:\dbbackup\data22_backup9.bak'WITHreplace,norecovery     --- Restore full backup


    GO



    --- restore all available log backup norecovery


    restore

    log[Data22] fromdisk='E:\dbbackup\data22_backup9_log1.bak'withrecovery


    and show the following

    Processed 0 pages for database 'Data22', file 'Data22_1' on file 1.

    Processed 4 pages for database 'Data22', file 'Data22_log' on file 1.

    The roll forward start point is now at log sequence number (LSN) 109000000039400001. Additional roll forward past LSN 109000000041500001 is required to complete the restore sequence.

    This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.

    RESTORE LOG successfully processed 4 pages in 0.011 seconds (2.397 MB/sec).

    ANd the database keep in Restoring state!!!


    Friday, March 27, 2020 4:43 PM
  • anyone has the idea ?
    Saturday, March 28, 2020 8:14 AM
  • Hi Sakurai,

    I assume that it is way to complex to assist over a forum. I can refer to the - EXCELLENT - blog posts from Paul Randal here:

    https://www.sqlskills.com/blogs/paul/category/repair/

    I would strongly recommend to get through his recommendations and try to repair your database(s)


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    • Marked as answer by sakurai_db Sunday, March 29, 2020 11:05 AM
    Saturday, March 28, 2020 8:50 AM
  • You need one more log backup and one more restore of that log backup. If you post a *full* repro script (that I can execute) then I can see if I can complement with that.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by sakurai_db Sunday, March 29, 2020 11:06 AM
    Sunday, March 29, 2020 11:03 AM
    Moderator