How to get online the database which has missing/corrupted FILESTREAM files? I've corrupted the files related to FILESTREAM in a database.

The .mdf and .ldf files are still intact, but when I try to get the database online, it complains, saying that the files related to FILESTREAM are incorrect.

I don't care about the data stored in FILESTREAM, but I do care about the other data. Can I get it back from .mdf and .ldf files? How?

When I execute:

sp_attach_db @dbname = 'Demo',
             @filename1 = N'&lt;File path&gt;.mdf',
             @filename2 = N'&lt;File path&gt;.ldf'


the response is:

Msg 5120, Level 16, State 105, Line 1
Unable to open the physical file "&lt;Location&gt;". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5105, Level 16, State 14, Line 1
A file activation error occurred. The physical file name '&lt;Location&gt;' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1813, Level 16, State 2, Line 1
I finally found how to do it. I've found some very old backups. The data changed meanwhile, and it was important to have an up-to-date version of this data, which meant that it wasn't possible to just restore the old backup. Instead, I did the following, thanks to Michael Eklöf on ServerFault:


Copy the current .mdf and .ldf files.
List the filegroups of the old backup:

use [master]
go

restore filelistonly
from disk = 'E:\Database backups\Hello_backup_2012_03_10_202359_9203520.bak'
go

Restore the database from the old backup, with the names of filegroups from the previous result:

restore database [Hello]
from disk = 'E:\Database backups\Hello_backup_2012_03_10_202359_9203520.bak'
with move 'Hello' to 'D:\Database\Hello.mdf',
move 'Hello_log' to 'D:\Database\Hello_log.ldf',
move 'Hello_files' to 'D:\Database\Hello_files'
go

Set to offline the filegroup (in my case, the FILESTREAM) which is missing in the new version of the database:

alter database [Hello]
modify file (name = 'Hello_files', offline)
go

Take the database offline.
Replace the .mdf and .ldf files created by the server at step 3 by the files copied at step 1.
Take the database online.


There are still consequences of the missing filegroup. Some operations require the database to be complete; on of those critical operations is the backup. In other words, full backup is impossible to do with a database restored through the six steps above.

In order to get the backup ability back, I've copied the structure of the database (through Tasks → Generate Scripts...) excluding the tables which relied on filestream and then copied the data itself.