MS SQL Server 2012 - DBA Articles

ALTER DATABASE - Add, Modify, Delete Filegroup Information

ALTER DATABASE keywords are used to add or modify or delete an existing database Primary Data File, Secondary Data File(s), Log Information File(s) and Database Filegroups Information. Moreover, ALTER DATABASE is used to modify the attribute of a database through SET Option.

Database snapshots cannot be modified through ALTER DATABASE T-SQL statement. SQL Server sp_replicationdboption system procedure is helps to modify a database that associated with replication options.

Example 07

Add Filegroup with Secondary Data Files

The following statement is used to add Filegroup with two Secondary Data Files in the eEducation database.

-- Add Filegroup with Secondary Data Files --

USE master

ALTER DATABASE eEducation

ADD FILEGROUP eEducation_FG;

ALTER DATABASE eEducation

ADD FILE

(

NAME = eEducation_FG_Disk_D_Data,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\eEducation_FG_Disk_D_Data.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

(

NAME = eEducation_FG_Disk_E_Data,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\eEducation_FG_Disk_E_Data.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

TO FILEGROUP eEducation_FG;

Note: ADD FILEGROUP keywords are used to add filegroup in the specified Database. TO FILEGROUP keywords are used to add secondary data file(s) in the specified FileGroup of Database.

The sys.filegroups system catalog view is helps to get default filegroups information that can retrieve through this query SELECT * FROM sys.filegroups.

Example 08

SET DEFAULT into User Defined Filegroup

The following T-SQL statement is used to convert a User Defined Filegroup to DEFAULT Filegroup of eEducation Database.

-- SET DEFAULT into User Defined FileGroup --

USE master;

ALTER DATABASE eEducation

MODIFY FILEGROUP eEducation_FG DEFAULT;

Note: MODIFY FILEGROUP keywords are used to update attribute of Filegroup.

Example 09

SET DEFAULT into System Defined Filegroup

The following T-SQL statement is used to convert a System Defined Filegroup to DEFAULT Filegroup of eEducation Database.

-- SET DEFAULT into System Defined FileGroup --

USE master;

ALTER DATABASE eEducation

MODIFY FILEGROUP [PRIMARY] DEFAULT;

Note: DEFAULT keyword is used to convert a user defined filegroup into default filegroup from system defined primary filegroup.

Example 10

Delete Filegroup and Secondary Data Files

The following T-SQL statement is used to delete Secondary Data Files and user defined Filegroup of eEducation Database.

-- Delete Filegroup and Secondary Files --

USE master;

ALTER DATABASE eEducation

REMOVE FILE eEducation_FG_Disk_E_Data;

ALTER DATABASE eEducation

REMOVE FILE eEducation_FG_Disk_D_Data;

ALTER DATABASE eEducation

REMOVE FILEGROUP eEducation_FG;

Note: REMOVE FILEGROUP keywords are used to delete a filegroup from database that filegroup should be empty. When a filegroup is configured with any secondary data file(s), earliest delete the attached secondary data file(s) and then delete the filegroup from the database.

* * * * *

Your Comment To AUTHOR