Index "INDEX" on table "TABLE" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

When I try to execute a simple SELECT statement, It's really slow and I found that the table performed TABLE SCAN.

But, The table also have a Nonclustered Index on it.

So, I try to force query optimizer to use the Index.

Select * from Tb_Sample with (Index =NCI_Id)
Go

oh. I got an error...

Msg 315, Level 16, State 1, Line 1
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

The reason behind are..
1. The Index (NCI_Id) may be DISABLED.
2. The Filegroup in OFFLINE(Where the actual Index resides).

1. The Index (NCI_Id) may be DISABLED(Scenario)

/*Disabling Index*/
Alter Index NCI_Id on Tb_Sample DISABLE
Go

/*Forcing Index*/
Select * from Tb_Sample with (Index =NCI_Id)
Go

It througs an error...
Msg 315, Level 16, State 1, Line 2
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

2. The Filegroup in OFFLINE(Where the actual Index resides)(Scenario)

/*Creating Index on different filegroup: IndexFilegroup*/
Create NonClustered Index NCI_Id on Tb_Sample(Id) on [IndexFilegroup]
Go
- File(Indexes) belongs to [IndexFilegroup] filegroup

/*Setting OFFLINE a particular file, which belongs to [IndexFilegroup] filegroup*/
Alter Database DatabaseName Modify File (Name=[Indexes],OFFLINE)
Go

Caution: It makes all the objects in the filegroup inaccessible, Only use when you have valid backup, Proper understanding/guidance

/*Forcing Index*/
Select * from Tb_Sample with (Index =NCI_Id)
Go

Now also, It througs an error...
Msg 315, Level 16, State 1, Line 2
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

So, Index can not be forced the query optimizer when the index in DISABLED or The filegroup is OFFLINE where the Index resides.

/*To Enable the DISABLED Index*/
Alter Index NCI_Id on Tb_Sample Rebuild
Go

/*To set ONLINE the Filegroup again*/
A file set to OFFLINE can only be set ONLINE by restoring the file from the backup..

Cannot change the state of non-data files or files in the primary filegroup.

When we try to set a particular Data file OFFLINE, The following Error occurred

Msg 5077, Level 16, State 2, Line 1
Cannot change the state of non-data files or files in the primary filegroup.

Why the Err ?
1. The file should be a Data file.
2. The file should be there in seperate Filegroup otherthan PRIMARY.
3. Log file can not be set OFFLINE as It can not be placed in seperate filegroup otherthan PRIMARY.

To set Data file OFFLINE:
ALTER DATABASE <Database Name> MODIFY FILE(NAME='<Data File Name>',OFFLINE)

To identify the file state(OFFLINE/ONLINE):
USE <Database Name>
GO
SELECT Name [File Name], Type_Desc [Data File Type],State_Desc [State] FROM SYS.MASTER_FILES WHERE NAME LIKE DB_NAME() + '%'

How to make the Data file ONLINE:
- Restore the backup file is the way to set the data file ONLINE
- Restore all the backup files (Full, Differential, Log(If any))

USE master
GO
RESTORE DATABASE <Database Name>
FILE = N'<Data File1>',
FILE = N'<Data File2>' FROM DISK = N'<Backup file Path.Bak>'
WITH FILE = 1,
NORECOVERY,
REPLACE
GO
--
--
--
RESTORE DATABASE <Database Name>
FILE = N'<Data File1>',
FILE = N'<Data File2>' FROM DISK = N'<Backup file Path.Bak>'
WITH FILE = 1,
REPLACE
GO

- All the Data file(s) changed to ONLINE.