locked
Detached SQL Server 2008 R2 Database with FILESTREAM won't re-attach RRS feed

  • Question

  • I detached a production database to try and resolve an issue with a truncated log file that would not shrink. I copied the log file to a backup location and tried to re-attach the database expecting to get the prompt about re-creating the transaction log file. The database would not attach it referred to the filestream location referenced in the primary file as being incorrect.

    I copied the backed up transaction log back and tried again but got the same error. Does anybody have a solution that does not involve restoring the database backups?

    I did restore a backup but when I do a DBCC checkdb it shows the filestream as offline.

    Sunday, October 28, 2018 2:13 PM

Answers

  • Error 5 = "Access is denied".

    Which would indicate a permission issue, or someone else is holding the file open.

    But I am a little puzzled by the error message, it says "physical file". But this is not a file, but a folder - and it should know that.

    What does "SELECT @@version" report?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 29, 2018 10:17 PM

All replies

  • https://www.mssqltips.com/sqlservertip/1878/how-to-detach-and-attach-a-sql-server-filestream-enabled-database/

    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, October 28, 2018 2:53 PM
    Answerer
  • Hi Uri, Thanks for the reply, the location of the files has not changed so I did not think I needed to specify the path, I have already tried the code shown in the recommended article USE [master] GO CREATE DATABASE [FileStreamDB] ON ( FILENAME = N'C:\FileStreamDB\FileStreamDB.mdf' ), ( FILENAME = N'C:\FileStreamDB\FileStreamDB_log.ldf' ), FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT ( NAME = N'FileStreamDB_FSData', FILENAME = N'C:\FileStreamDB\FileStreamData' ) FOR ATTACH GO Obviously changing the names and the path but it still failed to attach. Unfortunately, I am not at work right now so I don’t have the exact error message. Do you think it’s worth trying moving the 50gb FileStream data to a new location and trying again. I thought it might be related to permissions but have tried using different service accounts and granting access to the FileStream location and 5he windows share. I will add the actual details and responses to this request for help. Thanks
    Sunday, October 28, 2018 6:27 PM

  • I did restore a backup but when I do a DBCC checkdb it shows the filestream as offline.

    Check if it got disabled at the instance level. 

    https://docs.microsoft.com/en-us/sql/relational-databases/blob/enable-and-configure-filestream?view=sql-server-2017#enabling

    Also, post the error snapshot when you get to work. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, October 29, 2018 4:48 AM
  • Hi JohnEKF,

     

    Based on my test, I can't attach the filestream database without log file. I created a database with filestream and then detached it.

     

    According to your description, I moved the log file to another folder and then attached it with rebuilding the log file.

     

    USE [master] GO CREATE DATABASE [FileStreamDB] ON

    (FILENAME = N'D:\test\FileStreamDB.mdf' ),

    FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT

    (NAME = N'FileStreamDB_FSData', FILENAME = N'D:\test\FileStreamData' )

    FOR ATTACH_REBUILD_LOG

    GO


     

    I got the same error.

     

    Msg 5173, Level 16, State 2, Line 3

    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

    Msg 1802, Level 16, State 7, Line 3

    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

     

    By the way, I can attach the database with log file successfully.

     

    Best Regards,

    Emily


    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, October 29, 2018 7:41 AM
  • I do not see you attach the log of the FileStream database

    USE [master]
    GO
    CREATE DATABASE [FileStreamDB] ON 
    ( FILENAME = N'D:\FileStreamDB\FileStreamDB.mdf' ),
    ( FILENAME = N'D:\FileStreamDB\FileStreamDB_log.ldf' )
    FOR ATTACH
    GO


    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

    Monday, October 29, 2018 9:20 AM
    Answerer
  • Thanks for your answers, Here is the code and error messages:

    use [master]
    go
    create database [DILIGENT] ON
    ( FILENAME = N'M:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\DILIGENT.mdf' ),
    ( FILENAME = N'L:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Log\DILIGENT.LDF' ),
    FILEGROUP [DILIGENT_REPORTS] CONTAINS FILESTREAM DEFAULT
    (NAME = N'DILIGENTFS', FILENAME = N'F:\Diligent' )
    FOR ATTACH
    GO

    The error message was:

    Msg 5120, Level 16, State 101, Line 1

    Unable to open the physical file "F:\Diligent". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".


    Tried to go back to original log file and got the following error:

    Msg 5173, Level 16, State 2, Line 1

    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

    Here is the result of checkprimaryfile:

    use master
    go
    dbcc checkprimaryfile ('M:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\diligent.mdf',1)
    fileid	groupid	size	maxsize	growth	status	perf	name	filename
    1	1	985576	-1	10	1048578	0	audit1_Data                                                                                                                     	M:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\DILIGENT.mdf                                                                                                                                                                                                                
    2	0	63	-1	10	1048642	0	audit1_Log                                                                                                                      	L:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Log\DILIGENT.LDF                                                                                                                                                                                                                 

    I have set necessary access permission to the folders where the files are located.

    Thanks

    Monday, October 29, 2018 11:05 AM
  • Error 5 = "Access is denied".

    Which would indicate a permission issue, or someone else is holding the file open.

    But I am a little puzzled by the error message, it says "physical file". But this is not a file, but a folder - and it should know that.

    What does "SELECT @@version" report?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 29, 2018 10:17 PM
  • Hi Erland,

    The SQL Version is:

    Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64)

    Sep  3 2014 04:11:34

    Copyright (c) 1988-2008 Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

    Still having problems, have devided to try to migrate to a new database, this is a very slow process and not recommended.

    Thanks

    Tuesday, October 30, 2018 12:28 PM
  • There does not seem to be any other application accessing the filestream, I have even disabled the virus scanning.

    As I am not getting anywhere trying to attach this database, I need to know if there is a way to edit the referenced files in the mdf.

    When I run the DBCC command checkpimaryfile it shows the log and data files (no reference to filestream), is there a way to see more of the references, such as filestream and perhaps a way to edit/remove the reference.

    In summary: does anybody know if there is a third party tool or a way of removing a filstream reference from a detached MDF file?

    I have used a tool to read the data from the detached mdf but it does not provide any editing or a configuration facility.

    Tuesday, October 30, 2018 1:39 PM
  • As I am not getting anywhere trying to attach this database, I need to know if there is a way to edit the referenced files in the mdf.

    That would be entirely unsupported. And it would not be possible to edit the name only, but you would also have to compute a new checksum.

    I guess its time to find that backup and restore.

    I tried do to what you did, and I was not successful, but I got a different error message:

    Msg 5173, Level 16, State 5, Line 20
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

    You are on the most recent service pack, which is good. I too often see people who lingering behind on SP1 or even RTM! I note, however, that you are on SQL 2008 R1, not R2.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 30, 2018 11:09 PM
  • John

    As Erland pointed, can you connect to the server with more power user and re-run the statement?


    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

    Wednesday, October 31, 2018 6:52 AM
    Answerer
  • Hi, I did try connecting with a higher level of access, local administrator, I also asked the domain administrator to try the command with his higher access level both failed.

    I have stopped the virus scanner, re-booted the machine, raised the SQL Service account to the highest level nothing seems to work.

    However; I do think you are right when you point to permission issues.

    This database has been running on this server for a number of years, in which time, the domain the server is on was joined to another domain and the AD authentication is managed on the new domain. (The environment is on the customers site and managed by them.)

    One of the problems that I came across was that the backup maintenance plans started to failed due to a permissions issue, even though the owner was set to local admin (not good practice) and the backup of the database and log files were on the same machine. (Different disk that's content was moved to a network drive every night). So it all seems to come back to permissions.

    I have now recovered the data from the backups, but at some stage this system will be moved to a new server and a new (supported version of SQL Server), I have to say that I am not very keen to do anything that involves this server and filestream data again!

    Thanks to everyone who provided help with this your contributions were greatly appreciated.

    John

    Wednesday, October 31, 2018 9:56 AM