Home / server / Questions / 80623
Accepted
NotMe
Asked: 2009-11-03 10:19:50 +0800 CST

How to reattach sql 2008 database without filestream?

  • 772

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

sql-server-2008

2 Answers

  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

    • 1
  2. Best Answer

    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.

    • 1