Transact-SQL Reference

ALTER DATABASE

Adds or removes files and filegroups from a database. Can also be used to modify the attributes of files and filegroups, such as changing the name or size of a file. ALTER DATABASE provides the ability to change the database name, filegroup names, and the logical names of data files and log files.

ALTER DATABASE supports the setting of database options. In previous versions of Microsoft® SQL Server™, these options could be set with the sp_dboption stored procedure. SQL Server continues to support sp_dboption in this release but may not do so in the future. Use the DATABASEPROPERTYEX function to retrieve current settings for database options.

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 NAME = new_dbname
| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
| SET < optionspec > [ ,...n ] [ WITH < termination > ]
| COLLATE < collation_name >
}

< filespec > ::=

( NAME = logical_file_name
    [ , NEWNAME = new_logical_name ]
    [ , FILENAME = 'os_file_name' ]
    [ , SIZE = size ]
    [ , MAXSIZE = { max_size | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment ] )

< optionspec > ::=

    < state_option >
    | < cursor_option >
    | < auto_option >
    | < sql_option >
    | < recovery_option >

    < state_option > ::=
        { SINGLE_USER | RESTRICTED_USER | MULTI_USER }
        | { OFFLINE | ONLINE }
        | { READ_ONLY | READ_WRITE }

    < termination > ::=
        ROLLBACK AFTER integer [ SECONDS ]
        | ROLLBACK IMMEDIATE
        | NO_WAIT

    < cursor_option > ::=
        CURSOR_CLOSE_ON_COMMIT { ON | OFF }
        | CURSOR_DEFAULT { LOCAL | GLOBAL }

    < auto_option > ::=
        AUTO_CLOSE { ON | OFF }
        | AUTO_CREATE_STATISTICS { ON | OFF }
        | AUTO_SHRINK { ON | OFF }
        | AUTO_UPDATE_STATISTICS { ON | OFF }

    < sql_option > ::=
        ANSI_NULL_DEFAULT { ON | OFF }
        | ANSI_NULLS { ON | OFF }
        | ANSI_PADDING { ON | OFF }
        | ANSI_WARNINGS { ON | OFF }
        | ARITHABORT { ON | OFF }
        | CONCAT_NULL_YIELDS_NULL { ON | OFF }
        | NUMERIC_ROUNDABORT { ON | OFF }
        | QUOTED_IDENTIFIER { ON | OFF }
        | RECURSIVE_TRIGGERS { ON | OFF }

    < recovery_option > ::=
        RECOVERY { FULL | BULK_LOGGED | SIMPLE }
        | TORN_PAGE_DETECTION { ON | OFF }

Arguments

database

Is the name of the database changed.

ADD FILE

Specifies that a file is 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 file description from the database system tables and deletes the physical file. The file cannot be removed unless 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 empty.

MODIFY FILE

Specifies the given file that 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 file size. 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.

To modify the logical name of a data file or log file, specify in NAME the logical file name to be renamed, and specify for NEWNAME the new logical name for the file.

Thus:

MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...).

For optimum performance during multiple modify-file operations, several ALTER DATABASE database MODIFY FILE statements can be run concurrently.

MODIFY NAME = new_dbname

Renames the database.

MODIFY FILEGROUP filegroup_name { filegroup_property | NAME = new_filegroup_name }

Specifies the filegroup to be modified and the change needed.

If filegroup_name and NAME = new_filegroup_name are specified, changes the filegroup name to the new_filegroup_name.

If filegroup_name and filegroup_property are specified, indicates 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 with exclusive database access 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 as the default database filegroup. Only one database filegroup can be default. CREATE DATABASE sets the primary filegroup as 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.

WITH <termination>

Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. Only one termination clause can be specified and it follows the SET clauses.

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
Specifies whether to roll back after the specified number of seconds or immediately. If the termination clause is omitted, transactions are allowed to commit or roll back on their own.
NO_WAIT
Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.

COLLATE < collation_name >

Specifies the collation for the database. Collation name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the default collation of the SQL Server instance.

For more information about the Windows and SQL collation names, see COLLATE.

<filespec>

Controls the file properties.

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 within the database 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 is 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 should not 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 file size.
size
Is the size of the file. The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. 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 file size.
MAXSIZE
Specifies the maximum file size.
max_size
Is the maximum file size. The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number; do not include a decimal. If max_size is not specified, the file size will increase 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 increases in size until the disk is full.
FILEGROWTH
Specifies file increase increment.
growth_increment
Is the amount of space added to the file each time new space is needed. A value of 0 indicates no increase. The value can be specified in MB, KB, or %. Specify a whole number; do not include a decimal. When % is specified, the increment size is the specified percentage of the file size 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.

<state_option>

Controls user access to the database, whether the database is online, and whether writes are allowed.

SINGLE_USER | RESTRICTED_USER | MULTI_USER
Controls which users may access the database. When SINGLE_USER is specified, only one user at a time can access the database. When RESTRICTED_USER is specified, only members of the db_owner, dbcreator, or sysadmin roles can use the database. MULTI_USER returns the database to its normal operating state.
OFFLINE | ONLINE
Controls whether the database is offline or online.
READ_ONLY | READ_WRITE
Specifies whether the database is in read-only mode. In read-only mode, users can read data from the database, not modify it. The database cannot be in use when READ_ONLY is specified. The master database is the exception, and only the system administrator can use master while READ_ONLY is set. READ_WRITE returns the database to read/write operations.

<cursor_option>

Controls cursor options.

CURSOR_CLOSE_ON_COMMIT ON | OFF
If ON is specified, any cursors open when a transaction is committed or rolled back are closed. If OFF is specified, such cursors remain open when a transaction is committed; rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.
CURSOR_DEFAULTLOCAL | GLOBAL
Controls whether cursor scope defaults to LOCAL or GLOBAL.

<auto_option>

Controls automatic options.

AUTO_CLOSE ON | OFF
If ON is specified, the database is shut down cleanly and its resources are freed after the last user exits. If OFF is specified, the database remains open after the last user exits.
AUTO_CREATE_STATISTICS ON | OFF
If ON is specified, any missing statistics needed by a query for optimization are automatically built during optimization.
AUTO_SHRINK ON | OFF
If ON is specified, the database files are candidates for automatic periodic shrinking.
AUTO_UPDATE_STATISTICS ON | OFF
If ON is specified, any out-of-date statistics required by a query for optimization are automatically built during optimization. If OFF is specified, statistics must be updated manually.

<sql_option>

Controls the ANSI compliance options.

ANSI_NULL_DEFAULT ON | OFF
If ON is specified, CREATE TABLE follows SQL-92 rules to determine whether a column allows null values.
ANSI_NULLS ON | OFF
If ON is specified, all comparisons to a null value evaluate to UNKNOWN. If OFF is specified, comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.
ANSI_PADDING ON | OFF
If ON is specified, strings are padded to the same length before comparison or insert. If OFF is specified, strings are not padded.
ANSI_WARNINGS ON | OFF
If ON is specified, errors or warnings are issued when conditions such as divide-by-zero occur.
ARITHABORT ON | OFF
If ON is specified, a query is terminated when an overflow or divide-by-zero error occurs during query execution.
CONCAT_NULL_YIELDS_NULL ON | OFF
If ON is specified, the result of a concatenation operation is NULL when either operand is NULL. If OFF is specified, the null value is treated as an empty character string. The default is OFF.
QUOTED_IDENTIFIER ON | OFF
If ON is specified, double quotation marks can be used to enclose delimited identifiers.
NUMERIC_ROUNDABORT ON | OFF
If ON is specified, an error is generated when loss of precision occurs in an expression.
RECURSIVE_TRIGGERS ON | OFF
If ON is specified, recursive firing of triggers is allowed. RECURSIVE_TRIGGERS OFF, the default, prevents direct recursion only. To disable indirect recursion as well, set the nested triggers server option to 0 using sp_configure.

<recovery_options>

Controls database recovery options.

RECOVERY FULL | BULK_LOGGED | SIMPLE
If FULL is specified, complete protection against media failure is provided. If a data file is damaged, media recovery can restore all committed transactions.

If BULK_LOGGED is specified, protection against media failure is combined with the best performance and least amount of log memory usage for certain large scale or bulk operations. These operations include SELECT INTO, bulk load operations (bcp and BULK INSERT), CREATE INDEX, and text and image operations (WRITETEXT and UPDATETEXT).

Under the bulk-logged recovery model, logging for the entire class is minimal and cannot be controlled on an operation-by-operation basis.

If SIMPLE is specified, a simple backup strategy that uses minimal log space is provided. Log space can be automatically reused when no longer needed for server failure recovery.

Important  The simple recovery model is easier to manage than the other two models but at the expense of higher data loss exposure if a data file is damaged. All changes since the most recent database or differential database backup are lost and must be re-entered manually.

The default recovery model is determined by the recovery model of the model database. To change the default for new databases, use ALTER DATABASE to set the recovery option of the model database.


TORN_PAGE_DETECTION ON | OFF
If ON is specified, incomplete pages can be detected. The default is ON.
Remarks

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

Before you apply a different or new collation to a database, ensure the following conditions are in place:

  1. You are the only one currently using the database.

  2. No schema bound object is dependent on the collation of the database.

    If the following objects, which are dependent on the database collation, exist in the database, the ALTER DATABASE database COLLATE statement will fail. SQL Server will return an error message for each object blocking the ALTER action:

    • User-defined functions and views created with SCHEMABINDING.

    • Computed columns.

    • CHECK constraints.

    • Table-valued functions that return tables with character columns with collations inherited from the default database collation.
  3. Altering the database collation does not create duplicates among any system names for the database objects.

    These namespaces may cause the failure of a database collation alteration if duplicate names result from the changed collation:

    • Object names (such as procedure, table, trigger, or view).

    • Schema names (such as group, role, or user).

    • Scalar-type names (such as system and user-defined types).

    • Full-text catalog names.

    • Column or parameter names within an object.

    • Index names within a table.

    Duplicate names resulting from the new collation will cause the alter action to fail and SQL Server will return an error message specifying the namespace where the duplicate was found.

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

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 sysadmin and dbcreator fixed server roles, and to members of the db_owner fixed database 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:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat1.ndf',
 SIZE = 5MB,
 MAXSIZE = 100MB,
 FILEGROWTH = 5MB
)
GO
ALTER DATABASE Test1 
ADD FILE 
(
 NAME = Test1dat2,
 FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\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:\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

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:\Program Files\Microsoft SQL Server\MSSQL\Data\test2log.ldf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB),
( NAME = test1log3,
  FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\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

CREATE DATABASE

DROP DATABASE

sp_helpdb

sp_helpfile

sp_helpfilegroup

sp_renamedb

sp_spaceused

Using Recovery Models

 '

Comments ( )
Link to this page: //www.vb-net.com/Sql/Sql/Ts_aa-az_4e5h.htm
< THANKS ME>