SQL - How to attach FileStream enabled db without log file

Question

I'm trying to attach a FileStream enabled database without a log file. My SQL looks something like this:

USE master
CREATE DATABASE MyDB
ON PRIMARY(NAME = N'MyDB', FILENAME = 'C:\myDB.MDF' ),
FILEGROUP myFileGroup CONTAINS FILESTREAM ( NAME = myData, FILENAME = 'C:\myFileGroup')      
For Attach

Here is the error I'm receiving:

Msg 5173, Level 16, State 3, Line 2
One or more files do not match the primary file of the database.
If you are attempting to attach a database, retry the operation with the correct files.  
If this is an existing database, the file may be corrupted and should be restored from a backup.

Does anyone know if it's possible to attach a FileStream enabled database without the original log file?  Thanks!

Answer 1

Attachment of File stream DB is doable without Log
file  like any DB to give a chance to
rebuild new Transaction log file since File stream groups are totally separated
groups rather than log file

However , it looks like here File stream files are irrelevant
to that DB that couldn’t be attached with that primary file.

---

Think more deeply of performance terms

Answer 2

Try this,

 CREATE DATABASE MyDB
      ON (FILENAME = '<drive>:\<file path>\MyDB.mdf') 
     FOR ATTACH_REBUILD_LOG ;

Answer 3

I gave that a try, but, no luck.  Same error.

Answer 4

Hi cgregory,

The error might occur if the database is not shutdown cleanly. In this case, log file is required, or, you will have some data lost. Please pay attention to this thread addressing this type of issue: attaching DB without .ldf file ???

For attach a database with FILESTREAM enabled, please refer to this article: How to Detach and Attach a SQL Server FILESTREAM Enabled Database.

---

Stephanie Lv

TechNet Community Support

Answer 5

The database appears to be clean.  I detached like this:

USE master
GO
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE master
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyDB'
GO

Then try to attach like this:

USE master
GO
CREATE DATABASE FCD ON
( FILENAME = N'C:\MyDB.MDF' ),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM DEFAULT
( NAME = N'MyFileGroup', FILENAME = N'C:\MyFileGroup' )
FOR ATTACH_REBUILD_LOG
GO

Answer 6

I verified that the database was detached cleanly.  Still no luck.  Any other ideas?

Answer 7

Can you try using sp_attach_db or sp_attach_single_file

http://msdn.microsoft.com/en-us/library/ms174385.aspx

Answer 8

Hi sqlrockz,

No luck with sp_attach_db or sp_attach_single_file.

Answer 9

No luck so far.