Share via

How to attach an SQL Server database containing File Stream from one server to another server?

Silent Noise 26 Reputation points
2021-05-10T17:04:04.44+00:00

I have a database, there is no backup.
This database was created on SQL Server version 14 and that server is no longer available.

I have the MDF, LDF and the filestream.hdr files.

Now I want to attached this database to SQL Server 2017.

As you guess it, the attached process fails.

If I try to attached from Management Studio, I get an error that the file stream folder cannot be located.

However, if I point to the folder in script, I get File Activation Error. 

CREATE DATABASE [MyDB] ON 

( FILENAME = N'D:\MSSQL14.InstanceName\MSSQL\DATA\MyDB.mdf' ),

( FILENAME = N'L:\MSSQL14.InstanceName\MSSQL\Log\MyDB_log.ldf' ),

FILEGROUP [MyFileStream] CONTAINS FILESTREAM DEFAULT 

( NAME = N'MyFileStream', FILENAME = N'D:\MSSQL14.InstanceName\MSSQL\DATA\FileStreamFolder' )

FOR ATTACH

GO
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,363 questions
0 comments
Accepted answer
  1. Erland Sommarskog 107.2K Reputation points
    2021-05-10T21:37:32.783+00:00

    Only filestream.hdr? Not the actual files?

    What you can try is to create a new database, and then take that database offline, and copy the files of this database over the files of the new database. No guarantees, though.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Silent Noise 26 Reputation points
    2021-05-11T04:00:38.877+00:00

    Hello friends:

    Similar to what @Erland Sommarskog suggested:

    I did the following as I read on SO.

    1. Create a new database with the same name as the database I am attempting to attach. (The MDF and LDF names must match)
    2. Take the database offline and terminate all connections.
    3. Go to the folder where the new database is located and delete both the MDF and LDF files.
    4. Copy the files of the database I am attempting to attach and put them into the exact folder where the new DB was located before they were deleted.
    5. Make sure SQL Server has adequate access to the pasted DB file, MDF, LDF.
    6. Now in SSMS bring the database back online.

    I did the above and everything worked.

    I immediately backed-up the databases.

    1 person found this answer helpful.
    0 comments
  2. Cris Zhan-MSFT 6,616 Reputation points
    2021-05-11T03:40:47.8+00:00

    Hi,

    After I moved the mdf, ldf files and FileStream folders to different path like you did, I tested the script you showed in the question, but there was no problem and the database was successfully attached. Of course, if there is only the filestream.hdr file, the FileStream data will be lost, but it will not prevent the database from being successfully attached.