Add a filegroup

Command

-- 1) Create the filegroup:
ALTER DATABASE [Customer] ADD FILEGROUP [FG_Archive];

-- 2) Add a file to it:
ALTER DATABASE [Customer]
ADD FILE (
  NAME = N'Customer_archive1',
  FILENAME = N'F:\Data\Customer_archive1.ndf',
  SIZE = 8GB, FILEGROWTH = 1GB
) TO FILEGROUP [FG_Archive];

-- (Optional) make it the default filegroup:
-- ALTER DATABASE [Customer] MODIFY FILEGROUP [FG_Archive] DEFAULT;

What it does

Creates a new filegroup with a file in it; lets you place tables/indexes on a separate disk/filegroup.

When to use it

To separate archive/cold data, for filegroup-level backup/restore, and for I/O separation.

Caution

An empty filegroup cannot be backed up/used; add at least one file to it. Changing the default affects where new objects are placed.