Introduction:
In this article,i am going to explain how to alter a database in sql server 2008.

Main:
we can use the ALTER DATABASE command to change a database’s defi nition in one of the
following ways:
* Change the name of the database.
* Add one or more new data files to the database. If you want, you can put these files in
a user-defi ned filegroup. All files added in a single ALTER DATABASE command must go
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. (Adding files to those filegroups must be done
in a separate ALTER DATABASE command.) Modify an existing file in one of the
following ways:
* Increase the value of the SIZE property.
* Change the MAXSIZE or FILEGROWTH property.
* Change the logical name of a file by specifying a NEWNAME property. The value of
NEWNAME is then used as the NAME property for all future references to this file.
* Change the FILENAME property for files, which can effectively move the files to a new
location. The new name or location doesn’t take effect until you restart SQL Server.
For tempdb, SQL Server automatically creates the files with the new name in the new
location; for other databases, you must move the file manually after stopping your
SQL Server instance. SQL Server then fi nds the new file when it restarts.

* Mark the file as OFFLINE. You should set a file to OFFLINE when the physical file has
become corrupted and the file backup is available to use for restoring. (There is also
an option to mark the whole database as OFFLINE, which I’ll discuss shortly when I talk
about database properties.) Marking a file as OFFLINE allows you to indicate that you
don’t want SQL Server to recover that particular file when it is restarted. 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 command 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.

See this below simple examble,

The following example creates a new fi legroup in a database, adds two 500-MB fi les to
the fi legroup, and makes the new fi legroup the default fi legroup. You need three ALTER
DATABASE statements,

ALTER DATABASE Test1 ADD FILEGROUP Test1FG1; GO ALTER DATABASE Test1 ADD FILE ( NAME = 'test1dat4', FILENAME = 'c:\program files\microsoft sql server\mssql.1\mssql\data\t1dat4.ndf', SIZE = 500MB, MAXSIZE = 1000MB, FILEGROWTH = 50MB), ( NAME = 'test1dat5', FILENAME = 'c:\program files\microsoft sql server\mssql.1\mssql\data\t1dat5.ndf', SIZE = 500MB, MAXSIZE = 1000MB, FILEGROWTH = 50MB) TO FILEGROUP Test1FG1; GO ALTER DATABASE Test1 MODIFY FILEGROUP Test1FG1 DEFAULT; GO

Conclusion:
Hope this helps,
Happy Coding.