第2章 数据库的建立与维护

1、第 2 章数 据 库的建立与维护 系统数据库系统数据库数据库的存储结构数据库的存储结构数据库的创建和管理数据库的创建和管理查看数据库状态查看数据库状态数据库操作数据库操作数据快照的创建数据快照的创建系统数据库系统数据库 所谓所谓系统数据库系统数据库指的是随安装程序一起安装，用于协助指的是随安装程序一起安装，用于协助SQL Server 2008共同完成管理操作的数据库，它们是共同完成管理操作的数据库，它们是SQL Server 2008运行的基础。运行的基础。 在在SQL Server 2008中，默认有中，默认有5个系统数据库：个系统数据库：master、model、msdb、tempdb和

2、和resource数据库。数据库。 Master数据库数据库是是SQL Server系统最重要的数据库，它记录了系统最重要的数据库，它记录了SQL Server系统的所有系统信息。系统的所有系统信息。 在在 SQL Server 2008 中，系统对象不再存储在中，系统对象不再存储在 master 数据库中，数据库中，而是存储在而是存储在 Resource 数据库数据库中。中。 model 数据库数据库用作在用作在 SQL Server 实例上创建的所有数据库的模板。实例上创建的所有数据库的模板。如果修改如果修改 model 数据库，之后创建的所有数据库都将继承这些修改。数据库，之后创建的所有

3、数据库都将继承这些修改。 Msdb数据库数据库是代理服务数据库。是代理服务数据库。 Tempdb是一个临时数据库是一个临时数据库，它为所有的临时表、临时存储过程及，它为所有的临时表、临时存储过程及其他临时操作提供存储空间。其他临时操作提供存储空间。 Tempdb数据库由整个系统的所有数据库使用。数据库由整个系统的所有数据库使用。SQL Server每次启每次启动时，动时，tempdb数据库被重新建立。当用户与数据库被重新建立。当用户与SQL Server断开连接时，断开连接时，其临时表和存储过程自动被删除。其临时表和存储过程自动被删除。mastermodeltempdbmsdbAdventur

4、eWorksAdventureWorksDW系统数据库系统数据库用户数据库用户数据库User1StuInfo 数据库是表、视图、存储过程、触发器等数据库对象的集合，是数据库是表、视图、存储过程、触发器等数据库对象的集合，是数据库管理系统的核心内容。为了更好地理解数据库，应该首先了解数据库管理系统的核心内容。为了更好地理解数据库，应该首先了解数据库的物理文件和逻辑文件、事务日志、文件组、数据库的物理空数据库的物理文件和逻辑文件、事务日志、文件组、数据库的物理空间、数据库状态、数据库快照等基本概念。间、数据库状态、数据库快照等基本概念。 数据库：数据库：用于存储特定格式的关系数据的总集。用于存储特

5、定格式的关系数据的总集。 文件：文件：用于存放由数据构成的表。用于存放由数据构成的表。 文件组文件组 ：用于将多个文件组织起来进行管理。用于将多个文件组织起来进行管理。 架构：架构：用于管理数据表等对象的命名。用于管理数据表等对象的命名。 快照：快照：用于建立数据库的快速还原点，方便测试和故障恢复用于建立数据库的快速还原点，方便测试和故障恢复。 数据库对象及构成数据库对象及构成：数据库的存储结构数据库的存储结构数据库的存储结构分为数据库的存储结构分为逻辑存储结构逻辑存储结构和和物理存储结构物理存储结构两种。两种。1、数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成，、数据库的逻辑存储结

6、构指的是数据库是由哪些性质的信息所组成，SQL Server的数据库的数据库不仅只是不仅只是数据的存储，所有与数据处理操作相关的数据的存储，所有与数据处理操作相关的信息都存储在数据库中。信息都存储在数据库中。 SQL Server的数据库是由诸如表、视图、索引等各种不同的数据库的数据库是由诸如表、视图、索引等各种不同的数据库对象所组成，它们分别用来存储特定信息并支持特定功能，构成数据库对象所组成，它们分别用来存储特定信息并支持特定功能，构成数据库的逻辑存储结构。的逻辑存储结构。2、数据库的物理存储结构则是讨论数据库文件是如何在磁盘上存储的。、数据库的物理存储结构则是讨论数据库文件是如何在磁盘上

7、存储的。数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成。组成。 在在 SQL 2008 中，一个数据库至少有一个数据文件和一个事务日志中，一个数据库至少有一个数据文件和一个事务日志文件。该数据库也可以有多个数据文件和多个事务日志文件。数据文件文件。该数据库也可以有多个数据文件和多个事务日志文件。数据文件用于存放数据库的数据和各种对象，事务日志文件用于存放事务日志。用于存放数据库的数据和各种对象，事务日志文件用于存放事务日志。 数据库的文件组成数据库的文件组成：（：（通常可以由三类文件组成） 主数据文件：默认的扩展

8、名为.mdf 辅助数据文件：默认的扩展名为.ndf 事务日志文件：默认的扩展名为.ldf在在SQL Server 2008中一个数据库至少包含一个有数据库对象的主数据文中一个数据库至少包含一个有数据库对象的主数据文件和一个事务日志文件。件和一个事务日志文件。这些物理文件具有可用于这些物理文件具有可用于Transact-SQL 语句的操作系统文件名称和逻辑语句的操作系统文件名称和逻辑文件名称文件名称所有的的数据文件和事务日志的所有的的数据文件和事务日志的默认位置默认位置是是 C: Progrom Files Mirosoft SQL Server MSSQL.n MSSQL Data最佳的实践：

9、最佳的实践： 将所有的数据和对象存储在辅助数据文件中，而主数据文件只负责将所有的数据和对象存储在辅助数据文件中，而主数据文件只负责存储数据目录，这种配置可有效的避免访问时的磁盘争用。存储数据目录，这种配置可有效的避免访问时的磁盘争用。 存储数据的方法存储数据的方法：日志文件日志文件: .ldf数据文件数据文件:.mdf or .ndf区区: 8 个连续的个连续的 8 KB 页页页页: 8 KB 配置事务日志配置事务日志： 建立好数据库之后，在进行具体的操作之前应当首先配置事务日志建立好数据库之后，在进行具体的操作之前应当首先配置事务日志。事务日志文件记录事务日志文件记录SQL Server 对

10、这个数据库的操作，以便于在系统出现对这个数据库的操作，以便于在系统出现故障时恢复数据库。故障时恢复数据库。 那事务日志又有什么样的作用？（看个场景）那事务日志又有什么样的作用？（看个场景）小张在书店买书。他和书店进行一次性交易付款过程包括下面这些数据小张在书店买书。他和书店进行一次性交易付款过程包括下面这些数据库操作库操作： 更新客户所购书籍的库存信息。 保存客户的付款信息和银行系统进行互动。 生成订单并保存到数据库中。 更新用户相关信息。 上面的场景中每个数据库操作都被称为上面的场景中每个数据库操作都被称为事务。事务。 所有的数据库操作都将保存到相关的数据库日志中，发生故障后，所有的数据库操

11、作都将保存到相关的数据库日志中，发生故障后，系统可以根据事务日志将数据库返回到用户下单前的状态。系统可以根据事务日志将数据库返回到用户下单前的状态。正常情况一切顺利进行，交易成功，与交易相关的所有数据也成功的更新。正常情况一切顺利进行，交易成功，与交易相关的所有数据也成功的更新。 快速了解创建学校的数据库：快速了解创建学校的数据库：管理任务：管理任务： 小王的学校通过数据库管理所有老师和学生的信息，这台服务器配有三个磁盘驱动器。在安装了2008之后，作为一个DBA，需要实现以下任务： 创建数据库； 创建数据文件； 规划文件大小和位置； 配置数据库选项。 管理操作过程：管理操作过程： 小王分别在

12、 SQL 2008 中建立了老师和学生的数据库的文件，分别用来存储老师和学生的信息。并且根据在校的老师和学生数量来估计文件的初始大小，老师的数据文件初始大小为20 MB，学生的数据文件初始大小为80 MB，设置日志文件的总大小为数据文件的25。 由于服务器有三个硬盘驱动器，操作系统位于C盘，小王将数据文件分别放置于D盘和E盘，将日志文件放在C盘，这样就可以实现最好的性能。 创建数据库的示例创建数据库的示例： 可以使用SQL Server Management Studio中的可视化工具来创建数据库，图示：创建数据库时，可指定以下选项：创建数据库时，可指定以下选项： 主文件主文件：默认文件名为：

13、database_name.mdf，位于SQL Server 实例的Data 文件夹中。 辅助文件辅助文件：创建可选的辅助文件，将其分配到主文件组和任何现存用户定义的主文件组中 事务日志事务日志：默认文件名为：database_name.ldf，位于SQL Server 实例的Data 文件夹中。大小大小：可指定每个数据文件和日志文件的大小。初始大小与model 数据库中使用的值相同。设置自动增长对话框设置自动增长对话框: 文件增长文件增长：可指定文件是否在必要时增长。该选项称为自动增：可指定文件是否在必要时增长。该选项称为自动增长，即为启用文件增长。长，即为启用文件增长。 最大值最大值：可指

14、定：可指定MB或百分比作为文件可增长的最大值，建议或百分比作为文件可增长的最大值，建议指定文件增长的最大值。指定文件增长的最大值。 排序规则排序规则：数据的排序次序原则，用以确定进行数据排序时数：数据的排序次序原则，用以确定进行数据排序时数据值的具体位置。据值的具体位置。 文件组文件组： 如果在数据库中存在大量的文件，如何才能方便管理，同时提高数如果在数据库中存在大量的文件，如何才能方便管理，同时提高数据库的性能呢？据库的性能呢？探讨：探讨： SQL Server 2008在数据文件中存储数据，数据文件可分组为一个或多在数据文件中存储数据，数据文件可分组为一个或多个文件组。虽然数据库只用一个文

15、件组即可成功运行，但创建多个文件个文件组。虽然数据库只用一个文件组即可成功运行，但创建多个文件组可能更有利。组可能更有利。结论：结论： 我们要学会文件组的创建、设计和维护，以及利用文件组改善数我们要学会文件组的创建、设计和维护，以及利用文件组改善数据库的性能。据库的性能。文件组的概念文件组的概念： 文件组是一种逻辑结构，它允许文件组是一种逻辑结构，它允许DBA组织数据文件，并把它们作组织数据文件，并把它们作为一个逻辑单元进行管理。为一个逻辑单元进行管理。 文件组可以控制数据库中各个对象的物理布局，这可提供大量可文件组可以控制数据库中各个对象的物理布局，这可提供大量可管理性和性能方面的好处。管理

16、性和性能方面的好处。 SQL Server 支持支持两种两种文件组：主要文件组和用户定义的文件组。文件组：主要文件组和用户定义的文件组。主要文件组主要文件组包含主要数据文件以及任何没有被存储到另一文件组中的包含主要数据文件以及任何没有被存储到另一文件组中的次要数据文件。次要数据文件。 创建用户定义的文件组的目的是对次要文件进行分组，并将数据创建用户定义的文件组的目的是对次要文件进行分组，并将数据库对象分配给文件组。库对象分配给文件组。问：每个文件组可以有多少个日志文件？问：每个文件组可以有多少个日志文件？文件组文件组 创建数据库的方法有以下三种：创建数据库的方法有以下三种： 1、使用、使用SQ

17、L Server管理平台创建数据库管理平台创建数据库 2、使用模板创建数据库、使用模板创建数据库 使用模板创建数据库使用模板创建数据库 ，用户根据提示操作，即可创建数据库。，用户根据提示操作，即可创建数据库。 使用使用 CREATE DATABASE 语句创建数据库语句创建数据库：数据库的创建和管理数据库的创建和管理Transact-SQL语言使用语言使用CREATE DATABASE命令来创建数据库。该命命令来创建数据库。该命令的语法如下令的语法如下： CREATE DATABASE database_name ON PRIMARY ，n ， ，n LOG ON ，n FOR RESTORE

18、 :=（NAME=logical_file_name， FILENAME=os_file_name ，SIZE=size ，MAXSIZE=max_size|UNLIMITED ，FILEGROWTH=growth_increment ） ，n :=FILEGROUP filegroup_name ，n各参数说明如下：各参数说明如下： database_name：数据库的名称，最长为128个字符。 PRIMARY：该选项是一个关键字，指定主文件组中的文件。 LOG ON：指明事务日志文件的明确定义。 NAME=logical_file_name：指定数据库的逻辑名称，这是在指定数据库的逻辑名称

19、，这是在SQL Server系系统中使用的名称，是数据库在统中使用的名称，是数据库在SQL Server中的标识符。中的标识符。 FILENAME =os_file_name ：指定数据库所在文件的操作系统文件名称和路：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和径，该操作系统文件名和NAME的逻辑名称一一对应。的逻辑名称一一对应。 SIZE：指定数据库的初始容量大小。主文件大小至少与指定数据库的初始容量大小。主文件大小至少与model数据库中的主数据库中的主文件相同。文件相同。 ON：允许显式定义用于存储数据的文件允许显式定义用于存储数据的文件。 UNLIMITED：指定了

20、文件将一直增长到磁盘变满为止。指定了文件将一直增长到磁盘变满为止。 FILEGROUP filegroup_name ：这是文件组在数据库中的唯一逻辑名称。这是文件组在数据库中的唯一逻辑名称。 DEFULT：参数制定了该文件组为默认文件组。参数制定了该文件组为默认文件组。 FILEGROWTH=growth_increment：指定文件每次增加容量的大小，该值以指定文件每次增加容量的大小，该值以当前文件大小的百分比或当前文件大小的百分比或KB、MB、GB和和TB来表示。来表示。当指定数据为当指定数据为0时，表示文时，表示文件不增长。件不增长。 MAXSIZE=max_size：指定操作系统文件

21、可以增长到的最大尺寸。以指定操作系统文件可以增长到的最大尺寸。以KB、MB、GB和和TB为单元，默认为为单元，默认为MB。提示：提示： 区分NAME与FILENAME。NAME是数据库内部处理文件的逻辑名称，FILENAME 是数据库在磁盘上存放文件的文件名称，两者可以不同。场景：场景： 你正在设计开发一个学校的图书馆系统。这个图书馆系统带有一个藏你正在设计开发一个学校的图书馆系统。这个图书馆系统带有一个藏书记录的数据库。数据库系统采用书记录的数据库。数据库系统采用SQL Server 2008 ，数据库名为，数据库名为TestDB。数据库的初始大小为。数据库的初始大小为20 MB，日志文件的

22、大小为，日志文件的大小为5 MB。 由于不能确定学校将会把数据库安装在哪台由于不能确定学校将会把数据库安装在哪台SQL Server 服务器上，服务器上，你希望在安装这个系统的时候，能在服务器上按照要求自动创建数据库。你希望在安装这个系统的时候，能在服务器上按照要求自动创建数据库。CREATE DATABASE TestDBON -允许显式定义用于存储数据的文件 - 定义数据库主文件( NAME = TestDB_Data, - 主文件逻辑名称 FILENAME = D:DATATransactTestDB.mdf, - 操作系统文件名称，包含路径 SIZE = 20 MB, - 主文件初始大

23、小 FILEGROWTH = 0 ) - 未启用自动增长- 定义数据库日志文件LOG ON ( NAME = TestDB_Log, - 日志文件逻辑名称 FILENAME = D:DATATestDB_Log.ldf, - 包含路径的操作系统文件名称 SIZE = 5 MB, - 日志文件初始大小 FILEGROWTH = 0 ) -未启用自动增长举例举例：创建了一个 Test 数据库，该数据库的主数据文件逻辑名称为Test_data ，物理文件名为 Test.mdf，初始大小为 10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为 Test_log，物理文件名为 Tes

24、t.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。程序清单如下：程序清单如下：CREATE DATABASE test ON PRIMARY ( NAME = test, FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLdatatest.mdf,SIZE=10MB, MAXSIZE = UNLIMITED,FILEGROWTH = 10%) LOG ON ( NAME=test_log,FILENAME=C: Program Files Microsoft SQL Server MSSQL data test_log.ldf ,

25、SIZE=1MB,MAXSIZE = 5MB,FILEGROWTH = 1MB )GO 修改数据库修改数据库： 场景：场景： 在数据库设计过程中，有时候需要对数据库进行修改。可以使用SSMS 对数据库进行修改，还可以使用 Transact-SQL 脚本语句来完成数据库修改工作。 利用利用SQL Server管理平台修改数据库：管理平台修改数据库： 在 SQL Server 管理平台中，右击所要修改的数据库，从弹出的快捷菜单中选择【属性】选项，出现如下图所示的数据库属性设置对话框。 可以看到，修改或查看数据库属性时，属性页框比创建数据库时多了两个，即选项和权限页框。利用利用SQL Server管

26、理平台修改数据库（选项、权限）管理平台修改数据库（选项、权限） 使用使用ALTER DATABASE语句修改数据库语句修改数据库：ALTER DATABASE database ADD FILE ,.,n TO FILEGROUP filegroup_name | ADD LOG FILE ,.,n | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name| REMOVE FILEGROUP filegroup_name| MODIFY FILE 各参数说明如下：各参数说明如下： database：要更改数据库名称。 ADD

27、FILE：指定要添加文件。 TO FILEGROUP ：指定要将指定文件添加到的文件组。 filegroup_name：要添加指定文件的文件组的名称。 ADD LOG FILE ：指定要将日志文件添加到指定的数据库。 ADD FILEGROUP ：指定要添加的文件组。 REMOVE FILE ：从数据库系统表中删除文件描述并删除物理文件。（只有在文件为空时才能删除） REMOVE FILEGROUP ：从数据库中删除文件组并删除该文件组中所有文件。 （只有在文件为空时才能删除） MODIFY FILE ：指定要更改给定的文件，更改选项包括 NAME、SIZE 、FILEGROUPTH和MAXS

28、IZE。注意：注意： 若要更改数据文件或日志文件的逻辑名称，应在 NAME 选项中指定要改名的逻辑文件名称，并在 NEWNAME 选项中指定文件的新逻辑名称。举例：举例：将两个数据文件和一个事务日志文件添加到将两个数据文件和一个事务日志文件添加到 test 数据库中。数据库中。程序清单如下：程序清单如下：ALTER DATABASE Test ADD FILE (NAME = Test1, FILENAME=c:Program FilesMicrosoft SQL Server MSSQL Datatest1.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROW

29、TH = 5MB)，(NAME = Test2, FILENAME=c:Program FilesMicrosoft SQL Server MSSQL Datatest2.ndf,SIZE = 3MB,MAXSIZE = 10MB, FILEGROWTH = 1MB)GO ALTER DATABASE Test ADD LOG FILE ( NAME = testlog1， FILENAME=c:Program FilesMicrosoft SQL Server MSSQL Data testlog1.ldf， SIZE = 5MB， MAXSIZE = 100MB，FILEGROWTH =

30、5MB)GO 删除删除数据库数据库： 利用SQL Server管理平台删除数据库和 Drop语句删除数据库 在SQL Server管理平台中，右击所要删除的数据库，从弹出的快捷菜单中选择【删除】选项即可删除数据库。系统会弹出确认是否要删除数据库对话框，单击“确定”按钮则删除该数据库。 利用 Drop语句可以从SQL Server中一次删除一个或多个数据库。其语法如下： Drop database database_name,n举例：举例：删除创建的数据库删除创建的数据库Test。程序清单如下：程序清单如下： drop database Test 收缩数据库收缩数据库：-减小UserDB用户数据

31、库中数据文件和日志文件的大小，以便在数据库中留出10%的可用空间 n自动收缩数据库自动收缩数据库n手动收缩数据库手动收缩数据库n使用使用DBCC SHRINKDATABASE命令收缩数据库命令收缩数据库DBCC SHRINKDATABASE (UserDB, 10);GOUSE UserDB;GODBCC SHRINKFILE (DataFile1, 7);GO-将UserDB用户数据库中名为DataFile1的数据文件的大小收缩到7 MB 查看数据库状态查看数据库状态状状 态态描描 述述ONLINE在线状态或联机状态，可以执行对数据库的访问在线状态或联机状态，可以执行对数据库的访问OFFLI

32、NE离线状态或脱机状态，数据库不能正常使用。用户可以人工设置，可以执行对处于这种状态离线状态或脱机状态，数据库不能正常使用。用户可以人工设置，可以执行对处于这种状态的数据库文件的移动等处理的数据库文件的移动等处理RESTORING还原状态，正在还原主文件组的一个或多个文件。这时，数据库不能使用还原状态，正在还原主文件组的一个或多个文件。这时，数据库不能使用RECOVERING恢复状态，正在恢复数据库。这是一个临时性状态，如果恢复成功，那么数据库自动处于在恢复状态，正在恢复数据库。这是一个临时性状态，如果恢复成功，那么数据库自动处于在线状态；如果恢复失败，那么数据库处于不能正常使用的可疑状态线状

33、态；如果恢复失败，那么数据库处于不能正常使用的可疑状态RECOVERY PENDING恢复未完成状态。恢复过程中缺少资源造成的问题状态。这时数据库不可使用，必须执行其恢复未完成状态。恢复过程中缺少资源造成的问题状态。这时数据库不可使用，必须执行其他操作来解决这种问题他操作来解决这种问题SUSPECT可疑状态，主文件组可疑或可能被破坏。这时数据库不能使用，必须执行其他操作来解决这可疑状态，主文件组可疑或可能被破坏。这时数据库不能使用，必须执行其他操作来解决这种问题种问题EMERGENCY紧急状态，可以人工通过更改数据库设置为该状态。这时数据库处于单用户模式和只读状态，紧急状态，可以人工通过更改数

34、据库设置为该状态。这时数据库处于单用户模式和只读状态，只能由只能由sysadmin固定服务器角色成员访问。主要用于对数据库的故障排除固定服务器角色成员访问。主要用于对数据库的故障排除查看数据库信息查看数据库信息： 在在 SQL Server 2008系统中，可以使用一些目录视图、函数、存储系统中，可以使用一些目录视图、函数、存储过程查看有关数据库的基本信息。过程查看有关数据库的基本信息。 sys.databases 数据库和文件目录视图可以查看有关数据库的基本数据库和文件目录视图可以查看有关数据库的基本信息，信息，sys.database_files可以查看有关数据库文件的信息，可以查看有关数

35、据库文件的信息，sys.filegroups 可以查看有关数据库文件组的信息，可以查看有关数据库文件组的信息，sys.master_files 可以查看数据库文件的基本信息和状态信息。可以查看数据库文件的基本信息和状态信息。查看数据库查看数据库状态：状态： use 教务管理系统教务管理系统 go select DATABASEPROPERTYEX(教务管理系统教务管理系统,Recovery) 就会显示出此数据库当前的恢复模式。就会显示出此数据库当前的恢复模式。数据库操作数据库操作分离数据库分离数据库： 分离数据库是指将数据库从分离数据库是指将数据库从SQL Server实例中删除，但是该数据库

36、的数据文实例中删除，但是该数据库的数据文件和事务日志文件依然保持不变。这样可以将该数据库附加到任何的件和事务日志文件依然保持不变。这样可以将该数据库附加到任何的SQL Server实例中。实例中。 使用使用sp_detach_db存储过程来执行数据库分离操作，存储过程来执行数据库分离操作，还还可使用可使用SQL Server Management Studio工具来执行分离操作。工具来执行分离操作。附加附加数据库数据库： 将分离后的数据库附加到某个将分离后的数据库附加到某个Microsoft SQL Server实例中时，可以使用实例中时，可以使用CREATE DATABASE语句语句；还还可使用可使用SSMS工具来执行分离操作。工具来执行分离操作。 附加数据库时所有的数据文件必须都是可用的。在附加数据库过程中，如附加数据库时所有的数据文件必须都是可用的。在附加数据库过程中，如果没有日志文件，系统将自动创建一个新的日志文件。果没有日志文件，系统将自动创建一个新的日志文件。复制和移动数据库复制和移动数据库： 使用使用 SQL Server 2008 的【复制数据库向导】工具可以复制或移动数据的【复制数据库向导】工具可以复制或移动数据库，创建数据库的副本，在库，创建数据库的副本，在SQL 不同实例间复制和移动数据库，以及将不同实例间复制和移动数据库，以及将SQL 20