在数据库中添加或删除文件和文件组。也可用于更改文件和文件组的属性，例如更改文件的名称和大小。ALTER DATABASE 提供了更改数据库名称、文件组名称以及数据文件和日志文件的逻辑名称的能力。

ALTER DATABASE 支持数据库选项的设置。在早期版本的 Microsoft® SQL Server™ 中，这些选项可以通过 sp_dboption 存储过程来设置。在此次发布的版本中，SQL Server 继续支持 sp_dboption存储过程，但在未来版本中可能不再支持。可使用 DATABASEPROPERTYEX 函数检索数据库选项的当前设置。

语法

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 > ::=

< optionspec > ::=

参数

database

是要更改的数据库的名称。

ADD FILE

指定要添加文件。

TO FILEGROUP

指定要将指定文件添加到的文件组。

filegroup_name

是要添加指定文件的文件组名称。

ADD LOG FILE

指定要将日志文件添加到指定的数据库。

REMOVE FILE

从数据库系统表中删除文件描述并删除物理文件。只有在文件为空时才能删除。

ADD FILEGROUP

指定要添加文件组。

filegroup_name

是要添加或除去的文件组名称。

REMOVE FILEGROUP

从数据库中删除文件组并删除该文件组中的所有文件。只有在文件组为空时才能删除。

MODIFY FILE

指定要更改给定的文件，更改选项包括 FILENAME、SIZE、FILEGROWTH 和 MAXSIZE。一次只能更改这些属性中的一种。必须在 <filespec> 中指定 NAME，以标识要更改的文件。如果指定了 SIZE，那么新大小必须比文件当前大小要大。只能为 tempdb 数据库中的文件指定 FILENAME，而且新名称只有在 Microsoft SQL Server 重新启动后才能生效。

若要更改数据文件或日志文件的逻辑名称，应在 NAME 选项中指定要改名的逻辑文件名称，并在 NEWNAME 选项中指定文件的新逻辑名称。

示例如下：

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

可同时运行几个 ALTER DATABASE database MODIFY FILE 语句以实现多个修改文件操作时性能最优。

MODIFY NAME = new_dbname

重命名数据库。

MODIFY FILEGROUP filegroup_name { filegroup_property | NAME = new_filegroup_name }

指定要修改的文件组和所需的改动。

如果指定 filegroup_name 和 NAME = new_filegroup_name，则将此文件组的名称改为 new_filegroup_name。

如果指定 filegroup_name 和 filegroup_property，则表示给定文件组属性将应用于此文件组。filegroup_property 的值有：

READONLY

指定文件组为只读。不允许更新其中的对象。主文件组不能设置为只读。只有具有排它数据库访问权限的用户才能将文件组标记为只读。

READWRITE

逆转 READONLY 属性。允许更新文件组中的对象。只有具有排它数据库访问权限的用户才能将文件组标记为读/写。

DEFAULT

将文件组指定为默认数据库文件组。只能有一个数据库文件组是默认的。CREATE DATABASE 语句将主文件组设置为初始的默认文件组。如果在 CREATE TABLE、ALTER TABLE 或者 CREATE INDEX 语句中没有指定文件组，则新表及索引将在默认文件组—中创建。

WITH <termination>

指定当数据库从一种状态转换到另一种状态时，何时回滚未完成的事务。只能指定一条 termination 子句，而且该子句应跟在 SET 子句后面。

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE

设定是在指定秒数之后回滚还是立即回滚。如果省略了 termination 子句，那么将允许事务自主提交或回滚。

NO_WAIT

指定如果请求的数据库语句或选项更改只有等待事务自主提交或回滚才能立即完成，该请求将失败。

COLLATE < collation_name >

指定数据库的排序规则。排序规则名称既可以是 Windows 排序规则名称，也可以是 SQL 排序规则名称。如果没有指定排序规则，则将 SQL Server 实例的默认排序规则指派为数据库的排序规则。

有关 Windows 和 SQL 排序规则名称的更多信息，请参见 COLLATE。

<filespec>

控制文件属性。

NAME

指定文件的逻辑名称。

logical_file_name

是在 Microsoft SQL Server 中引用文件时所用的名称。该名称在数据库中必须唯一，并应遵循标识符规则。该名称可以是字符或 Unicode 常量、常规标识符或定界标识符。有关更多信息，请参见使用标识符。

FILENAME

指定操作系统中的文件名。当和 MODIFY FILE一起使用时，只能对 tempdb 数据库中的文件指定 FILENAME。新 tempdb 文件名只有在 SQL Server 停止并重新启动后才能生效。

'os_file_name'

由操作系统使用的文件的路径和文件名。该文件必须驻留在安装 SQL Server 的服务器上。数据文件和日志文件不应置于压缩的文件系统中。

如果文件位于原始分区上，那么 os_file_name 必须仅指定该原始分区的驱动器字母。每个原始分区上只能存放一个文件。原始分区上的文件不会自动增长；因此，os_file_name 指定原始分区时，不需要指定 MAXSIZE 和 FILEGROWTH 参数。

SIZE

指定文件大小。

size

是文件的大小。后缀 KB、MB、GB 和 TB 可用于指定千字节、兆字节、千兆字节或兆兆字节。默认值为 MB。指定一个整数，不要包含小数位。size 的最小值是 512 KB，如果 size 未指定，默认值是 1 MB。当随 ADD FILE 一起指定时，size 是文件的初始大小。当随 MODIFY FILE 一起指定时，size 是文件的新大小，而且必须大于文件的当前大小。

MAXSIZE

指定最大的文件大小。

max_size

是最大的文件大小。后缀 KB、MB、GB 和 TB 可用于指定千字节、兆字节、千兆字节或兆兆字节。默认值为 MB。指定一个整数，不要包含小数位。如果没有指定 max_size，那么文件大小将一直增长，直至磁盘填满为止。当磁盘即将填满时，Microsoft Windows NT® 应用程序日志将向管理员发出警告。

UNLIMITED

指定文件大小可一直增加直至磁盘已满。

FILEGROWTH

指定文件增长的增量。

growth_increment

每次需要新的空间时为文件添加的空间大小。值为 0 表示不增长。该值可按 MB、KB 或 % 的形式指定。指定一个整数，不要包含小数位。如果指定了 %，那么文件增量为文件发生增长时文件大小的指定百分比。如果未在数量后面指定 MB、KB 或 %，则默认值为 MB。如果 FILEGROWTH 未指定，默认值为 10%，且最小值为 64 KB。指定的大小舍入为最接近的 64 KB 的倍数。

<state_option>

控制用户对数据库的访问，数据库是否处于联机状态，以及是否允许写操作。

SINGLE_USER | RESTRICTED_USER | MULTI_USER

控制哪些用户可以访问数据库。如果指定为 SINGLE_USER，那么同一时间只能有一个用户访问数据库。如果指定为 RESTRICTED_USER，那么只有 db_owner、dbcreator 或 sysadmin 角色的成员可以使用数据库。MULTI_USER 使数据库返回到正常操作状态。

OFFLINE | ONLINE

控制数据库是联机还是脱机。

READ_ONLY | READ_WRITE

指定数据库是否处于只读模式。在只读模式下，用户可以从数据库中读取数据，但不能修改数据。当指定 READ_ONLY 时，数据库不能处于使用状态。master 数据库是一个例外，当该数据库设置为 READ_ONLY 时，只有系统管理员才能使用该数据库。READ_WRITE 使数据库返回到读写操作状态。

<cursor_option>

控制游标选项。

CURSOR_CLOSE_ON_COMMIT ON | OFF

如果指定为 ON，在事务提交或回滚时所有打开的游标都将关闭。如果指定为 OFF，那么在事务提交时打开的游标仍保持打开；而回滚事务时关闭所有除定义为 INSENSITIVE 或 STATIC 之外的游标。

CURSOR_DEFAULTLOCAL | GLOBAL

控制游标作用域默认为 LOCAL 还是 GLOBAL。

<auto_option>

控制自动选项。

AUTO_CLOSE ON | OFF

如果指定为 ON，那么最后一个用户退出后，数据库将干净地关闭，其占用的资源将释放。如果指定为 OFF，那么最后一个用户退出后数据库仍保持打开。

AUTO_CREATE_STATISTICS ON | OFF

如果指定为 ON，那么所有优化查询需要但缺少的统计信息都会在查询优化时自动生成。

AUTO_SHRINK ON | OFF

如果指定为 ON，数据库文件将定期自动收缩。

AUTO_UPDATE_STATISTICS ON | OFF

如果指定为 ON，所有查询优化所需的过时的统计信息在都将在优化时自动重建。如果指定为 OFF，统计信息必须手工更新。

<sql_option>

控制 ANSI 遵从性选项。

ANSI_NULL_DEFAULT ON | OFF

如果指定为 ON，CREATE TABLE 在确定列是否允许空值时遵从 SQL-92 规则。

ANSI_NULLS ON | OFF

如果指定为 ON，所有与空值的比较运算结果为 UNKNOWN。如果指定为 OFF，非 UNICODE 值与空值的比较运算在两者均为 NULL 时结果为 TRUE。

ANSI_PADDING ON | OFF

如果指定为 ON，在比较或插入前，字符串将填充为同一长度。如果指定为 OFF，字符串将不填充。

ANSI_WARNINGS ON | OFF

如果指定为 ON，当出现诸如被零除的情况时将产生错误或警告。

ARITHABORT ON | OFF

如果指定为 ON，在执行查询时如果发生溢出或被零除，该查询将终止。

CONCAT_NULL_YIELDS_NULL ON | OFF

如果指定为 ON，当串联操作的两个操作数中任意一个为 NULL 时，结果也为 NULL。如果指定为 OFF，空值将按空字符串对待。默认设置为 OFF。

QUOTED_IDENTIFIER ON | OFF

如果指定为 ON，双引号将可用于包含定界标识符。

NUMERIC_ROUNDABORT ON | OFF

如果指定为 ON，当表达式中出现精度损失时将产生错误。

RECURSIVE_TRIGGERS ON | OFF

如果指定为 ON，将允许递归激发触发器。RECURSIVE_TRIGGERS OFF（默认值）只禁止直接递归。若要也禁用间接递归，请使用 sp_configure 将 nested triggers 服务器选项设置为 0。

<recovery_options>

控制数据库恢复选项。

RECOVERY FULL | BULK_LOGGED | SIMPLE

如果指定为 FULL，系统将对介质错误提供完全保护。如果数据文件损坏，介质恢复可以还原所有已提交的事务。

如果指定为 BULK_LOGGED，将在介质错误保护程度与某些大规模或大容量操作的最优性能及日志存储空间最少占用量之间进行权衡。这些操作包括 SELECT INTO、大容量装载操作（bcp 和 BULK INSERT）、CREATE INDEX 以及文本和图象操作（WRITETEXT 和 UPDATETEXT）。

在大容量日志记录恢复模型下，对整个类只做最少的日志记录，并且无法逐个操作地控制日志记录行为。

如果指定为 SIMPLE，系统将提供占用日志空间最小的备份策略。服务器故障恢复不再需要的日志空间可被自动重用。

重要  简单恢复模型比其它两种模型更容易管理，但数据文件损坏时造成数据丢失的可能性更大。最近的数据库备份或差异数据库备份之后的更改都将丢失，必须手工重新输入。

默认恢复模型由 model 数据库的恢复模型确定。若要更改新数据库的默认值，请用 ALTER DATABASE 设置 model 数据库的恢复选项。

TORN_PAGE_DETECTION ON | OFF

如果指定为 ON，将可以检测到未完成的页。默认设置为 ON。

注释

若要删除数据库，请使用 DROP DATABASE 语句。若要更改数据库名称，请使用 sp_renamedb。有关减小数据库大小的更多信息，请参见 DBCC SHRINKDATABASE。

在对数据库应用不同的或新的排序规则时，应确保符合以下条件：

1. 您是当前数据库的唯一用户。
   
   



2. 没有依赖数据库排序规则的架构绑定对象。
   

   如果数据库中存在以下依赖数据库排序规则的对象，则 ALTER DATABASE database COLLATE 语句将失败。SQL Server 将针对每一个阻塞 ALTER 操作的对象返回一个错误信息：

   
   
   
   
   • 通过 SCHEMABINDING 创建的用户定义函数和视图。
     
     
   
   
   
   • 计算列。
     
     
   
   
   
   • CHECK 约束。
     
     
   
   
   
   • 表值函数返回包含字符列的表，这些列继承了默认的数据库排序规则。
   
   
   
   



3. 改变数据库的排序规则不会在任何数据对象的系统名称中产生重复名称。
   

   如果改变排序规则后出现重复的名称，则下列命名空间可能导致改变数据库排序规则的操作失败：

   
   
   
   
   • 对象名（如过程、表、触发器或视图）。
     
     
   
   
   
   • 架构名（如组、角色或用户）。
     
     
   
   
   
   • 标量类型名（如系统和用户定义类型）。
     
     
   
   
   
   • 全文目录名称。
     
     
   
   
   
   • 对象内的列名或参数名。
     
     
   
   
   
   • 表范围内的索引名。
   
   
   
   
   

   由新的排序规则产生的重复名称将导致改变操作失败，SQL Server 将返回错误信息，指出重复名称所在的命名空间。

当 BACKUP 语句正在执行时，不能添加或删除文件。

若要在 size 参数中指定兆字节的分数，应通过乘以数字 1024 将该值转换为千字节。例如，应指定 1536 KB 而不是 1.5MB（1.5 x 1024 = 1536）。

权限

ALTER DATABASE 权限默认授予 sysadmin 和 dbcreator 固定服务器角色成员以及 db_owner 固定数据库角色成员。这些权限是不可传递的。

示例

A. 向数据库中添加文件

下例创建数据库，并更改该数据库以添加一个 5 MB 大小的新数据文件。

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. 向数据库中添加由两个文件组成的文件组

下例在示例 A 中所创建的 Test1 数据库中创建一个文件组，并将两个 5 MB 的文件添加到该文件组。然后下例将 Test1FG1 设置为默认文件组。

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. 向数据库中添加两个日志文件

下例向数据库中添加两个 5 MB 大小的日志文件。

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. 从数据库中删除文件

下例将示例 B 中添加到数据库 Test1 中的一个文件删除。

USE master
GO
ALTER DATABASE Test1 
REMOVE FILE test1dat4
GO

E. 更改文件

下例将示例 B 中添加到数据库 Test1 中的一个文件增加大小。

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

F. 将主文件组设置为默认文件组

假定原来另一个文件组设置为默认文件组，下例将主文件组设置为默认文件组。

USE master
GO
ALTER DATABASE MyDatabase 
MODIFY FILEGROUP [PRIMARY] DEFAULT
GO