ALTER DATABASE

Modify a database, or the database files and filegroups.

Syntax
     ALTER DATABASE database
        ADD FILE filespec [ ,...n ] 
           [TO FILEGROUP {filegroup | DEFAULT} ] [;]
        
     ALTER DATABASE database
        ADD LOG FILE filespec [ ,...n ] [;]

     ALTER DATABASE database
        REMOVE FILE logical_file_name  [;]

     ALTER DATABASE database
        MODIFY FILE filespec [;]

     ALTER DATABASE database
        ADD FILEGROUP filegroup [;]

     ALTER DATABASE database
        REMOVE FILEGROUP filegroup [;]

     ALTER DATABASE database
        MODIFY FILEGROUP filegroup [;]
           { filegroup_updatability_option 
           | DEFAULT
           | NAME = new_filegroup 
           } [;]

     ALTER DATABASE database
        SET optionspec [ ,...n ] [ WITH termination ]  [;]

     ALTER DATABASE database
        MODIFY NAME = new_database_name [;]

     ALTER DATABASE database
        COLLATE collation [;]


filespec: 
(
  NAME = logical_file_name  
     [ , NEWNAME = new_logical_name ] 
        [ , FILENAME = 'os_file_name' ] 
           [ , SIZE = size [ KB | MB | GB | TB ] ] 
              [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
                 [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 
                    [ , OFFLINE ]
) 

filegroup_updatability_option:
   READ_ONLY
   READ_WRITE

optionspec: 
   ONLINE
   OFFLINE
   EMERGENCY

   SINGLE_USER
   RESTRICTED_USER
   MULTI_USER

   READ_ONLY
   READ_WRITE

   DB_CHAINING {ON | OFF}
   TRUSTWORTHY {ON | OFF}

   CURSOR_CLOSE_ON_COMMIT {ON | OFF}
   CURSOR_DEFAULT {LOCAL | GLOBAL}

   AUTO_CLOSE {ON | OFF} 
   AUTO_CREATE_STATISTICS {ON | OFF} 
   AUTO_SHRINK {ON | OFF} 
   AUTO_UPDATE_STATISTICS {ON | OFF} 
   AUTO_UPDATE_STATISTICS_ASYNC {ON | OFF}

   ANSI_NULL_DEFAULT {ON | OFF} 
   ANSI_NULLS {ON | OFF}
   ANSI_PADDING {ON | OFF} 
   ANSI_WARNINGS {ON | OFF}
 
   ARITHABORT {ON | OFF}
   CONCAT_NULL_YIELDS_NULL {ON | OFF} 
   NUMERIC_ROUNDABORT {ON | OFF}
   QUOTED_IDENTIFIER {ON | OFF} 
   RECURSIVE_TRIGGERS {ON | OFF} 

   RECOVERY {FULL | BULK_LOGGED | SIMPLE} 
   TORN_PAGE_DETECTION {ON | OFF}
   PAGE_VERIFY {CHECKSUM | TORN_PAGE_DETECTION | NONE}
 
   PARTNER = 'partner_server' 
   PARTNER FAILOVER 
   PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
   PARTNER OFF
   PARTNER RESUME 
   PARTNER SAFETY {FULL | OFF}
   PARTNER SUSPEND 
   PARTNER TIMEOUT integer
   WITNESS = 'witness_server' 
   WITNESS OFF

   DATE_CORRELATION_OPTIMIZATION {ON | OFF}

   PARAMETERIZATION {SIMPLE | FORCED}

   ENABLE_BROKER
   DISABLE_BROKER
   NEW_BROKER
   ERROR_BROKER_CONVERSATIONS

   ALLOW_SNAPSHOT_ISOLATION {ON | OFF}
   READ_COMMITTED_SNAPSHOT {ON | OFF}

termination: 
   ROLLBACK AFTER integer [SECONDS] 
   ROLLBACK IMMEDIATE 
   NO_WAIT

Key:
   MODIFY FILE Modify file location or properties, Only one <filespec> property
               can be changed at a time.

   FILEGROWTH  Add new space in increments of x, MB, KB, GB, TB, or percent (%)
               A value of 0 will set automatic growth to off.

   filespec OFFLINE    Set the file offline, make all objects in the filegroup inaccessible.
                       To set the file back online restore the file from a backup.

   optionspec OFFLINE  Close the database, clean shut down.

   EMERGENCY   Mark the database as READ_ONLY, disable logging, and restrict access
               to members of the sysadmin fixed server role.

   DB_CHAINING Database can be accessed by external resources (objects from another database)

   AUTO_CLOSE  Cleanly shut down the database when no users are connected, 
               this will free up its resources. Database mirroring requires AUTO_CLOSE OFF.

   AUTO_SHRINK he database files are candidates for periodic shrinking.

Examples

-- Rename a database
USE master;
GO
ALTER DATABASE MyDatabase
  MODIFY NAME = SalesDatabase;
GO

-- Move a file
USE master;
GO
ALTER DATABASE MyDatabase
  MODIFY FILE ( NAME = MyData1, FILENAME = 'c:\demo\data_01.mdf' );
GO

-- Drop a file
USE master;
GO
ALTER DATABASE MyDatabase
REMOVE FILE MyData1;

-- Add a filegroup and 2 datafiles 
USE master;
GO

ALTER DATABASE windevcluster
ADD FILEGROUP windevclusterFG1;

ALTER DATABASE windevcluster
ADD FILE
( NAME = windevcluster_dat2,
FILENAME = 'E:\DATA\windevclusterdatabase\windevcluster_data2.mdf',
SIZE = 100MB,
MAXSIZE = 150MB,
FILEGROWTH = 25MB ),
( NAME = windevcluster_dat3,
FILENAME = 'E:\DATA\windevclusterdatabase\windevcluster_data3.mdf',
SIZE = 100MB,
MAXSIZE = 150MB,
FILEGROWTH = 25MB )
)
TO FILEGROUP windevclusterFG1;
GO

-- Gain exclusive access, rollback all incomplete transactions. 
USE master;
GO

ALTER DATABASE windevcluster
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

"All conservatism is based upon the idea that if you leave things alone you leave them as they are. But you do not. If you leave a thing alone you leave it to a torrent of change” ~ G.K. Chesterton

Related commands

CREATE DATABASE
DROP DATABASE
sys.databases