I'm trying to backup a database, which has been created by someone I don't have access to. I get this error message:

The backup of the file or filegroup "fileStream" is not permitted, because it is not online.

and then it suggests to backup database via T-SQL to only backup online filegroups. However, this is not my requirement.

I want to bring the offline filegroup online again. I saw this answer which suggests using alter database command to take a fielgroup offline.

alter database dn_name
modify file (name='filegroup_name', offline)

However, as I saw the documentation of the mentioned command, there is no option to bring a fielgroup online back. What should I do? How can I bring a filegroup (or a file) back online in SQL Server 2012?

  • Please run this query to determine the current state (we cannot recommend anything without this information): SELECT state_desc FROM sys.database_files WHERE type = 2 (I'm assuming the filegroup actually is a FILESTREAM filegroup -- if not, modify the query to return that column for the particular file of interest.)
    – Jon Seigel
    Feb 9, 2013 at 15:42

1 Answer 1


If the file is still present, you should be able to do this:


If the file is no longer present (or present but not eligible to recover the data), you're in for a rough weekend. Depending on the complexity of the database, I would probably start making a backup plan (no pun intended):

  1. create a new database
  2. use the "Generate scripts" option against the old database to script the objects and data (or use a 3rd party tool and do a schema/data compare)
  3. make sure all database settings, logins etc. are mapped the same to the new database (this can get complex if you're using TDE, certificates, service broker, etc.)
  4. rename the old database
  5. rename the new database

(You could also skip 4. and 5. if you can adjust your applications to point to the new copy of the database.)

  • If someone else is trying to restore the filegroup from backups, running this statement could also lead to a rough weekend. That's why I asked for the current state first.
    – Jon Seigel
    Feb 10, 2013 at 14:52
  • @JonSeigel I thought it was a fair assumption that only one person is working on this database. If someone else is already trying to restore this file group, this operation should just fail immediately - no long weekend involved. Feb 10, 2013 at 15:09
  • Ah yes, you're right, either it's up to the current point in time or not. "a database, which has been created by someone I don't have access to" -- while it's unclear exactly what this means, to me it sounds like a multi-user scenario where the OP is not the database owner. I agree your answer is a possible resolution; still, I'm wary of taking an action without knowing more about the situation, like why is this filegroup not online in the first place?
    – Jon Seigel
    Feb 10, 2013 at 15:20
  • @Jon I took that as "no longer employed here." If they can't get an answer, I'd feel much safer trying to bring the file group online so I can start taking backups again (after all, the worst that happens is the operation fails), than just leaving it like it is. YMMV. Feb 10, 2013 at 19:06
  • @AaronBertrand, thanks for your answer. I tried the solution, but I got The file or filegroup "logSoundFile" is not in a valid state for the "Recover Data Only" option to be used. Only secondary files in the OFFLINE or RECOVERY_PENDING state can be processed.. Feb 11, 2013 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.