Sql-server – The backup of the file or filegroup “fileStream” is not permitted, because it is not online

filegroupssql server

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)
go

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?

Best Answer

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

RESTORE DATABASE YourDB FILEGROUP=YourFG WITH RECOVERY;

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

Related Question