| 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. You can optionally put these files in a user-defined filegroup. All files added in a single ALTER DATABASE statement 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 statement.) 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. (In SQL Server 2000, only files in the tempdb database can be moved in this way.) 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 finds 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 is a new feature in SQL Server 2005; it 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 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 ALTER DATABASE command. 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.1\mssql\data\t1dat3.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB), ( NAME = 'test1dat4', FILENAME = 'c:\program files\microsoft sql server\ mssql.1\mssql\data\t1dat4.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP Test1FG1; GO ALTER DATABASE Test1 MODIFY FILEGROUP Test1FG1 DEFAULT; GO
|
