Create manage filegroups

Managing filegroups in SQL Server is an important aspect of database administration, allowing you to control the physical storage of data. Filegroups are logical groupings of files that can be used to distribute database objects across multiple files and disks. This can enhance performance and facilitate data management. Here's how to create and manage filegroups in SQL Server 2022: Creating Filegroups

1. Using SQL Server Management Studio (SSMS):**
   - Connect to your SQL Server instance. - Right-click the database where you want to add a filegroup, and select "Properties." - Go to the "Filegroups" page, click "Add," and enter the name of the new filegroup. - Click "OK" to create the filegroup.
2. Using Transact-SQL (T-SQL):**
   • Connect to your SQL Server instance using a query tool like SSMS.
   • Execute a `ALTER DATABASE` statement to add a filegroup:

     ALTER DATABASE [YourDatabaseName]
     ADD FILEGROUP YourFilegroupName;
     

Adding Files to Filegroups After creating a filegroup, you need to add files to it:

1. Using SSMS:** - Right-click your database, select "Properties," and then go to the "Files" page. - Click "Add" to create a new row in the database files grid. - Specify the file type, name, and path. Under "Filegroup," select the filegroup you created.
2. Using T-SQL:** - Use the `ALTER DATABASE` statement to add a file to your filegroup:

        ALTER DATABASE [YourDatabaseName] 
        ADD FILE (
          NAME = 'YourFileName',
          FILENAME = 'C:\Path\To\Your\File.ndf',
          SIZE = SizeInMB, -- e.g., 100MB
          MAXSIZE = UNLIMITED,
          FILEGROWTH = GrowthInMB -- e.g., 10MB
        ) 
        TO FILEGROUP YourFilegroupName;
        

Moving Objects to a New Filegroup To move table indexes to a new filegroup: - Use the `CREATE INDEX` statement with the `DROP_EXISTING = ON` option, specifying the new filegroup:

  CREATE NONCLUSTERED INDEX [YourIndexName]
  ON [YourTableName]([YourColumnName])
  WITH (DROP_EXISTING = ON) ON [YourFilegroupName];
  

Default Filegroup You might want to set a filegroup as the default. New tables and indexes will be created in this filegroup unless specified otherwise:

• Using SSMS, go to the database properties, select the "Filegroups" page, and set the desired filegroup as "Default".
• Using T-SQL:

  ALTER DATABASE [YourDatabaseName]
  MODIFY FILEGROUP YourFilegroupName DEFAULT;
  

Managing Filegroups

• To view filegroup information, query the `sys.filegroups` and `sys.database_files` system catalog views.
• Regularly monitor the disk space used by your filegroups to ensure that your database operates efficiently and to avoid running out of space.

Backup and Restore with Filegroups

• You can perform backup and restore operations on individual filegroups. This can be useful for large databases, allowing partial database availability during these operations.

  -- Backup
  BACKUP DATABASE [YourDatabaseName] FILEGROUP = 'YourFilegroupName'
  TO DISK = 'C:\BackupPath\YourBackupFile.bak';

  -- Restore
  RESTORE DATABASE [YourDatabaseName] FILEGROUP = 'YourFilegroupName'
  FROM DISK = 'C:\BackupPath\YourBackupFile.bak';
  

Managing filegroups effectively in SQL Server can lead to improved database performance and manageability. Regularly review your filegroup configuration and disk space usage to ensure optimal performance.

A filegroup is, exactly as it sounds, a group of files. Groups of files are useful for administration purposes because you can create a filegroup on specific drives, then assign specific tables, indexes, or other data to that filegroup. This other data can be of datatype text, ntext, or image.

A filegroup is particularly useful if you are using any form of fault tolerance, such as disk striping. This allows a database's files to reside across multiple disks, but administer the file in one convenient place; the filegroup. Therefore, if a change needs to be made, you do not need to change the multiple files, only the filegroup.

There are three types of filegroups in MS SQL Server 7.0. They are:

1. Primary: The filegroup that contains the primary database file (*.MDF), as well as pages for the system tables.
2. Default: The filegroup that contains pages for all database objects that did not explicitly specify a filegroup when they were created, such as tables and indexes. Although the default filegroup can be changed by anyone who is a member of the db_owner fixed database role, only one filegroup can be the default at any one time. If no default filegroup is specified, the primary filegroup is the default.
   
3. User-defined: A filegroup that is specified when the CREATE DATABASE or ALTER DATABASE statements are used, but is not the primary or default filegroup.

To specify a filegroup when you create a database, you use the CREATE DATABASE Transact-SQL statement, which you learned earlier in this module.
Let us take an example of how to specify a filegroup when you create a database. Suppose you are creating a database, called Timesheet. This database is to use a Primary file named c:\mssql7\data\Timesheet.mdf that starts with a size of 1MB, but grows to 10MB. This database creates a new filegroup, called TimesheetGrp with the same parameters as the database. This database also uses the default log size, as follows:

CREATE DATABASE Timesheet
ON PRIMARY
( NAME = Timesheet_dat,
FILENAME = 'c:\mssql7\data\Timesheet.mdf',
SIZE = 1,
MAXSIZE = 10),
FILEGROUP TimesheetGrp
( NAME = TimesheetGrp_dat,
FILENAME = 'c:\mssql7\data\Timesheetgrp.ndf',
SIZE = 1,
MAXSIZE = 10)

ALTER DATABASE Timesheet

MODIFY FILEGROUP TimesheetGrp DEFAULT