Attaching SQL Database with FILESTREAM data

 Have you ever come across a situation where you need to attach a database using existing files (mdf, ldf & file stream data), but you don't have an option to mention the file stream folder when you use SSMS.

This won't be an issue if the filestream folder resides on its original path.

Let's look at an example. I have SQL data, log and filestream data which I will first attach using SSMS.

1. Right-click on the database folder in SSMS and choose "Attach"
2. Click the "Add" button and choose the data file (.mdf). Upon selecting the mdf file it will list the associated ldf file as well. But make sure to change the path accordingly from the second pane.
3. Click on Ok.

In the above example, it will succeed without an issue since I have not changed the paths.

Now we will detach the database and change the file stream folder name to a different one. I will change the folder name to "SampleSqlDatabase_FSData_New" (This is the most common case where you will get the relevant files from the production environment which you need to attach to a test or development environment.

Now if you try to attach the database using SSMS in the same way you will get the following error message.

Even if you try to generate the scripts using SSMS it doesn't provide you with the option to include the filestream folder.

   
USE [master]
GO
CREATE DATABASE [SampleSqlDatabase] ON 
( FILENAME = N'D:\_SQL_DATA\SampleSqlDatabase_Data.mdf' ),
( FILENAME = N'D:\_SQL_LOG\SampleSqlDatabase_Log.ldf' )
 FOR ATTACH
GO

We can use the above code snippet and include the file stream path.

    
USE [master]
GO
CREATE DATABASE [SampleSqlDatabase] ON 
(FILENAME = N'D:\_SQL_DATA\SampleSqlDatabase_Data.mdf'),
(FILENAME = N'D:\_SQL_LOG\SampleSqlDatabase_Log.ldf'),
FILEGROUP NewFSGroup CONTAINS FILESTREAM (
	NAME = 'NewDatabaseFileStream'
	,FILENAME = 'D:\_SQL_DATA\SampleSqlDatabase_FSData_New'
)
FOR ATTACH
GO

Then you will be able to attach the database without an issue. You can use any name for the FILEGROUP and NAME (under the FILEGROUP). It doesn't need to be the same as your source database.

Hope this will be a helpful tip to you.