Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a sql 2008 database that had a filestream. Unfortunately, all I have is the .mdf and .ldf files. I no longer have access to the filestream .hdr file or anything that was in that directory.

Normally, you can just reattach the database to a new server; but sql server refuses to do that.

The error message I get is
Unable to open the physical file "c:\". Operating system error 2: "2(The system cannot find the file specified.)".

That error shows up when I try to use the DDL command to CREATE DATABASE FOR ATTACH.

What's the trick?
Thanks

share|improve this question

2 Answers 2

up vote 1 down vote accepted

Short answer: a database is screwed IF you are missing the filestream data. I tried pretty much everything under the sun and you cannot attach a database that has a filestream dependency WITHOUT the original filestream.hdr file.

You can get it to the point that it's in recovery mode, but that won't allow you to get to the tables. All we cared about was structure.

Fortunately, we did have a backup. It was located on a drive that had been disconnected after the server reinstall. Network guy was verbally spanked on that one.

Armed with the .hdr file and everything else in that directory we were able to attach the database back to the server with zero issues.

The point is: IF you are using FILESTREAM, you have to get the stream data in addition to all of the normal data files otherwise you can't reattach.

share|improve this answer

In SQL 2K5 I've had this happen.

I create a new db, name it whatever you want. take it offline, go into preferences and attach the .mdf and .ldf files as replacements accordingly. It's worked for me a few times, but not sure on SQL 2k8.

Good luck

share|improve this answer
    
This was a no go on 2008. –  Chris Lively Nov 6 '09 at 15:13
    
I did this on SQL Server 2008 R2. I renamed the MDF and LDF files of the original database (let's call it "MyDB") that is missing the Filestream files,. I then created a new database "MyDB" with the original data / log files. I then took "MyDB" offline. I then deleted the newest data / log files, and renamed the original data / log files to the ones I just deleted. I then brought the database back online. I had all the original objects and data, with no Filestream filegroup. My guess is that this clears the Filegroup / Filestream definitions for the database out of the system tables in master. –  user118608 Apr 24 '12 at 18:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.