Altering a Database

You can use the ALTER DATABASE statement to change a database's definition in one of the following ways:

• Change the name of the database.
• Add one or more new data files to the database, which you can optionally put in a user-defined filegroup. You must put all files added in a single ALTER DATABASE statement in the same filegroup.
• Add one or more new log files to the database.
• Remove a file or a filegroup from the database. You can do this only if the file or filegroup is completely empty. Removing a filegroup removes all the files in it.
• Add a new filegroup to a database.
• Modify an existing file in one of the following ways:
  • Increase the value of the SIZE property.
  • Change the MAXSIZE or FILEGROWTH properties.
  • Change the name of a file by specifying a NEWNAME property. The value given for NEWNAME is then used as the NAME property for all future references to this file.
  • Change the FILENAME property for files only in the tempdb database; this change doesn't go into effect until you stop and restart SQL Server. You can change the FILENAME in order to move the tempdb files to a new physical location.
• Modify an existing filegroup in one of the following ways:
  • Mark the filegroup as READONLY so that updates to objects in the filegroup aren't allowed. The primary filegroup cannot be made READONLY.
  • Mark the filegroup as READWRITE, which reverses the READONLY property.
  • Mark the filegroup as the default filegroup for the database.
  • Change the name of the filegroup.
  • Change one or more database options. (I'll discuss database options later in the chapter.)

The ALTER DATABASE statement can make only one of the changes described each time it is executed. Note that you cannot move a file from one filegroup to another.

ALTER DATABASE Examples

The following examples demonstrate some of the changes you can make using the statement ALTER DATABASE.

This example increases the size of a database file:

 USE master GO ALTER DATABASE Test1 MODIFY FILE ( NAME = 'test1dat3', SIZE = 20MB) 

The following example creates a new filegroup in a database, adds two 5-MB files to the filegroup, and makes the new filegroup the default filegroup. We need three ALTER DATABASE statements.

 ALTER DATABASE Test1 ADD FILEGROUP Test1FG1 GO ALTER DATABASE Test1 ADD FILE ( NAME = 'test1dat3', FILENAME = 'c:\program files\microsoft sql server\mssql\data\t1dat3.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB), ( NAME = 'test1dat4', FILENAME = 'c:\program files\microsoft sql server\mssql\data\t1dat4.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP Test1FG1 GO ALTER DATABASE Test1 MODIFY FILEGROUP Test1FG1 DEFAULT GO