SQL 2008 Filestream and Attach Database

If when you upgrade to SQL Server 2008 you decide to use the Filestream feature, you can not detach a database, move the files and attach the database back to SQL Server with as much ease as you previously used to do.  In order to move the filestream binaries, and the database data and log files to a new location, you have to use the CREATE DATABASE command and specify the FOR ATTACH option:

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = N'D:\Srvapps\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = N'D:\Srvapps\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = N'D:\Srvapps\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\archlog1.ldf')
GO

If you create the above database, specifying the appropriate paths for your environment, and then detach the database from the SQL Instance, you can move the files, so the mdf, ldf, and filestream1 folder will be moved to the D:\Data\ directory.  To attach the database back from this new path, you can not use SSMS.  It is not aware of the Filestream filegroup, so you have to issue a DDL CREATE DATABASE command:

USE [master]
GO
CREATE DATABASE [Archive] ON 
( FILENAME = N'D:\Data\archdat1.mdf' ),
( FILENAME = N'D:\Data\archlog1.ldf' ),
( FILENAME = N'D:\Data\filestream1' )
 FOR ATTACH
GO

This will attach the database and update the system catalogs to reflect the new file locations properly.