ALTER DATABASE (Transact-SQL)

Modifies the properties of a database.

Syntax

ALTER DATABASE { database_name | DATABASE_ID }
{ <alter_option_specifier> }
[;]

<alter_option_specifier> ::=
    <set_options> | <file_and_filegroup_options> | <transformation_options>

<set_options> ::=
    SET <option> [ ON | OFF ]
    [ , ...n ]

<option> ::=
    ( For example: ALLOW_CONNECTIONS, AUTO_CLOSE, AUTO_SHRINK, etc. )

<file_and_filegroup_options> ::=
    { ADD | REMOVE } FILE { [char_name] | [char_logical_name] }
    | ADD FILEGROUP <filegroup_name>
    | REMOVE FILEGROUP <filegroup_name>
    | MODIFY FILE ( NAME = <logical_name> ,
        { FILENAME = { <'os_file_name'> | NULL }
        | SIZE = { <size> | UNLIMITED }
        | MAXSIZE = { <max_size> | UNLIMITED }
        | FILEGROWTH = { <growth_increment> | UNLIMITED }
        | [ FILEGROWTH = 0 ]
        } [ , ...n ]
    )
    | MODIFY FILEGROUP <filegroup_name> <filegroup_option>

<transformation_options> ::=
    { SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }
    | MODIFY NAME = <new_database_name>
    }

Description

The ALTER DATABASE statement changes one or more database properties or performs other database maintenance. You can use ALTER DATABASE to change settings such as compatibility level, file growth, and file locations.

Permissions

By default, members of the sysadmin fixed server role can execute ALTER DATABASE. To execute ALTER DATABASE, a user must have at least the ALTER ANY DATABASE permission.

Examples

A. Changing the compatibility level

The following example changes the compatibility level of the AdventureWorks2019 database to 150.

ALTER DATABASE AdventureWorks2019
SET COMPATIBILITY_LEVEL = 150;

B. Adding a file to a database

The following example adds a new file named AdventureWorks2019_Data_2 to the AdventureWorks2019 database.

ALTER DATABASE AdventureWorks2019
ADD FILE (
    NAME = AdventureWorks2019_Data_2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Data_2.ndf',
    SIZE = 50MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 10MB
);

C. Modifying a file's size and growth

The following example modifies the AdventureWorks2019_Log file to allow unlimited growth and sets the maximum file size to 2TB.

ALTER DATABASE AdventureWorks2019
MODIFY FILE (
    NAME = AdventureWorks2019_Log,
    MAXSIZE = 2TB,
    FILEGROWTH = UNLIMITED
);