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