locked
attach_rebuild_log not working on filestream actived databases RRS feed

  • Question

  • Hi, I use SQL Server 2014

    I have cleanly detached a database:

    USE master; GO ALTER DATABASE mtheory SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO

    sp_detach_db 'mtheory';

    I am able to attach the database with this statement:

    USE [master]
    GO
    CREATE DATABASE mtheory ON 
    ( FILENAME = N'P:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\mtheory.mdf' ),
    ( FILENAME = N'P:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\mtheory.ldf' ),
    FILEGROUP SQLFilestreamGroup CONTAINS FILESTREAM DEFAULT 
    ( NAME = N'SQLFilestream', FILENAME = N'P:\Daten\SQL2014\mtheory\DATA' )
    FOR ATTACH
    GO

    The following command is also working, if I do not remove the physical file mtheory.ldf:

    USE [master]
    GO
    CREATE DATABASE mtheory ON 
    ( FILENAME = N'P:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\mtheory.mdf' ),
    FILEGROUP SQLFilestreamGroup CONTAINS FILESTREAM DEFAULT 
    ( NAME = N'SQLFilestream', FILENAME = N'P:\Daten\SQL2014\mtheory\DATA' )
    FOR  ATTACH_REBUILD_LOG
    GO
    


    But if I previously remove the physical file mtheory.ldf, the command will not work (sorry i have a german language Version, I try to translate):

    File activation error. The physical filename 'P:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\mtheory.ldf' may be incorrect. the new protocol file 'P:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\mtheory_log.ldf' was created. Meldung 5173, Ebene 16, Status 3, Zeile 54 At least one file does not match against the primary database of the database. If you try to attach a database, repeat the procedure with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. Meldung 5105, Ebene 16, Status 14, Zeile 54 Failed to activate the file. The physical file name'P:\Daten\SQL2014\mtheory\DATA' my be incorrect. Diagnose and resolve additional errors, and retry the operation.

    Meldung 1813, Ebene 16, Status 2, Zeile 54 the new mtheory-Database could not be opened. CREATE DATABASE is aborted.

    The result is: the command will create a new protocol-file mtheory_log.ldf (the old Name was mtheory.ldf). But this new file do not work with this database.

    After the file was created, I try to attach with the new created file:

    USE [master]
    GO
    CREATE DATABASE mtheory ON 
    ( FILENAME = N'P:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\mtheory.mdf' ),
    ( FILENAME = N'P:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\mtheory_log.ldf' ),
    FILEGROUP SQLFilestreamGroup CONTAINS FILESTREAM DEFAULT 
    ( NAME = N'SQLFilestream', FILENAME = N'P:\Daten\SQL2014\mtheory\DATA' )
    FOR ATTACH
    GO

    But i got this error:

    Message 5173, Level 16, State 5, Line 42
    At least one file does not match against the primary database of the database. If you try to attach a database, repeat the procedure with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.

    It seems, it is possible to attach a filestream activated database, it is also possible to attach a normal database wird rebuild log, but it seems, there is no way to attach a filestream activated database with attach_rebuild_log.


    • Edited by Isix Wednesday, February 3, 2016 10:02 AM sp_detach_db added
    Wednesday, February 3, 2016 9:59 AM

Answers

  • To my knowledge filestream has it's own log, which is kept in sync with the normal database log.

    Mow when you remove the latter there is no way to bring them back in sync.

    And I suppose that CREATE ... FOR ATTACH_REBUILD_LOG has not been extended to include new code to rebuild the FS-log.

    So you are really out of luck.

    Be aware that CREATE for ATTACH_REBUILD_LOG really has very limited use and you always risk to lose data unless you had full control over the circumstances at detach time.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Friday, February 5, 2016 9:07 AM

All replies