MS SQL Server 2012 - DBA Articles

ALTER DATABASE - Add, Modify, Delete Secondary Data File

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 01

Add Secondary Data File

The following T-SQL statement is used to add secondary data file in the eEducation database.

USE master;

ALTER DATABASE eEducation

ADD FILE

(

NAME = eEducation_New_Data,

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

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

);

Note: ADD FILE keywords are used to add secondary data file(s) in the specified Database.

Example 02

Modify Secondary Data File

The following T-SQL statement is used to modify secondary data file size.

-- Modify Secondary Data File --

USE master;

ALTER DATABASE eEducation

MODIFY FILE

(NAME = eEducation_New_Data,

SIZE = 20MB);

Note: MODIFY FILE keywords are used to update logical and physical data or log file name, file path and file size information.

Example 03

Delete Secondary Data File

The following T-SQL statement is used to delete a secondary data file from eEducation database.

-- Delete Secondary Data File --

USE master;

ALTER DATABASE eEducation

REMOVE FILE eEducation_New_Data;

Note: REMOVE FILE keywords are used to delete logical file description from an instance of SQL Server and delete empty physical data or log file.

* * * * *

Your Comment To AUTHOR