ALTER DATABASE 文件和文件组选项 (Transact-SQL)

修改与数据库关联的文件和文件组。在数据库中添加或删除文件和文件组、更改数据库或其文件和文件组的属性。有关其他 ALTER DATABASE 选项，请参阅 ALTER DATABASE (Transact-SQL)。

Transact-SQL 语法约定

语法

ALTER DATABASE database_name 
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ] 
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ] 
  | REMOVE FILE logical_file_name 
  | MODIFY FILE <filespec>
}

<filespec>::= 
(
    NAME = logical_file_name  
    [ , NEWNAME = new_logical_name ] 
    [ , FILENAME = {'os_file_name' | 'filestream_path' } ] 
    [ , SIZE = size [ KB | MB | GB | TB ] ] 
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 
    [ , OFFLINE ]
) 

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name 
        [ CONTAINS FILESTREAM ]
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option> 
        | DEFAULT
        | NAME = new_filegroup_name 
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE } 
    | { READ_ONLY | READ_WRITE }
}

参数

<add_or_modify_files>::=

指定要添加、删除或修改的文件。

• database_name
  要修改的数据库的名称。

• ADD FILE
  向数据库中添加文件。

  • TO FILEGROUP { filegroup_name }
    指定要将指定文件添加到的文件组。若要显示当前文件组和当前的默认文件组，请使用 sys.filegroups 目录视图。

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

• REMOVE FILE logical_file_name
  从 SQL Server 的实例中删除逻辑文件说明并删除物理文件。除非文件为空，否则无法删除文件。

  • logical_file_name
    在 SQL Server 中引用文件时所用的逻辑名称。

• MODIFY FILE
  指定应修改的文件。一次只能更改一个 <filespec> 属性。必须在 <filespec> 中指定 NAME，以标识要修改的文件。如果指定了 SIZE，那么新大小必须比文件当前大小要大。

  若要修改数据文件或日志文件的逻辑名称，请在 NAME 子句中指定要重命名的逻辑文件名称，并在 NEWNAME 子句中指定文件的新逻辑名称。例如：

  MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 
  

  若要将数据文件或日志文件移至新位置，请在 NAME 子句中指定当前的逻辑文件名称，并在 FILENAME 子句中指定新路径和操作系统文件名称。例如：

  MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )
  

  在移动全文目录时，请只在 FILENAME 子句中指定新路径。请不要指定操作系统文件名称。

  有关详细信息，请参阅移动数据库文件。

  对于 FILESTREAM 文件组，可以联机修改 NAME。可以联机修改 FILENAME；但是，只有在实际重新分配容器且关闭并重新启动服务器之后，所做的更改才生效。

  可以将 FILESTREAM 文件设置为 OFFLINE。在 FILESTREAM 文件处于脱机状态时，它的父文件组将在内部标记为脱机；因此，针对该文件组内 FILESTREAM 数据的所有访问均将失败。

<filespec>::=

控制文件属性。

• NAME logical_file_name
  指定文件的逻辑名称。

  • logical_file_name
    在 SQL Server 的实例中引用文件时所用的逻辑名称。

• NEWNAME new_logical_file_name
  指定文件的新逻辑名称。

  • new_logical_file_name
    用于替换现有逻辑文件名称的名称。该名称在数据库中必须唯一，并应符合标识符规则。该名称可以是字符或 Unicode 常量、常规标识符或分隔标识符。有关详细信息，请参阅使用标识符作为对象名称。

• FILENAME { 'os_file_name' | 'filestream_path' }
  指定操作系统（物理）文件名称。

  • ' os_file_name '
    对于标准 (ROWS) 文件组，这是在创建文件时操作系统所使用的路径和文件名。该文件必须驻留在安装 SQL Server 的服务器上。在执行 ALTER DATABASE 语句前，指定的路径必须已经存在。

    如果为该文件指定了 UNC 路径，则无法设置 SIZE、MAXSIZE 和 FILEGROWTH 参数。

    注意
    系统数据库不能位于 UNC 共享目录中。

    不应将数据文件放在压缩文件系统中，除非这些文件是只读辅助文件或该数据库是只读的。日志文件一定不要放在压缩文件系统中。有关详细信息，请参阅只读文件组和压缩。

    如果文件位于原始分区上，则 os_file_name 必须仅指定现有原始分区的驱动器号。每个原始分区上只能存放一个文件。

  • 'filestream_path'
    对于 FILESTREAM 文件组，FILENAME 指向将存储 FILESTREAM 数据的路径。在最后一个文件夹之前的路径必须存在，但不能存在最后一个文件夹。例如，如果指定路径 C:\MyFiles\MyFilestreamData、C:\MyFiles 必须存在才能运行 ALTER DATABASE，但 MyFilestreamData 文件夹不能存在。

    必须在同一语句中创建文件组和文件 (<filespec>)。只能有一个文件，FILESTREAM 文件组的 <filespec>,。

    SIZE、MAXSIZE 和 FILEGROWTH 属性不适用于 FILESTREAM 文件组。

• SIZE size
  指定文件大小。SIZE 不适用于 FILESTREAM 文件组。

  • size
    文件的大小。

    与 ADD FILE 一起指定时，size 是文件的初始大小。与 MODIFY FILE 一起指定时，size 是文件的新大小，而且必须大于文件的当前大小。

    如果没有为主文件提供 size，则 SQL Server 将使用 model 数据库中主文件的大小。如果指定了辅助数据文件或日志文件，但未指定该文件的 size，则数据库引擎将以 1 MB 作为该文件的大小。

    后缀 KB、MB、GB 和 TB 可用于指定千字节、兆字节、千兆字节或兆兆字节。默认值为 MB。指定整数，不包含小数。若要指定兆字节的分数，应通过乘以数字 1024 将该值转换为千字节。例如，应指定 1536 KB 而不是 1.5MB（1.5 x 1024 = 1536）。

• MAXSIZE { max_size| UNLIMITED }
  指定文件可增大到的最大文件大小。MAXSIZE 不适用于 FILESTREAM 文件组。

  • max_size
    最大的文件大小。后缀 KB、MB、GB 和 TB 可用于指定千字节、兆字节、千兆字节或兆兆字节。默认值为 MB。指定整数，不包含小数。如果未指定 max_size，则文件大小将一直增加，直至磁盘已满。

  • UNLIMITED
    指定文件将增长到磁盘充满。在 SQL Server 中，指定为不限制增长的日志文件的最大大小为 2 TB，而数据文件的最大大小为 16 TB。

• FILEGROWTH growth_increment
  指定文件的自动增量。文件的 FILEGROWTH 设置不能超过 MAXSIZE 设置。FILEGROWTH 不适用于 FILESTREAM 文件组。

  • growth_increment
    每次需要新空间时为文件添加的空间量。

    该值可以 MB、KB、GB、TB 或百分比 (%) 为单位指定。如果未在数量后面指定 MB、KB 或 %，则默认值为 MB。如果指定 %，则增量大小为发生增长时文件大小的指定百分比。指定的大小舍入为最接近的 64 KB 的倍数。

    如果值为 0，则表明自动增长被设置为关闭，且不允许增加空间。

    如果未指定 FILEGROWTH，则数据文件的默认值为 1 MB，日志文件的默认增长比例为 10%，并且最小值为 64 KB。

    注意
    从 SQL Server 2005 开始，数据文件的默认增量已从 10% 改为 1 MB。日志文件的默认值仍然为 10%。

• OFFLINE
  将文件设置为脱机并使文件组中的所有对象都不可访问。

  注意
  仅当文件已损坏但可以还原时，才能使用该选项。对于设置为 OFFLINE 的文件，只有通过从备份中还原该文件，才能将其设置为联机。有关还原单个文件的详细信息，请参阅 RESTORE (Transact-SQL)。

<add_or_modify_filegroups>::=

在数据库中添加、修改或删除文件组。

• ADD FILEGROUP filegroup_name
  向数据库中添加文件组。

• CONTAINS FILESTREAM
  指定文件组在文件系统中存储 FILESTREAM 二进制大型对象 (BLOB)。

• REMOVE FILEGROUP filegroup_name
  从数据库中删除文件组。除非文件组为空，否则无法将其删除。首先从文件组中删除所有文件。有关详细信息，请参阅本主题前面的“REMOVE FILE logical_file_name”部分。

• MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME = new_filegroup_name }
  通过将状态设置为 READ_ONLY 或 READ_WRITE、将文件组设置为数据库的默认文件组或者更改文件组名称来修改文件组。

  • <filegroup_updatability_option>
    对文件组设置只读或读/写属性。

  • DEFAULT
    将默认数据库文件组改为 filegroup_name。数据库中只能有一个文件组作为默认文件组。有关详细信息，请参阅了解文件和文件组。

  • NAME = new_filegroup_name
    将文件组名称改为 new_filegroup_name。

<filegroup_updatability_option>::=

对文件组设置只读或读/写属性。

• READ_ONLY | READONLY
  指定文件组为只读。不允许更新其中的对象。主文件组不能设置为只读。若要更改此状态，您必须对数据库有独占访问权限。有关详细信息，请参阅 SINGLE_USER 子句。

  因为只读数据库不允许数据修改，所以将发生以下情况：

  • 系统启动时，将跳过自动恢复。

  • 不能收缩数据库。

  • 在只读数据库中不会进行锁定。这可以加快查询速度。

  注意
  在 Microsoft SQL Server 的未来版本中，将删除 READONLY 关键字。请避免在新的开发工作中使用 READONLY，并计划修改当前使用 READONLY 的应用程序。Use READ_ONLY instead.

• READ_WRITE | READWRITE
  将该组指定为 READ_WRITE。允许更新文件组中的对象。若要更改此状态，您必须对数据库有独占访问权限。有关详细信息，请参阅 SINGLE_USER 子句。

  注意
  在 Microsoft SQL Server 的未来版本中，将删除 READWRITE 关键字。避免在新的开发工作中使用 READWRITE，并计划修改当前使用 READWRITE 的应用程序。请改用 READ_WRITE。

这些选项的状态可通过查看 sys.databases 目录视图中的 is_read_only 列或 DATABASEPROPERTYEX 函数的 Updateability 属性来确定。

注释

若要减小数据库的大小，请使用 DBCC SHRINKDATABASE。

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

可以为每个数据库指定最多 32,767 个文件和 32,767 个文件组。

在 SQL Server 2005 或更高版本中，对数据库文件状态（例如，联机或脱机）的维护是独立于数据库状态的。有关详细信息，请参阅文件状态。文件组中文件的状态决定整个文件组的可用性。文件组中的所有文件都必须联机，文件组才可用。如果文件组脱机，则使用 SQL 语句访问文件组的所有尝试都会失败并报告错误。在为 SELECT 语句生成查询计划时，查询优化器会避免驻留在脱机文件组中的非聚集索引和索引视图。这样，这些语句就会成功。但是，如果脱机文件组包含目标表的堆或聚集索引，SELECT 语句将失败。此外，如果 INSERT、UPDATE 或 DELETE 语句所修改表的索引包含在脱机文件组中，这些语句将失败。

移动文件

在 SQL Server 2005 或更高版本中，可通过在 FILENAME 中指定新位置来移动系统或用户定义的数据和日志文件。这在下列情况下可能很有用：

• 故障恢复。例如，数据库处于可疑模式或因硬件故障而关闭。

• 计划的重定位。

• 为预定的磁盘维护操作而进行的重定位。

有关详细信息，请参阅移动数据库文件。

初始化文件

默认情况下，在执行下列操作之一时，将通过在文件中填充零来初始化数据和日志文件。

• 创建数据库。

• 向现有数据库添加文件。

• 增加现有文件的大小。

• 还原数据库或文件组。

可以在瞬间对数据文件进行初始化。这样，可以快速执行这些文件操作。有关详细信息，请参阅数据库文件初始化。

示例

A. 向数据库中添加文件

以下示例将一个 5 MB 的数据文件添加到 AdventureWorks2008R2 数据库。

USE master;
GO
ALTER DATABASE AdventureWorks2008R2 
ADD FILE 
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

B. 向数据库中添加由两个文件组成的文件组

以下示例在 AdventureWorks2008R2 数据库中创建文件组 Test1FG1，然后将两个 5 MB 的文件添加到该文件组。

USE master
GO
ALTER DATABASE AdventureWorks2008R2
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2008R2 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

C. 向数据库中添加两个日志文件

以下示例向 AdventureWorks2008R2 数据库中添加两个 5 MB 的日志文件。

USE master;
GO
ALTER DATABASE AdventureWorks2008R2 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test3log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

D. 从数据库中删除文件

以下示例删除示例 B 中添加的一个文件。

USE master;
GO
ALTER DATABASE AdventureWorks2008R2
REMOVE FILE test1dat4;
GO

E. 修改文件

以下示例增加示例 B 中添加的一个文件的大小。

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

F. 将文件移至新位置

以下示例将在示例 A 中创建的 Test1dat2 文件移至新目录中。

注意
必须先将该文件实际移至新目录中，然后才能运行此示例。然后，停止和启动 SQL Server 的实例，或使 AdventureWorks2008R2 数据库 OFFLINE 再 ONLINE，以实施更改。

USE master;
GO
ALTER DATABASE AdventureWorks2008R2
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G. 将 tempdb 移至新位置

以下示例将 tempdb 从其在磁盘上的当前位置移至另一个磁盘位置。由于每次启动 MSSQLSERVER 服务时都会重新创建 tempdb，因此您不必实际移动数据和日志文件。在步骤 3 中重新启动服务时，将创建这些文件。在重新启动该服务之前，tempdb 将继续在现有位置发挥作用。

1. 确定 tempdb 数据库的逻辑文件名称以及这些文件在磁盘上的当前位置。

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   GO
   

2. 使用 ALTER DATABASE 更改每个文件的位置。

   USE master;
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   ALTER DATABASE  tempdb 
   MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
   GO
   

3. 停止再重新启动 SQL Server 的实例。

4. 验证文件更改。

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   

5. 将 tempdb.mdf 和 templog.ldf 文件从其原始位置中删除。

H. 使文件组成为默认文件组

以下示例使示例 B 中创建的 Test1FG1 文件组成为默认文件组。然后，默认文件组被重置为 PRIMARY 文件组。请注意，必须使用括号或引号分隔 PRIMARY。

USE master;
GO
ALTER DATABASE AdventureWorks2008R2 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2008R2 
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

I. 使用 ALTER DATABASE 添加文件组

以下示例将一个包含 FILESTREAM 子句的 FILEGROUP 添加到 FileStreamPhotoDB 数据库。

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause to
--the FileStreamPhotoDB database.
ALTER database FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM
GO

--Add a file for storing database photos to FILEGROUP 
ALTER database FileStreamPhotoDB
ADD FILE
(
    NAME= 'PhotoShoot1',
    FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot
GO

请参阅

参考

CREATE DATABASE (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DROP DATABASE (Transact-SQL)

sp_spaceused (Transact-SQL)

sys.databases (Transact-SQL)

sys.database_files (Transact-SQL)

sys.data_spaces (Transact-SQL)

sys.filegroups (Transact-SQL)

sys.master_files (Transact-SQL)

其他资源

设计和实现 FILESTREAM 存储