Altering a Database

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

• 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 will remove all the files in the filegroup.
• 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 property for files only in the tempdb database; this change doesn't go into effect until you stop and restart SQL Server.
• 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.

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

ALTER DATABASE Examples

The following examples demonstrate what you can do with ALTER DATABASE. This first example increases the size of a database file:

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

This next example creates a new filegroup in a database, adds two 5-MB files to the filegroup, and makes the new filegroup the default filegroup. Three ALTER DATABASE statements are needed:

 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  GO  ALTER DATABASE Test1  MODIFY FILEGROUP Test1FG1 DEFAULT  GO