Sql-server – Bringing SQL Server filegroups online

filegroupspartitioningrecoverysql-injectionsql-server-2008-r2

We've experienced an sql injection attack which corrupted our main db.

The main db had two partitioned tables into filegroups.

The corrupt database entered into Suspect mode, and it's been impossible for us to use.

I was able to restore a backup of the db with partial (no filegroups, since we didn't have backup of those.. I know bad thing).

I was now able to remove the db in suspect mode so we are left with the restored db and the file groups that were linked to the corrupt db.

Now I'm trying to figure out how to bring those file groups back online.

I've tried removing them so I can then add them back on, but I can't remove them since I get 

Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup 'FG_Audit' because the filegroup is not online.

Is there any work around? I've found posts where they talk about restoring from backup but we don't have one of the filegroups.

Have we lost that data? How do we remove the filegroups? Backups now stopped working because of this.

UPDATE

If I run this

SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

All the file groups that the DB is complaining about being off line they say "RECOVERY_PENDING"

Best Answer

John Huang (MVP) outlines an approach in his blog post:

http://www.sqlnotes.info/2013/05/07/attach-database-with-missing-ndf-file/

The basic approach is outlined by John Huang as:

  1. Create a empty database with the same file layout, including names, filegroups, location, etc, everything but data
  2. Take the missing files offline
  3. Take the database offline
  4. Use the data files you have saved to overwrite the empty data files
  5. Bring the database online

This allows you the chance to cobble things back together, but it can still have issues.

So, please, make sure you maintain good and complete backups.

Related Question