Dont worry if SSMS not support attaching FILESTREAM enabled databases

SQL Server 2005 introduced the unstructured data types VARCHAR (MAX), NVARCHAR (MAX) and VARBINARY (MAX). And later on File stream features came with 2008.

We are using SQL Server version 2012 and unfortunately I would like to say to Microsoft doesn’t given option to attach File-stream Enabled Database from SSMS. Mostly this requires when trying to migrate DB or system or doing some DR activity or maintenance activity. If you will try to attach from SSMS then it may give some errors like below …

TITLE: Microsoft SQL Server Management Studio

------------------------------

An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details.

------------------------------

BUTTONS:

OK

------------------------------

 But don’t worry just try below query to re-attach db..

USE [master]

GO

CREATE DATABASE <DBNAME> ON

( FILENAME = N'LOCATION\name.mdf' ),

( FILENAME = N'Location\name.ldf' ),

FILEGROUP FileGroup CONTAINS FILESTREAM DEFAULT

( NAME = N'filesname', FILENAME = N'location' )

FOR ATTACH

GO

Just re-fresh the db explorer, soon you will see Database but in read-only mode.. Now give below query...

ALTER DATABASE < DBNAME> SET READ_WRITE WITH NO_WAIT

Hope this queries will resolve your issue, like my issues get resolved and enjoy happy moments.

Thanks