1

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"

  • Have you checked to see if the RECOVERY_PENDING files are actually still on disk and uncorrupted by the problem you ran into? Since the database is "corrupt" you likely have more than one problem to overcome. – RLF Oct 21 '15 at 12:51
  • Try watching the presentation from 2008 by Paul Randal at: sqlskills.com/blogs/paul/… – RLF Oct 21 '15 at 13:01
  • @RLF They are still on disk – Federico Giust Oct 21 '15 at 15:20
  • Try attaching the missing files back to the database. – RLF Oct 21 '15 at 19:59
  • @RLF Could you elaborate on that? I only see options to attach a database but not file groups – Federico Giust Oct 22 '15 at 0:15
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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