ALTER DATABASE (Transact-SQL)

Modifies one or more properties of a database.

Syntax


ALTER DATABASE database_name
[ SET option_name [ ON | OFF ] ]
[ MODIFY FILE file_name ]
[ MODIFY LOG file_name ]
[ ADD FILE file_name ]
[ DROP FILE file_name ]
[ COLLATE collation_name ]
[ RESIZE = new_size MB ]
[ FILEGROUP filegroup_name DEFAULT ]
[ ENABLE_BROKER | DISABLE_BROKER ]
[ SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON | OFF ]
[ SET CONTAINMENT = [ NONE | PARTIAL ] ]
[ SET TRANSFORM GRID_SIZE = value ]
[ SET COMPRESSION = { ON | OFF } ]
[ SET DELAYED_DURABILITY = { DISABLED | ON | FOR_DESIRED_STATE } ]
[ SET CATALOG_COLLATION = collation_name ]
[ SET FILE file_name GROUP filegroup_name ]
[ SET FILE file_name PRIORITY percent ]
[ SET FILE file_name AUTO GROWTH = { ON | OFF | size [ KB | MB | GB | 100 MB ] } ]
[ SET FILE file_name MAXSIZE = { unlimited | size [ KB | MB | GB | 100 MB ] } ]
[ MODIFY NAME = new_name ]
[ READ_ONLY | READ_WRITE ]
[ SET SINGLE_USER | RESTRICTED_USER | MULTI_USER ]
[ SET AUTO_CLOSE { ON | OFF } ]
[ SET AUTO_SHRINK { ON | OFF } ]
[ SET AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF } ]
[ SET TRUSTWORTHY { ON | OFF } ]
[ SET DB_CHAINING { ON | OFF } ]
[ ALTER PARTITION scheme_name DISABLE ]
[ ALTER PARTITION scheme_name ENABLE ]
[ ALTER PARTITION scheme_name SWAP PARTITION source_partition_number WITH swap_parameters ]
[ ALTER PARTITION scheme_name MERGE PARTITION source_partition_number ]
[ ALTER PARTITION scheme_name SPLIT PARTITION source_partition_number VALUES ( boundary_value ) ]
[ ALTER PARTITION scheme_name UNINSTALL partition_name ]

Parameters

database_name
The name of the database to be modified.

option_name
Specifies a database option to be modified. Common options include:

RECOVERY: Specifies the recovery model of the database. Values are FULL, BULK_LOGGED, or SIMPLE.
COMPATIBILITY_LEVEL: Sets the compatibility level of the database.
AUTO_CLOSE: Automatically closes the database when the last user disconnects.
AUTO_SHRINK: Automatically shrinks database files when unused space is detected.

file_name
The logical name of the data or log file to be modified.

new_size
The new size of the database file in megabytes (MB).

collation_name
Specifies the default collation for the database.

Description

The ALTER DATABASE statement is a powerful command in SQL Server that allows administrators to modify various properties of an existing database. This includes changing file sizes, adding or removing files, setting recovery models, altering collations, and managing filegroups.

Important Considerations:

Always back up your database before executing ALTER DATABASE statements, especially those that modify file structures or critical settings.
Be mindful of the impact on database performance when resizing files or changing growth settings.
Ensure you have the necessary permissions (e.g., ALTER ANY DATABASE or db_owner role) to execute this command.
The specific syntax and available options may vary slightly between different versions of SQL Server. Consult the official documentation for your specific version.

Examples

1. Resizing a Data File

This example resizes a data file named MyDatabase_Data to 500 MB.


ALTER DATABASE MyDatabase
MODIFY FILE ( Name = MyDatabase_Data, Size = 500MB );

2. Setting the Recovery Model to Simple

This example changes the recovery model of MyDatabase to SIMPLE.


ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE;

3. Adding a New Data File

This example adds a new data file named MyDatabase_Data_2 to the PRIMARY filegroup.


ALTER DATABASE MyDatabase
ADD FILE ( Name = MyDatabase_Data_2,
    FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabase_Data_2.ndf',
    Size = 100MB,
    Maxsize = 1GB,
    Filegrowth = 50MB )
TO FILEGROUP PRIMARY;

4. Changing the Database Name

This example renames the database from OldDatabaseName to NewDatabaseName.


ALTER DATABASE OldDatabaseName
MODIFY NAME = NewDatabaseName;

Microsoft Learn