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"

Federico Giust
  • 1,803
  • 4
  • 20
  • 45

3 Answers3

0

Can you create any FILEGROUP and FILES therein (see Code). Do you have any information about what tables or partitions were allocated to the FILEGROUPS that were there before? USE Master, then ...

ALTER DATABASE [<YourDatabase>] ADD FILEGROUP [FG_Audit_New];
GO

ALTER DATABASE [<YourDatabase>] ADD FILE (NAME = N'AuditData_1', 
FILENAME = N'<YourPath>\AuditData_1.ndf', 
SIZE = 256000KB , FILEGROWTH = 61440KB)
TO FILEGROUP [FG_Audit_New]; -- change metrics at will.
GO

ALTER DATABASE [<YourDatabase>] ADD FILE (NAME = N'AuditData_2', 
FILENAME = N'<YourPath>\AuditData_2.ndf', 
SIZE = 256000KB , FILEGROWTH = 61440KB )
TO FILEGROUP [FG_Audit_New]  -- change metrics at will.
GO

Does anything come out of this?

SELECT * FROM <YourDatabase>.sys.data_spaces;

CubeSpark
  • 1
  • 1
  • I know which tables are in the file groups, it's just two of them and it's only old data for example for the Audit table it's one file group per year from 2007 until 2013 and anything 2013 onwards is in the Primary File Group. When I run that select I get the list of file groups https://www.dropbox.com/s/ijxy1igh6jec6uh/Screenshot%202015-10-21%2008.51.57.png?dl=0 – Federico Giust Oct 21 '15 at 11:52
  • If I run ```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``` They all say recovery pending – Federico Giust Oct 21 '15 at 11:52
0

Okay, good explanation. Let me ask you, can you navigate to the Windows disk location where you stored the files for the FILEGROUPs in question? There will be a path to the Windows OS drives or LUNS that store that. Each FILEGROUP is logical (simply an attribute of storage, not a physical unit), but the files located in each FILEGROUP will have a physical location on disk. Find the files; there should be one or more files. If you cannot find those files, let me know, and while you are doing that I will figure out another angle.

Also, add the column "physical_name" to your query, and that will show you where the files are. The files have the data in them, so if you can find those files, we ought to be able to recover the FILEGROUPS.

By the way, Federico, can you list any information about your backup devices and backup sets? Is the database in SIMPLE model, BULK_LOGGED, or FULL? Do you have any FULL backup files, and are you doing LOG backups frequently (FULL Recovery model)? Do you have any differential backups? Did you back up the TAIL of the LOG when you smelled something bad in the fridge?

CubeSpark
  • 1
  • 1
  • The filegroup files are on the HD, I know the location and they haven't been deleted. The backup is FULL backup and we do a tlog backup every hour. After the attack I've made a mistake and did a partial restore instead of a full restore and it's been impossible to get those filegroups back online. – Federico Giust Oct 22 '15 at 19:26
  • The biggest issue with this is that I can't remove the file groups, can't remove some indexes that were on those file groups and the weekly backup is not running anymore because of this. – Federico Giust Oct 22 '15 at 19:30
  • Find the last full backup before the injection attack. Test a restore in a separate, renamed database when you restore. Chain in the restore of all the transaction log backups. If you have a Full Backup that was taken some time immediately before the attack, that FULL backup will have all the metadata about the logical FILEGROUPS, so a Restore from that Backup will restore all the files and containers. A FILEGROUP is logical, and the files within are physical. I have code that will help that, even to create a net new database with all the FILEGROUP logical containers. Do you want that script? – CubeSpark Oct 23 '15 at 00:41
  • Yes please! Just so you know, there is a gap in time that I don't have logs for but those filegroups is just old data. – Federico Giust Oct 23 '15 at 13:20
  • I was thinking, what if I restore the latest backup to a temp db, then setup some sort of replication using that temp db as a subscriber to get all the latest changes instead of using the logs? Do you think that would work? – Federico Giust Oct 23 '15 at 14:06
0

Federico, can you find a full back from just before the messup, along with the chain of differentials and / or log backups from BEFORE you did the partial? If so, can you start again with the restore, chaining in all the log backups and the tail?

CubeSpark
  • 1
  • 1
  • I have a full backup with logs up to an hour before the attack. – Federico Giust Oct 23 '15 at 13:17
  • Hours after I did the partial restore I've realised the log backups was not running anymore, so some of the logs are missing. Those filegroups is just old data so there is no transactions on those, but they are not configured as readonly. So I don't know if there is a way to restore them as readonly. – Federico Giust Oct 23 '15 at 13:19