Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question
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 at 15:42

1 Answer

up vote 4 down vote

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

share|improve this answer
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 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. – Aaron Bertrand Feb 10 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 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. – Aaron Bertrand Feb 10 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.. – Saeed Neamati Feb 11 at 14:02
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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