How To Offline Secondary FileGroup in Sql Server???

In this small blog post I’m going to explain how to offline your secondary filegroup in sql server. There may be a situation when your drive gets corrupted and the secondary filegroup becomes unavailable. In this case you can do Filegroup restore of database while your primary filegroup will be available to users and your secondary file group will be offline till your restore process gets complete. I’ll explain how to do Filegroup restore of  your database in future post. Before applying Filegroup restore of your database you have to offline your secondary file group. So let see how to make offline your secondary filegroups in Sql server:

I have a sample database FileGroup. Use below script to create the same:

CREATE DATABASE FILEGROUP

ON PRIMARY

(NAME = N'REGISTRATION',

FILENAME = N'C:\REGISTRATION.MDF'),

FILEGROUP REPORT

(NAME = N'REPORTFILES',

FILENAME = N'E:\REPORTFILES.NDF')

LOG ON

(NAME = N'FILEGROUPDB_LOG',

FILENAME = N'C:\FILEGROUPDB_LOG.LDF')

  

Let see the status of our database FileGroup. Execute the below Query to get the same.

Use FileGroup

Go

select file_id, name, state_desc, physical_name from sys.database_files

You will See the output as below:

file_id name                             state_desc  physical_name

1          Registration                   ONLINE               C:\Registration.mdf

2          FileGroupDB_log           ONLINE               C:\FileGroupDB_log.ldf

3          ReportFiles                   ONLINE                E:\ReportFiles.ndf

As you can see right now all our filegroups are online. Now suppose E drive corrupted and report files become unavailable.

So now to  restore you have to offline the secondary file group i.e. ReportFiles

We can offline this filegroup by executing below query:

ALTER DATABASE FILEGROUP MODIFY FILE(NAME = REPORTFILES, OFFLINE)

Where..

FILEGROUP= Database  Name

REPORTFILES= FileGroup Name

The message in output box is "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.". It indicates that there were users found in the principal when you issued the command and these users have to be killed and their transactions rolled back.

Now Let see the status of files by executing below query:

Use FileGroup

Go

select file_id, name, state_desc, physical_name from sys.database_files

 As you can see from screen shot the ReportFiles is in OFFLINE state.

Thanks For Reading This Blog!!!!