SQL - Structured Query Language

ALTER DATABASE



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;

GO

           

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

);

GO


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;

GO

         

ALTER DATABASE eEducation

MODIFY FILE

    (NAME = eEducation_New_Data,

    SIZE = 20MB);

GO


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;

GO

       

ALTER DATABASE eEducation

REMOVE FILE eEducation_New_Data;

GO


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.

Example 04

Add Log Information File

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

-- Add Log Information File --

     

USE master;

GO

     

ALTER DATABASE eEducation

ADD LOG FILE

(

    NAME = eEducation_New_Log,

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

    SIZE = 5MB,

    MAXSIZE = 100MB,

    FILEGROWTH = 5MB

);

GO


Note: ADD LOG FILE keywords are used to add log information file(s) in the specified Database.

Example 05

Modify Log Information File

The following T-SQL statement is used to modify log information file size.

-- Modify Log Information File --

   

USE master;

GO

   

ALTER DATABASE eEducation

MODIFY FILE

    (NAME = eEducation_New_Log,

    SIZE = 20MB);

GO


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

Email Your Comment To AUTHOR