本文介绍如何使用SQL Server的ALTER DATABASE语句扩展、收缩数据库及文件，管理文件组，设置数据库选项，以及重命名数据库。
本节书摘来自异步社区出版社《锋利的SQL（第2版）》一书中的第2章，第2.3节，作者：张洪举 王晓文，更多章节内容可以访问云栖社区“异步社区”公众号查看。
2.3 修改数据库
锋利的SQL（第2版）
创建数据库后，可以对其原始定义进行更改，如扩展或收缩数据库、设置数据库选项等。要修改数据库，可以使用ALTER DATABASE等语句。
2.3.1 扩展数据库和文件
默认情况下，SQL Server可根据创建数据库时定义的增长参数自动扩展数据库。也可以通过为现有数据库文件分配更多空间，或者创建新文件来手动扩展数据库。如果未将数据库设置为自动增长或硬盘上没有足够的磁盘空间，数据库已经用完分配给它的空间且不能自动增长，会出现1105错误。
扩展数据库时，必须使数据库的大小至少增加1 MB。如果扩展了数据库，则根据被扩展的文件，数据文件或事务日志文件将可以立即使用新空间。扩展数据库时，应指定允许文件增长到的最大大小。这样可防止文件无限制地增大，以致于用尽整个磁盘空间。
可以使用ALTER DATABASE语句设置数据库大小或向数据库添加文件，其语法格式如下：
ALTER DATABASE database_name
ADD FILE <filespec> [ ,...n ]
[ TO FILEGROUP { filegroup_name | DEFAULT } ]
| ADD LOG FILE <filespec> [ ,...n ]
| REMOVE FILE logical_file_name
| MODIFY FILE <filespec>
其中部分用于设置文件组的属性，语法格式如下：
(
NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
[ , OFFLINE ]
)
其中的OFFLINE选项用于将文件设置为脱机并使文件组中的所有对象都不可访问，仅在文件已损坏但可以还原时，才能使用该选项。其他参数选项请参考前面CREATE DATABASE语句中的说明。
例如，下面的语句用于将Sales中的SPri1_dat文件扩展到15 MB，并将最大值设置为25 MB。
ALTER DATABASE Sales
MODIFY FILE
(
NAME = 'SPri1_dat',
SIZE = 15MB,
MAXSIZE = 25MB
)
下面的语句向SalesGroup1文件组中添加一个SGrp1Fi3_dat文件。
ALTER DATABASE Sales
ADD FILE
(
NAME = SGrp1Fi3_dat,
FILENAME = 'c:\SG1Fi3dt.ndf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 5MB
)
TO FILEGROUP SalesGroup1 ;
执行下面的语句则可以删除上面添加的SGrp1Fi3_dat文件。
ALTER DATABASE Sales
REMOVE FILE SGrp1Fi3_dat ;
2.3.2 向数据库中添加、删除和修改文件组
下面是使用ALTER DATABASE语句向数据库中添加、删除和修改文件组时的语法格式：
ALTER DATABASE database_name
ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILEGROUP filegroup_name
{ <filegroup_updatability_option>
| DEFAULT
| NAME = new_filegroup_name
}
部分的语法格式如下：
{ READONLY | READWRITE } | { READ_ONLY | READ_WRITE }
例如，下面的语句用于向Sales数据库中添加一个名为SalesGroup3的文件组。
ALTER DATABASE Sales
ADD FILEGROUP SalesGroup3 ;
下面的语句重命名文件组SalesGroup3为SalesGroup4。
ALTER DATABASE Sales
MODIFY FILEGROUP SalesGroup3
NAME = SalesGroup4 ;
2.3.3 收缩数据库和文件
可以使用DBCC SHRINKDATABASE语句或DBCC SHRINKFILE语句来手动收缩数据库或数据库中的文件。数据库中的每个文件都可以通过删除未使用的页的方法来减小。尽管数据库引擎会有效地重新使用空间，但某个文件多次出现无须原来大小的情况后，收缩文件就变得很有必要了。可以成组或单独地手动收缩数据库文件，也可以设置数据库的AUTO_SHRINK选项为ON来指定按间隔自动收缩。
文件始终从末尾开始收缩。例如，如果有个5 GB的文件，并且在DBCC SHRINKFILE语句中指定为4 GB，则数据库引擎将从文件的最后一个1 GB开始释放尽可能多的空间。如果文件中被释放的部分包含使用过的页，则数据库引擎先将这些页重新放置到文件的保留部分。只能将数据库收缩到没有剩余的可用空间为止。例如，如果某个5 GB的数据库有4 GB的数据，并且在DBCC SHRINKFILE语句中指定为 3 GB，则只能释放1 GB。
在使用DBCC SHRINKDATABASE语句时，无法将整个数据库收缩得比其初始大小更小。例如，如果数据库创建时的大小为10 MB，后来增长到100 MB，则该数据库最小只能收缩到10 MB，即使已经删除数据库的所有数据也是如此。
但是，使用DBCC SHRINKFILE语句时，可以将各个数据库文件收缩得比其初始大小更小。必须对每个文件分别进行收缩，而不能尝试收缩整个数据库。
1．手动收缩数据库
下面是DBCC SHRINKDATABASE语句的语法格式：
DBCC SHRINKDATABASE
( 'database_name' | database_id | 0
[ ,target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]
'database_name' | database_id | 0
要收缩的数据库的名称或ID。如果指定0，则使用当前数据库。
target_percent
数据库收缩后的数据库文件中所需的剩余可用空间百分比。
NOTRUNCATE
指定在数据库文件中保留所释放的文件空间。如果未指定，将所释放的文件空间释放给操作系统。
TRUNCATEONLY
将数据文件中任何未使用空间释放给操作系统，并将文件收缩到最后分配的区，从而无须移动任何数据即可减小文件大小。使用TRUNCATEONLY时，将忽略target_percent设置。
WITH NO_INFOMSGS
取消严重级别从0到10的所有信息性消息。
下面的语句使Sales数据库中文件有10%的可用空间。
DBCC SHRINKDATABASE ('Sales', 10)
2．使用ALTER DATABASE设置自动收缩数据库
将数据库的AUTO_SHRINK选项设置为ON后，数据库引擎将自动收缩有可用空间的数据库。下面是使用ALTER DATABASE语句将Sales数据库的AUTO_SHRINK选项设置为ON的方法。
ALTER DATABASE Sales
SET AUTO_SHRINK ON ;
3．收缩文件
下面是DBCC SHRINKFILE语句的语法格式：
DBCC SHRINKFILE
(
{ 'file_name' | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
'file_name'
要收缩的文件的逻辑名称。
file_id
要收缩的文件的标识（ID）号。可以使用FILE_ID函数获取文件的ID。
target_size
用兆字节表示的文件大小。如果未指定，则DBCC SHRINKFILE将文件大小减少到默认文件大小。
EMPTYFILE
将指定文件中的所有数据迁移到同一文件组中的其他文件。
NOTRUNCATE
将释放的文件空间保留在文件中。当与target_size一起指定NOTRUNCATE时，释放的空间不会释放给操作系统。唯一影响是将已使用的页从target_size行前面重新定位到文件的前面。
TRUNCATEONLY
将文件中的任何未使用空间释放给操作系统，并将文件收缩到最后一次分配的区，从而减小了文件大小，但是没有移动任何数据。不会尝试将行重新定位到未分配的页。使用TRUNCATEONLY时，将忽略target_size。
WITH NO_INFOMSGS
禁止显示所有信息性消息。
例如，下面的语句将Sales数据库中的SPri1_dat文件的大小收缩到8 MB。
USE Sales ;
GO
DBCC SHRINKFILE (SPri1_dat, 8) ;
以下示例演示了清空文件以便从数据库中将其删除的步骤。针对此示例，首先创建一个数据文件，并假设该文件包含数据。
USE AdventureWorks;
GO
-- 创建一个数据文件并假设其包含数据
ALTER DATABASE AdventureWorks
ADD FILE (
NAME = Test1data,
FILENAME = 'C:\t1data.ndf',
SIZE = 5MB
);
GO
-- 清空数据文件
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- 从数据库中移除数据文件
ALTER DATABASE AdventureWorks
REMOVE FILE Test1data;
GO
2.3.4 设置数据库选项
可以在新建数据库时或对现有数据库通过“数据库属性”窗口进行部分数据库选项设置，而使用ALTER DATABASE的SET子句则可以进行更加全面的选项设置。可用的设置选项如表2-1所示。
表2-1 可用的数据库设置选项
2.3 修改数据库
创建数据库后，可以对其原始定义进行更改，如扩展或收缩数据库、设置数据库选项等。要修改数据库，可以使用ALTER DATABASE等语句。
2.3.1 扩展数据库和文件
默认情况下，SQL Server可根据创建数据库时定义的增长参数自动扩展数据库。也可以通过为现有数据库文件分配更多空间，或者创建新文件来手动扩展数据库。如果未将数据库设置为自动增长或硬盘上没有足够的磁盘空间，数据库已经用完分配给它的空间且不能自动增长，会出现1105错误。
扩展数据库时，必须使数据库的大小至少增加1 MB。如果扩展了数据库，则根据被扩展的文件，数据文件或事务日志文件将可以立即使用新空间。扩展数据库时，应指定允许文件增长到的最大大小。这样可防止文件无限制地增大，以致于用尽整个磁盘空间。
可以使用ALTER DATABASE语句设置数据库大小或向数据库添加文件，其语法格式如下：
ALTER DATABASE database_name
ADD FILE <filespec> [ ,...n ]
[ TO FILEGROUP { filegroup_name | DEFAULT } ]
| ADD LOG FILE <filespec> [ ,...n ]
| REMOVE FILE logical_file_name
| MODIFY FILE <filespec>
其中部分用于设置文件组的属性，语法格式如下：
(
NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
[ , OFFLINE ]
)
其中的OFFLINE选项用于将文件设置为脱机并使文件组中的所有对象都不可访问，仅在文件已损坏但可以还原时，才能使用该选项。其他参数选项请参考前面CREATE DATABASE语句中的说明。
例如，下面的语句用于将Sales中的SPri1_dat文件扩展到15 MB，并将最大值设置为25 MB。
ALTER DATABASE Sales
MODIFY FILE
(
NAME = 'SPri1_dat',
SIZE = 15MB,
MAXSIZE = 25MB
)
下面的语句向SalesGroup1文件组中添加一个SGrp1Fi3_dat文件。
ALTER DATABASE Sales
ADD FILE
(
NAME = SGrp1Fi3_dat,
FILENAME = 'c:\SG1Fi3dt.ndf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 5MB
)
TO FILEGROUP SalesGroup1 ;
执行下面的语句则可以删除上面添加的SGrp1Fi3_dat文件。
ALTER DATABASE Sales
REMOVE FILE SGrp1Fi3_dat ;
2.3.2 向数据库中添加、删除和修改文件组
下面是使用ALTER DATABASE语句向数据库中添加、删除和修改文件组时的语法格式：
ALTER DATABASE database_name
ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILEGROUP filegroup_name
{ <filegroup_updatability_option>
| DEFAULT
| NAME = new_filegroup_name
}
部分的语法格式如下：
{ READONLY | READWRITE } | { READ_ONLY | READ_WRITE }
例如，下面的语句用于向Sales数据库中添加一个名为SalesGroup3的文件组。
ALTER DATABASE Sales
ADD FILEGROUP SalesGroup3 ;
下面的语句重命名文件组SalesGroup3为SalesGroup4。
ALTER DATABASE Sales
MODIFY FILEGROUP SalesGroup3
NAME = SalesGroup4 ;
2.3.3 收缩数据库和文件
可以使用DBCC SHRINKDATABASE语句或DBCC SHRINKFILE语句来手动收缩数据库或数据库中的文件。数据库中的每个文件都可以通过删除未使用的页的方法来减小。尽管数据库引擎会有效地重新使用空间，但某个文件多次出现无须原来大小的情况后，收缩文件就变得很有必要了。可以成组或单独地手动收缩数据库文件，也可以设置数据库的AUTO_SHRINK选项为ON来指定按间隔自动收缩。
文件始终从末尾开始收缩。例如，如果有个5 GB的文件，并且在DBCC SHRINKFILE语句中指定为4 GB，则数据库引擎将从文件的最后一个1 GB开始释放尽可能多的空间。如果文件中被释放的部分包含使用过的页，则数据库引擎先将这些页重新放置到文件的保留部分。只能将数据库收缩到没有剩余的可用空间为止。例如，如果某个5 GB的数据库有4 GB的数据，并且在DBCC SHRINKFILE语句中指定为 3 GB，则只能释放1 GB。
在使用DBCC SHRINKDATABASE语句时，无法将整个数据库收缩得比其初始大小更小。例如，如果数据库创建时的大小为10 MB，后来增长到100 MB，则该数据库最小只能收缩到10 MB，即使已经删除数据库的所有数据也是如此。
但是，使用DBCC SHRINKFILE语句时，可以将各个数据库文件收缩得比其初始大小更小。必须对每个文件分别进行收缩，而不能尝试收缩整个数据库。
1．手动收缩数据库
下面是DBCC SHRINKDATABASE语句的语法格式：
DBCC SHRINKDATABASE
( 'database_name' | database_id | 0
[ ,target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]
'database_name' | database_id | 0
要收缩的数据库的名称或ID。如果指定0，则使用当前数据库。
target_percent
数据库收缩后的数据库文件中所需的剩余可用空间百分比。
NOTRUNCATE
指定在数据库文件中保留所释放的文件空间。如果未指定，将所释放的文件空间释放给操作系统。
TRUNCATEONLY
将数据文件中任何未使用空间释放给操作系统，并将文件收缩到最后分配的区，从而无须移动任何数据即可减小文件大小。使用TRUNCATEONLY时，将忽略target_percent设置。
WITH NO_INFOMSGS
取消严重级别从0到10的所有信息性消息。
下面的语句使Sales数据库中文件有10%的可用空间。
DBCC SHRINKDATABASE ('Sales', 10)
2．使用ALTER DATABASE设置自动收缩数据库
将数据库的AUTO_SHRINK选项设置为ON后，数据库引擎将自动收缩有可用空间的数据库。下面是使用ALTER DATABASE语句将Sales数据库的AUTO_SHRINK选项设置为ON的方法。
ALTER DATABASE Sales
SET AUTO_SHRINK ON ;
3．收缩文件
下面是DBCC SHRINKFILE语句的语法格式：
DBCC SHRINKFILE
(
{ 'file_name' | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
'file_name'
要收缩的文件的逻辑名称。
file_id
要收缩的文件的标识（ID）号。可以使用FILE_ID函数获取文件的ID。
target_size
用兆字节表示的文件大小。如果未指定，则DBCC SHRINKFILE将文件大小减少到默认文件大小。
EMPTYFILE
将指定文件中的所有数据迁移到同一文件组中的其他文件。
NOTRUNCATE
将释放的文件空间保留在文件中。当与target_size一起指定NOTRUNCATE时，释放的空间不会释放给操作系统。唯一影响是将已使用的页从target_size行前面重新定位到文件的前面。
TRUNCATEONLY
将文件中的任何未使用空间释放给操作系统，并将文件收缩到最后一次分配的区，从而减小了文件大小，但是没有移动任何数据。不会尝试将行重新定位到未分配的页。使用TRUNCATEONLY时，将忽略target_size。
WITH NO_INFOMSGS
禁止显示所有信息性消息。
例如，下面的语句将Sales数据库中的SPri1_dat文件的大小收缩到8 MB。
USE Sales ;
GO
DBCC SHRINKFILE (SPri1_dat, 8) ;
以下示例演示了清空文件以便从数据库中将其删除的步骤。针对此示例，首先创建一个数据文件，并假设该文件包含数据。
USE AdventureWorks;
GO
-- 创建一个数据文件并假设其包含数据
ALTER DATABASE AdventureWorks
ADD FILE (
NAME = Test1data,
FILENAME = 'C:\t1data.ndf',
SIZE = 5MB
);
GO
-- 清空数据文件
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- 从数据库中移除数据文件
ALTER DATABASE AdventureWorks
REMOVE FILE Test1data;
GO
2.3.4 设置数据库选项
可以在新建数据库时或对现有数据库通过“数据库属性”窗口进行部分数据库选项设置，而使用ALTER DATABASE的SET子句则可以进行更加全面的选项设置。可用的设置选项如表2-1所示。
例如，下面的语句设置Sales数据库的ANSI_NULLS和ANSI_NULL_DEFAULT选项为ON。
ALTER DATABASE Sales
SET ANSI_NULLS ON,ANSI_NULL_DEFAULT ON ;
2.3.5 重命名数据库
在SQL Server中，可以更改数据库的名称。在重命名数据库之前，应该确保没有人使用该数据库，而且该数据库设置为单用户模式。
下面是使用ALTER DATABASE语句重命名数据库时的语法格式：
ALTER DATABASE database_name
MODIFY NAME = new_database_name ;
例如，下面语句将Sales数据库重命名为Sales1。
ALTER DATABASE Sales
SET SINGLE_USER; --设置为单用户
GO
ALTER DATABASE Sales
MODIFY NAME = Sales1; --重命名为Sales1
GO
ALTER DATABASE Sales1 --重新设置为多用户
SET MULTI_USER;| SUPPLEMENTAL_LOGGING | 指定为ON时，会将详细信息添加到第三方产品的日志中；指定为OFF时，则不将详细信息添加到日志中 默认值为OFF |
例如，下面的语句设置Sales数据库的ANSI_NULLS和ANSI_NULL_DEFAULT选项为ON。
ALTER DATABASE Sales SET ANSI_NULLS ON,ANSI_NULL_DEFAULT ON ;
2.3.5 重命名数据库
在SQL Server中，可以更改数据库的名称。在重命名数据库之前，应该确保没有人使用该数据库，而且该数据库设置为单用户模式。
下面是使用ALTER DATABASE语句重命名数据库时的语法格式：
ALTER DATABASE database_name MODIFY NAME = new_database_name ;
例如，下面语句将Sales数据库重命名为Sales1。
ALTER DATABASE Sales SET SINGLE_USER; --设置为单用户 GO ALTER DATABASE Sales MODIFY NAME = Sales1; --重命名为Sales1 GO ALTER DATABASE Sales1 --重新设置为多用户 SET MULTI_USER; ```
扫一扫
611
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
