2

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

1

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.

0

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

  • This was a no go on 2008. – NotMe Nov 6 '09 at 15:13
  • 1
    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
  • mowarren and @user118608: dang... I wish you were more clear in your answer, because this worked for me in SQL Server 2014 after many attempts because I didn't understand you at first. This is what worked: We create a new DB to match the existing (old) MDF and LDF files -- you do NOT create any filestream!!!! Then, you take it offline, delete the new MDF and LDF files, and plant your old MDF and LDF files. Then you take it online again. The result is you now have your DB with no filestream active. Next, queries affected by the missing filestream: there is a workaround you can google. – Richard Woolf Jul 9 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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