ALTER DATABASE (T-SQL)

Adds or removes files and filegroups for a database. Can also be used to modify the attributes of files and filegroups, such as changing the name or size of a file.

Syntax

ALTER DATABASE database
{    ADD FILE <filespec> [,...n] [TO FILEGROUP filegroup_name]
    | ADD LOG FILE <filespec> [,...n]
    | REMOVE FILE logical_file_name
    | ADD FILEGROUP filegroup_name
    | REMOVE FILEGROUP filegroup_name
    | MODIFY FILE <filespec>
    | MODIFY FILEGROUP filegroup_name filegroup_property
}

<filespec> ::=
(NAME = logical_file_name
  [, FILENAME = 'os_file_name' ]
  [, SIZE = size]
  [, MAXSIZE = { max_size | UNLIMITED } ]
  [, FILEGROWTH = growth_increment] )

Arguments

database

Is the name of the database being changed.

ADD FILE

Specifies that a file is being added.

TO FILEGROUP

Specifies the filegroup to which to add the specified file.

filegroup_name

Is the name of the filegroup to add the specified file to.

ADD LOG FILE

Specifies that a log file be added to the specified database.

REMOVE FILE

Removes the description of the file from the database system tables and deletes the physical file. The file cannot be removed unless it is empty.

ADD FILEGROUP

Specifies that a filegroup is to be added.

filegroup_name

Is the name of the filegroup to add or drop.

REMOVE FILEGROUP

Removes the filegroup from the database and deletes all the files in the filegroup. The filegroup cannot be removed unless it is empty.

MODIFY FILE

Specifies the given file should be modified, including the FILENAME, SIZE, FILEGROWTH, and MAXSIZE options. Only one of these properties can be changed at a time. NAME must be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current size of the file. FILENAME can be specified only for files in the tempdb database, and the new name does not take effect until Microsoft® SQL Server™ is restarted.

MODIFY FILEGROUP filegroup_name filegroup_property

Specifies the given filegroup property be applied to the filegroup. The values for filegroup_property are:

READONLY

Specifies the filegroup is read-only. Updates to objects in it are not allowed. The primary filegroup cannot be made read-only. Only users who have exclusive access to the database can mark a filegroup read-only.

READWRITE

Reverses the READONLY property. Updates are enabled for the objects in the filegroup. Only users who have exclusive access to the database can mark a filegroup read-write.

DEFAULT

Specifies the filegroup is the default filegroup for the database. Only one filegroup in a database can be the default filegroup. When you make one filegroup the default filegroup, the default property is removed from the filegroup that had been the default. CREATE DATABASE makes the primary filegroup the initial default filegroup. New tables and indexes are created in the default filegroup if no filegroup is specified in the CREATE TABLE, ALTER TABLE, or CREATE INDEX statements.

NAME

Specifies a logical name for the file.

logical_file_name

Is the name used in Microsoft SQL Server when referencing the file. The name must be unique in the server and conform to the rules for identifiers. The name can be a character or Unicode constant, a regular identifier, or a delimited identifier. For more information, see Using Identifiers.

FILENAME

Specifies an operating-system file name. When used with MODIFY FILE, FILENAME can be specified only for files in the tempdb database. The new tempdb file name takes effect only after SQL Server has been stopped and restarted.

'os_file_name'

Is the path and file name used by the operating system for the file. The file must reside in the server in which SQL Server is installed. Data and log files cannot be placed on compressed file systems.

If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be placed on each raw partition. Files on raw partitions do not autogrow; therefore, the MAXSIZE and FILEGROWTH parameters are not needed when os_file_name specifies a raw partition.

SIZE

Specifies the size of the file.

size

Is the size of the file. The MB and KB suffixes can be used to specify megabytes or kilobytes. The default is MB. Specify a whole number, do not include a decimal. The minimum value for size is 512 KB, and the default if size is not specified is 1 MB. When specified with ADD FILE, size is the initial size for the file. When specified with MODIFY FILE, size is the new size for the file, and must be larger than the current size of the file.

MAXSIZE

Specifies maximum size to which the file is allowed to grow.

max_size

Is the maximum size to which the file can grow. The MB and KB suffixes can be used to specify megabytes or kilobytes. The default is MB. Specify a whole number, do not include a decimal. If max_size is not specified, the file will grow until the disk is full. The Microsoft Windows NT® application log warns an administrator when a disk is about to become full.

UNLIMITED

Specifies that the file grows until the disk is full.

FILEGROWTH

Specifies file growth increment.

growth_increment

Is the amount of space added to the file each time new space is needed. A value of 0 indicates no growth. The value can be specified in MB, KB, or %. Specify a whole number, do not include a decimal. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. If a number is specified without an MB, KB, or % suffix, the default is MB. The default value if FILEGROWTH is not specified is 10%, and the minimum value is 64 KB. The size specified is rounded to the nearest 64 KB.

Remarks

To remove a database, use DROP DATABASE. To rename a database, use sp_renamedb. For information about decreasing the size of a database, see DBCC SHRINKDATABASE.

You cannot add or remove a file while a BACKUP statement is executing.

Adding or dropping a data or log file invalidates an existing log backup chain. If the backup and restore plan for the database includes log backups, perform a BACKUP DATABASE statement to start a new sequence of log backups soon after the ALTER DATABASE statement completes. For more information, see Transaction Log Backups.

To specify a fraction of a megabyte in the size parameters, convert the value to kilobytes by multiplying the number by 1024. For example, specify 1536 KB instead of 1.5MB (1.5 x 1024 = 1536).

Permissions

ALTER DATABASE permissions default to members of the db_owner and db_ddladmin fixed database roles, and to members of the sysadmin and dbcreator fixed server roles. These permissions are not transferable.

Examples

A. Add a file to a database

This example creates a database and alters it to add a new 5-MB data file.

USE master

GO

CREATE DATABASE Test1 ON

(

 NAME = Test1dat1,

 FILENAME = 'c:\mssql7\data\t1dat1.ndf',

 SIZE = 5MB,

 MAXSIZE = 100MB,

 FILEGROWTH = 5MB

)

GO

ALTER DATABASE Test1

ADD FILE

(

 NAME = Test1dat2,

 FILENAME = 'c:\mssql7\data\t1dat2.ndf',

 SIZE = 5MB,

 MAXSIZE = 100MB,

 FILEGROWTH = 5MB

)

GO

B. Add a filegroup with two files to a database

This example creates a filegroup in the Test 1 database created in Example A and adds two 5-MB files to the filegroup. It then makes Test1FG1 the default filegroup.

USE master

GO

ALTER DATABASE Test1

ADD FILEGROUP Test1FG1

GO

ALTER DATABASE Test1

ADD FILE

( NAME = test1dat3,

  FILENAME = 'c:\mssql7\data\t1dat3.ndf',

  SIZE = 5MB,

  MAXSIZE = 100MB,

  FILEGROWTH = 5MB),

( NAME = test1dat4,

  FILENAME = 'c:\mssql7\data\t1dat4.ndf',

  SIZE = 5MB,

  MAXSIZE = 100MB,

  FILEGROWTH = 5MB)

TO FILEGROUP Test1FG1

ALTER DATABASE Test1

MODIFY FILEGROUP Test1FG1 DEFAULT

GO

C. Add two log files to a database

This example adds two 5-MB log files to a database.

USE master

GO

ALTER DATABASE Test1

ADD LOG FILE

( NAME = test1log2,

  FILENAME = 'c:\mssql7\data\test2log.ldf',

  SIZE = 5MB,

  MAXSIZE = 100MB,

  FILEGROWTH = 5MB),

( NAME = test1log3,

  FILENAME = 'c:\mssql7\data\test3log.ldf',

  SIZE = 5MB,

  MAXSIZE = 100MB,

  FILEGROWTH = 5MB)

GO

D. Remove a file from a database

This example removes one of the files added to the Test1 database in Example B.

USE master

GO

ALTER DATABASE Test1

REMOVE FILE test1dat4

GO

E. Modify a file

This example increases the size of one of the files added to the Test1 database in Example B.

USE master

GO

ALTER DATABASE Test1

MODIFY FILE

    (NAME = test1dat3,

    SIZE = 20MB)

GO

F. Make the primary filegroup the default

This example makes the primary filegroup the default filegroup if another filegroup was made the default earlier.

USE master

GO

ALTER DATABASE MyDatabase

MODIFY FILEGROUP [PRIMARY] DEFAULT

GO

See Also