Sql-server – SQL Server, attaching a database with Filestream, error 2

sql server

I'm trying to attach a database sent to me by someone. Unfortunately no backup is available, so I've been sent the raw files themselves. I'm getting the following error in Management Studio:

Unable to open the physical file 
"C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_fs".  
Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)". 

A file activation error occurred.  The physical file name
'C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_fs' may be incorrect.
Diagnose and correct additional errors, and retry the operation. 

Could not open new database 'MyDatabase'. CREATE DATABASE is aborted. 
File activation failure. 
The physical file name 
"C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_log.ldf" may be incorrect. 
New log file 
'C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_log.LDF' was created.

From what I can see, it's struggling with the FILESTREAM folder (which contains around 21Gb of data). I've made it writeable, am running Management Studio and running as Administrator. The folder I'm trying to attach from is a folder with other working databases in. I'm at a loss to interpret this error. The server version is 2008 (the same as that at the other end).

Can anyone assist here?

Edit: To add to this, what I did was create a new database with the same name, stop SQL Server, copy the old database files over and then start SQL Server again. The table that contains the FILESTREAM records can't be enumerated in Management Studio, however, giving me the following error: "A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)". Some reading leads me to believe there's a permissions issue with the filestream files, although as far as I can see, it has SYSTEM, Administrator and local user permissions, so I'm at a loss to see what I should do here.

The command I used was:

CREATE DATABASE [MyDatabase] ON  (FILENAME =
N'C:\Data\MSSQL10.SQL2008\MSSQL\DATA\MyDatabase.mdf'), FILEGROUP
[MyDatabase_fs_Group] CONTAINS FILESTREAM DEFAULT  (NAME =
N'MyDatabase_fs', FILENAME =
N'C:\Data\MSSQL10.SQL2008\MSSQL\DATA\MyDatabase_fs') FOR
ATTACH_REBUILD_LOG GO

Also, looking in the logs, there's an EXCEPTION_ACCESS_VIOLATION when I do a SELECT involving the FILESTREAM field in the table. 

2013-12-06 13:58:22.89 Server      Error: 17310, Severity: 20, State:1. 
2013-12-06 13:58:22.89 Server      A user request from the session with SPID 52       
    generated a fatal exception. SQL Server is terminating
    this session. Contact Product Support Services with the dump produced
    in the log directory. 
2013-12-06 13:58:23.83 spid52      Starting up
    database 'ManageTTX'. 
2013-12-06 13:59:25.07 spid52      Using 'dbghelp.dll' version '4.0.5' 
2013-12-06 13:59:25.07 spid52      ***Stack Dump being sent to 
    C:\Data\MSSQL10.Data\MSSQL\LOG\SQLDump0006.txt 
2013-12-06 13:59:25.07 spid52      SqlDumpExceptionHandler: Process 52 generated
    fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
    terminating this process. 2013-12-06 13:59:25.07 spid52

Best Answer

It appears you are trying to use the GUI interface to attach the database. In this instance you are going to have to write a T-SQL statement in order to attach it, and let SQL Server know where you put the filestream data as well.

There is a write up here that provides a walk through on doing this, the command the author used in order to attach a database once he had moved the default location of the filestream data for the example database:

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

Related Question