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

NotMe
  • 3,772
  • 7
  • 30
  • 43

2 Answers2

1

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

mowarren
  • 71
  • 2
  • This was a no go on 2008. – NotMe Nov 06 '09 at 15:13
  • 3
    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. –  Apr 24 '12 at 18:30
  • 2
    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 09 '19 at 08:40
  • Still works in SQL 2017 – Jaques Jul 07 '20 at 19:57
  • Today I spent about 4 hours trying to resolve this issue. I came across this post the first time but based on the response below I simply browsed away without checking it out. This works and it save me from Months of work. I would have had to recreate an entire database and all of the tables and relationships from scratch. Thanks guys!! – Asynchronous May 10 '21 at 21:07
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.

NotMe
  • 3,772
  • 7
  • 30
  • 43