How to get online the database which has missing/corrupted FILESTREAM files?

Question

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'<File path>.mdf',
             @filename2 = N'<File path>.ldf'

the response is:

Msg 5120, Level 16, State 105, Line 1
Unable to open the physical file "<Location>". 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 '<Location>' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1813, Level 16, State 2, Line 1

Answer 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:

1. Copy the current .mdf and .ldf files.

2. 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
   

3. 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
   

4. 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
   

5. Take the database offline.

6. Replace the .mdf and .ldf files created by the server at step 3 by the files copied at step 1.

7. 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.