HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

SQL Server, attaching a database with Filestream, error 2

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
errorfilestreamsqlwithdatabaseattachingserver

Problem

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 shoul

Solution

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

Context

StackExchange Database Administrators Q#54586, answer score: 2

Revisions (0)

No revisions yet.