Sql-server – set a filegroup of database offline,change the location of files and bring it online again

filegroupssql servert-sql

I found that we can only take filegroup offline through t-sql,but to bring that filegroup online we need to restore the whole database.

Best Answer

There is no T-SQL command to take a filegroup offline, though an individual file can be set offline.

From the documentation, ALTER DATABASE File and Filegroup Options (Transact-SQL):

BOL excerpt

Use this option only when the file is corrupted and can be restored. A file set to OFFLINE can only be set online by restoring the file from backup. For more information about restoring a single file, see RESTORE (Transact-SQL).

To relocate files, follow the instructions in the documentation, or see the related Q & A:

How do I move SQL Server database files?