1-2 通过SQL管理数据库文件
u 掌握数据库文件的类型，学习在管理平台下建立数据库文件和文件组
u 掌握数据库文件及日志文件的建立操纵语句
u 掌握对数据库文件的修改技术及操纵语句
u 掌握数据库的收缩技术及操纵语句
1-2-1 数据库文件及文件组
每个 SQL Server 2005 数据库至少具有两个操作系统文件：一个数据文件和一个日志文件。数据文件包含数据和对象，例如表、索引、存储过程和视图。日志文件包含恢复数据库中的所有事务所需的信息。为了便于分配和管理，可以将数据文件集合起来，放到文件组中。
1. 数据库文件
SQL Server 2005 数据库具有三种类型的文件，如表1-2所示：
表1-2 SQL Server 2005 数据库的三种类型的文件
文件
说明
主数据文件
主要数据文件包含数据库的启动信息，并指向数据库中的其他文件。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。每个数据库有一个主要数据文件。主要数据文件的建议文件扩展名是 .mdf。
从数据文件
从要数据文件是可选的，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。另外，如果数据库超过了单个 Windows 文件的最大大小，可以使用次要数据文件，这样数据库就能继续增长。次要数据文件的建议文件扩展名是 .ndf。
事务日志文件
事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。事务日志的建议文件扩展名是 .ldf。
2． 在图形化管理平台下建立数据库文件
该方法是最简便快捷的一种方式，下面我们通过一个小实验说明如何在图形化管理平台下建立数据库文件。
实验：新建数据库文件和日志文件
第一步：启动SQL Server2005的Management Studio，进入管理平台界面。在对象资源管理器中右击数据库文件夹，选择新建数据库。
图1-6 新建数据库
第二步：在弹出的新建数据库的对话框中，首先命名一个数据库名称，该名称被称为是逻辑文件名的命名；其次会在数据库文件工作区域出现两个默认文件，对这两个文件的命名称为是物理文件名的命名，后缀名分别是mdf文件（主数据库文件）和ldf文件（日志文件）。这两个文件分别可以通过设置进行数据库文件配置，包括：文件的初始化大小，自动增长率配置，文件存储路径。当然，用户还可以建立新的数据库文件（从数据库文件），后缀名为ndf文件，以及日志文件，后缀名是ldf文件。
图1-7 在弹出的新建数据库的对话框中对数据库文件进行命名
3. 文件组
每个数据库有一个主要文件组。此文件组包含主要数据文件和未放入其他文件组的所有次要文件。可以创建用户定义的文件组，用于将数据文件集合起来，以便于管理、数据分配和放置。
例如，可以分别在三个磁盘驱动器上创建三个文件 Data1.ndf、Data2.ndf 和 Data3.ndf，然后将它们分配给文件组 fgroup1。然后，可以明确地在文件组fgroup1上创建一个表。对表中数据的查询将分散到三个磁盘上，从而提高了性能。通过使用在RAID（独立磁盘冗余阵列）条带集上创建的单个文件也能获得同样的性能提高。表1-3列出了主文件组和用户定义文件组以及默认文件组的差异。
表1-3 主文件组和用户定义文件组的差异
文件组
说明
主要文件组
包含主要文件的文件组。所有系统表都被分配到主要文件组中。
用户定义文件组
该文件组是用 CREATE DATABASE 或 ALTER DATABASE 语句中的 FILEGROUP 关键字，或在 SQL Server 企业管理器内的"属性"对话框上指定的任何文件组。
默认文件组
在每个数据库中，每次只能有一个文件组是默认文件组。如果没有指定默认文件组，则默认文件组是主文件组。
如果在数据库中创建对象时没有指定对象所属的文件组，对象将被分配给默认文件组。不管何时，只能将一个文件组指定为默认文件组。默认文件组中的文件必须足够大，能够容纳未分配给其他文件组的所有新对象。如图1-8所示，如果PRIMARY文件组是默认文件组，则需要在默认值上打勾，则任何新存储的文件信息只能够被保存在PRIMARY文件组中，而不能够被保存在test_1文件组，除非用脚本指定存放在test_1文件组。
图1-8 创建默认文件组和从文件组
1-2-2 数据库的建立与撤消
数据库的建立是指在数据库服务器上面建立数据库的物理文件，建立一个新数据库的基本语法是：create database 数据库名。
撤消数据库是对数据库物理文件删除过程，因此首先必须保证的是当前数据库并未在使用中，而是出于脱机工作的状态。撤销数据库的基本语法是：drop database 数据库名。
下面我们通过一个小实验来说明，如何建立一个数据库和怎样删除一个数据库。
实验：创建MyDB数据库
（1）按照图1-9所规定的要求，建立一个主数据文件（大小4M，命名为MyDB_Prm.mdf文件）和一个日志文件（大小1M，命名为MyDB.ldf文件），存放的路径都在C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data目录下面；再建立两个从数据文件（大小1M，命名为MyDB_FG1_1.ndf和MyDB_FG1_2.ndf文件），路径相同。
图1-9 创建MyDB数据库基本要求
（2）启动SQL Server2005的Management Studio，进入管理平台界面，在新建的查询界面中键入下面的代码：
USE master;
--调用主数据库master，任何用户自定义数据库文件的创建都是在master数据库下完成的。
GO
--结束批处理
--在默认文件组中建立日志文件和数据库文件，特别注意的是针对于主数据文件的增长率和最大字节数的规定
CREATE DATABASE MyDB
ON
--ON语句的出现表示下面的代码是建立数据文件，并且下面存储的文件将存储在主文件组之中。
PRIMARY (--如果出现PRIMARY则表示下面的数据文件为主数据文件
NAME='MyDB_Primary', -- NAME关键词表示逻辑文件名的命名，注意结束时以逗号结尾
FILENAME='c:\Program Files\
Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf',
-- FILENAME关键词表示物理文件名的命名，后面的字符串必须在单引号范围内书写，并且是存在的物理路径，建议打开windows的资源管理器，直接寻找到物理文件夹后将路径地址复制下来比较保险。
SIZE=4MB, -- 默认的初始值大小为4兆。
MAXSIZE=10MB, -- 文件最大增长到10兆即停止存储
FILEGROWTH=1MB -- 文件增长率是按照兆字节进行增招，每次增长1兆。
--注意这是建立数据库文件MyDB_Primary的最后一句话，因此结尾处没有逗号。), --注意此处括号后仍然有逗号，表示将继续建立数据文件的意思。
FILEGROUP MyDB_FG1
--如果出现有FILEGROUP则表示将下列创建的数据文件保存到文件组MyDB_FG1中，
--而不是默认的主文件组之中，如果不出现FILEGROUP关键词，则直接存储在默认的主文件组之中。
( NAME = 'MyDB_FG1_Dat1',
FILENAME = 'c:\Program Files\
Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
--注意此处用逗号区分下一个数据文件的建立从此开始也被存储在文件组MyDB_FG1中
( NAME = 'MyDB_FG1_Dat2',
FILENAME = 'c:\Program Files\
Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
LOG ON
-- LOG ON语句的出现表示下面的代码是建立日志文件，日志文件没有主从之分。
( NAME='MyDB_log',
FILENAME = 'c:\Program Files\
Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);
GO
数据库的建立是指在数据库服务器上面建立数据库的物理文件，建立一个新数据库的基本语法是：create database 数据库名。
撤消数据库是对数据库物理文件删除过程，因此首先必须保证的是当前数据库并未在使用中，而是出于脱机工作的状态。撤销数据库的基本语法是：drop database 数据库名。
下面我们通过一个小实验来说明，如何建立一个数据库和怎样删除一个数据库。
实验：创建MyDB数据库
（1）按照图1-9所规定的要求，建立一个主数据文件（大小
图1-9 创建MyDB数据库基本要求
（2）启动SQL Server2005的Management Studio，进入管理平台界面，在新建的查询界面中键入下面的代码：
USE master;
--调用主数据库master，任何用户自定义数据库文件的创建都是在master数据库下完成的。
GO
--结束批处理
--在默认文件组中建立日志文件和数据库文件，特别注意的是针对于主数据文件的增长率和最大字节数的规定
CREATE DATABASE MyDB
ON
--ON语句的出现表示下面的代码是建立数据文件，并且下面存储的文件将存储在主文件组之中。
PRIMARY (--如果出现PRIMARY则表示下面的数据文件为主数据文件
NAME='MyDB_Primary', -- NAME关键词表示逻辑文件名的命名，注意结束时以逗号结尾
FILENAME='c:\Program Files\
Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf',
-- FILENAME关键词表示物理文件名的命名，后面的字符串必须在单引号范围内书写，并且是存在的物理路径，建议打开windows的资源管理器，直接寻找到物理文件夹后将路径地址复制下来比较保险。
SIZE=4MB, -- 默认的初始值大小为4兆。
MAXSIZE=10MB, -- 文件最大增长到10兆即停止存储
FILEGROWTH=1MB -- 文件增长率是按照兆字节进行增招，每次增长1兆。
--注意这是建立数据库文件MyDB_Primary的最后一句话，因此结尾处没有逗号。), --注意此处括号后仍然有逗号，表示将继续建立数据文件的意思。
FILEGROUP MyDB_FG1
--如果出现有FILEGROUP则表示将下列创建的数据文件保存到文件组MyDB_FG1中，
--而不是默认的主文件组之中，如果不出现FILEGROUP关键词，则直接存储在默认的主文件组之中。
( NAME = 'MyDB_FG1_Dat1',
FILENAME = 'c:\Program Files\
Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
--注意此处用逗号区分下一个数据文件的建立从此开始也被存储在文件组MyDB_FG1中
( NAME = 'MyDB_FG1_Dat2',
FILENAME = 'c:\Program Files\
Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
LOG ON
-- LOG ON语句的出现表示下面的代码是建立日志文件，日志文件没有主从之分。
( NAME='MyDB_log',
FILENAME = 'c:\Program Files\
Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);
GO
数据库的修改是指针对于已经建立的数据库文件或者日志文件进行后期的修改过程，修改数据库的基本语法是：
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
/指定将要删除的文件组信息，注意：必须是在文件组为空的时候才可以/
实验：向数据库sales中添加一个含有两个数据库文件和两个
第一步：启动SQL Server2005的Management Studio，进入管理平台界面后，点击工具栏中的第一项：新建查询。
第二步：在打开的新建查询界面中，请键入下面代码并执行
Use master
Go
Alter database sales add filegroup salesgroup
--修改数据库sales，增加文件组salesgroup
Go
--下面是修改数据库sales，增加一个数据库文件sales1_data
Alter database sales
add file
(name=sales1_data,
--逻辑文件名是sales1_data
filename='E:\Program Files\Microsoft SQL Server\MSSQL\Data\sales1data.ndf',
--物理文件名是sales1data.ndf，此处必须用单引号将具体的物理路径和文件括住，同时需注意文件名的后缀是ndf文件。建议直接复制资源管理器的路径，避免出错。
size=5,
--初始化大小为MB，此处在数字后面是否加MB都可以
maxsize=50, --文件最大是MB
filegrowth=5% --文件增长率是百分之五),
--此处仅仅建立了一个数据文件，下面增加另一个数据文件sales3_data，其他内容与上面相同
(name=sales3_data,
filename='E:\Program Files\Microsoft SQL Server\MSSQL\Data\sales3_data.ndf',
size=5,
maxsize=50,
filegrowth=5)
To filegroup salesgroup
--将上面两个数据文件保存到新建的文件组salesgroup中，结束数据文件的添加工作，如果不写该句话，则直接保存至默认的文件组中。
--下面，再次开始修改数据库sales，但此次仅仅修改该数据库的日志文件。
Alter database sales
Add log file --添加日志文件
(name=saleslog2,
filename='E:\Program Files\Microsoft SQL Server\MSSQL\Data\saleslog2.ldf',
--日志文件的后缀名称是ldf
size=5,
maxsize=50,
filegrowth=5),
--添加日志文件saleslog2.ldf完毕，具体含义与数据文件一致，下面再添加另一个日志文件saleslog3.ldf
(name=saleslog3,
filename='E:\Program Files\Microsoft SQL Server\MSSQL\Data\saleslog3.ldf',
size=5,
maxsize=50,
filegrowth=5)
--结束日志文件的添加工作，日志文件是没有工作组的概念的
小问题：
在新建数据文件和日志文件时候，按兆字节增长和百分比增长应当在什么情况下使用？各自有什么好处？
数据库服务器的数据存储首先被存储到内部寄存器当中，当达到一定的字节量时候进行一次性的反写磁盘，进行磁盘数据存储。因此，如果确定当前的应用系统是小型应用系统，则每次存储的数据量不大，可以考虑使用按照百分比增长；但是如果是大型应用系统，每次存储的数据量较大，则建议使用兆字节增长。总之，为了避免存储在内部寄存器中数据的意外丢失，以最小数据量写磁盘为最佳。故而实际我们在创建数据文件和日志文件时候多采用系统默认的形式，即不随便制定文件的增长率较好。
数据库在使用一段时间后，会产生一些磁盘碎片的空间，这是由于频繁的删除和插入数据造成的。数据库的收缩就是将这些产生的碎片空间进行重新整合，从而优化数据磁盘组合，进而提高查询效率的过程。在压缩数据库时，将创建一个新的数据库文件，重新组织表页以使其驻留在相邻的数据库页中，并通过将所有数据库数据都重写到新的数据页中来回收未使用的空间。
第一步：鼠标右键单击school，在展开的菜单中，选择收缩数据库。如图1-10所示。
图1-10 选择收缩数据库
第二步：配置当前的分配空间和可用空间。如图1-11所示。
图1-11 选择收缩数据库
第三步：我们也可以收缩单个的数据库文件，见图1-12所示。通过配置界面，我们可以选择收缩的文件是数据库文件还是日志文件，以及具体的收缩操作。
图1-12 选择收缩数据库的具体文件
实验2：通过命令行收缩数据库实验
第一步：收缩数据库
若要收缩特定数据库的所有数据和日志文件，我们通过关键词DBCC SHRINKDATABASE来完成并实现对数据库的收缩工作，其基本的命令如下所示：
DBCC SHRINKDATABASE ( database_name [ , target_percent ] )
相关参数的意义是，database_name表示是要收缩的数据库名称；target_percent是指数据库收缩后的数据库文件中所要的剩余可用空间百分比。
问题：
假设有数据库文件字节为
|
假设可用的空间是x，那么基本的代数表达式为： --例如：请使得数据库中的文件减小，以使 school 中的文件有 10% 的可用空间。 |
DBCC SHRINKDATABASE (school, 10)
第二步：收缩数据库文件或日志文件
收缩数据库文件或日志文件基本的命令如下所示：
DBCC SHRINKFILE ( { file_name | file_id } { [ , target_size ]} )
相关参数的意义是：database_name是已收缩文件的逻辑名称，target_size是用兆字节表示的所要的文件大小（用整数表示）。
在进行数据库文件收缩时候，需要注意的事项包括两点：
q 如果已用空间超过目标文件的时候，就仅仅是放掉原来未使用的磁盘空间。
q 文件改大可以，改小不可以。
--例如：收缩数据库school_data，以使 school 中的数据库文件为
DBCC SHRINKFILE (school_data, 128)
