第3章 数据库管理、配置和维护

1、制作：荆州职业技术学院 彭岚第三章第三章 数据库管理、数据库管理、配置和维护配置和维护制作：荆州职业技术学院 彭岚1. 学习目标掌握后台数据库的创建方法； 熟练掌握数据库管理、配置及维护的各种操作； 2. 学习要点数据库的创建；数据库更名、属性修改、删除；数据库备份、还原、附加及分离；制作：荆州职业技术学院 彭岚1 1、系统数据库、系统数据库 （1 1） MasterMaster数据库：数据库：记录了一个记录了一个SQL SQL SERVERSERVER系统的所有系统信息，主要有：所有的系统的所有系统信息，主要有：所有的登录信息、系统设置信息、登录信息、系统设置信息、SQL serverSQL

2、 server初始化初始化信息、系统中其他系统数据库和用户数据库的信息、系统中其他系统数据库和用户数据库的相关信息等。相关信息等。 （2 2） ModelModel数据库：数据库：创建所有用户数据库创建所有用户数据库和和TempdbTempdb数据库的模板文件。创建用户数据库数据库的模板文件。创建用户数据库时，系统会将时，系统会将ModelModel数据库中的内容复制到新数据库中的内容复制到新建的数据库中，可对建的数据库中，可对ModelModel数据库进行修改，数据库进行修改，以简化数据库的创建和设置工作。以简化数据库的创建和设置工作。制作：荆州职业技术学院 彭岚 （3 3） MsdbMsd

3、b数据库数据库：用来存储计划信息以及：用来存储计划信息以及与备份和恢复相关的信息，代理服务器利用它与备份和恢复相关的信息，代理服务器利用它来安排工作和警报、记录操作等工作来安排工作和警报、记录操作等工作 （4 4） TempdbTempdb数据库数据库：用作系统的临时存储：用作系统的临时存储空间，主要有：存储临时表和临时存储过程、空间，主要有：存储临时表和临时存储过程、存储全局变量值、存储用户利用游标说明所筛存储全局变量值、存储用户利用游标说明所筛选出来的数据。选出来的数据。制作：荆州职业技术学院 彭岚数据库简介数据库简介1 1数据库文件的组成数据库文件的组成 SQL中数据保存在独立的数据库文

4、件中，数据库文件是存放数据库数据和数据库对象的文件。一个数据库通常有两个文件： （1）数据文件：用于存放数据；数据文件：用于存放数据； （2）事务日志文件：用于存放数据库的操作记录）事务日志文件：用于存放数据库的操作记录， 数据库中所有的数据和对象，如表、存储过程、触发器和视图，都只保存在以下的操作系统文件中。制作：荆州职业技术学院 彭岚SQL中的文件1 1、主数据文件、主数据文件：包含数据库的启动信息，以包含数据库的启动信息，以及存储数据库。及存储数据库。 每个数据库只有一个主数据文件，文件每个数据库只有一个主数据文件，文件后缀名为：后缀名为：.MDF.MDF。 主数据文件是所有数据文件的起

5、点，包主数据文件是所有数据文件的起点，包含指向其他数据库文件的指针。含指向其他数据库文件的指针。制作：荆州职业技术学院 彭岚2、次数据文件、次数据文件：用来保存所有主数据文件中：用来保存所有主数据文件中容纳不下的数据。容纳不下的数据。 当一个数据库数据量大到主数据文件（在当一个数据库数据量大到主数据文件（在一个磁盘上）容纳不下，需要多个次数据文一个磁盘上）容纳不下，需要多个次数据文件（可以在多个磁盘上）时，就可以采用次件（可以在多个磁盘上）时，就可以采用次数据文件；数据文件； 一个数据库可以有多个次数据文件，后一个数据库可以有多个次数据文件，后缀为缀为.NDF 制作：荆州职业技术学院 彭岚3、

6、事务日志文件：是用来记录数据库更新情、事务日志文件：是用来记录数据库更新情况的文件。况的文件。 它保存了恢复数据库的所有日志信息，扩展名为.ldf。例如：update、insert、delete等更改操作会记录在此文件中，而select的操作不会更改数据库，则不会记录在案。 一个数据库可以有多个事务日志文件。 制作：荆州职业技术学院 彭岚说明：说明： （1） 一个数据库可以有一个或多个数据库文件，一个数据库可以有一个或多个数据库文件，但一个数据库文件只属于一个数据库但一个数据库文件只属于一个数据库。例：。例：jwgljwgl库可以包含库可以包含jwgl_data.mdfjwgl_data.md

7、f和和jwgl_log.ldfjwgl_log.ldf两个两个数据文件，但数据文件，但jwgl_data.mdfjwgl_data.mdf只能属于只能属于jwgljwgl这一这一个数据库所有。个数据库所有。 （2） 数据库文件可以不断扩充而不受操作系统数据库文件可以不断扩充而不受操作系统文件大小的限制文件大小的限制。 （3）可以将数据库文件存储在不同的硬盘中，）可以将数据库文件存储在不同的硬盘中，这样可以同时对几个硬盘做数据存取，提高了数这样可以同时对几个硬盘做数据存取，提高了数据处理的效率据处理的效率 制作：荆州职业技术学院 彭岚4 4、事务日志事务日志创建事务日志：创建事务日志： 即为事务

8、日志指定其在数据库中的存储文件。即为事务日志指定其在数据库中的存储文件。数据文件和日志文件是分开存放的，分开存放的优点：数据文件和日志文件是分开存放的，分开存放的优点： （1） 事务日志可以单独地备份；事务日志可以单独地备份； （2） 有可能从服务器失效的事件中将服务器恢复到有可能从服务器失效的事件中将服务器恢复到最近的状态；最近的状态； （3） 事务日志不会抢占数据库的空间；事务日志不会抢占数据库的空间； （4） 可以很容易地监测事务日志的空间；可以很容易地监测事务日志的空间； （5）在向数据文件和事务日志文件写入数据时会产生在向数据文件和事务日志文件写入数据时会产生较少的冲突，有利于提高较

9、少的冲突，有利于提高SQL性能性能 制作：荆州职业技术学院 彭岚数据库的创建数据库的创建创建数据库的两种方法： （1） 使用对象资源管理器创建；使用对象资源管理器创建； （2 2）使用）使用T-SQL语句创建；语句创建； 制作：荆州职业技术学院 彭岚注意事项：注意事项： （1）T-SQL-SQL语言创建时要指定逻辑文件名（是在语言创建时要指定逻辑文件名（是在SQL SQL ServerServer中中使用的名称，是数据库在使用的名称，是数据库在SQL SERVERSQL SERVER中的标识）和中的标识）和操作系统文件名（在操作系统中存储时使用的文件名）。操作系统文件名（在操作系统中存储时使用

10、的文件名）。 （2）数据文件逻辑名后应加上）数据文件逻辑名后应加上_data_data后缀，事务日志文后缀，事务日志文件逻辑名后加入件逻辑名后加入_log_log后缀，以区别这两类文件；后缀，以区别这两类文件； （3） 缺省情况下，只有系统管理员可以创建新数据库，缺省情况下，只有系统管理员可以创建新数据库，但系统管理员可赋予其他用户创建数据库的权限；但系统管理员可赋予其他用户创建数据库的权限； （4） 所以的新数据库都是以所以的新数据库都是以ModelModel数据库为模板创建的，数据库为模板创建的，其大小可以被增大或收缩。创建时文件名要遵循其大小可以被增大或收缩。创建时文件名要遵循SQL S

11、QL ServerServer命名规范，一台服务器最多可创建命名规范，一台服务器最多可创建3276732767个数据库；个数据库； （5 5） 数据库信息保存在数据库信息保存在master数据库的数据库的sysdatabases系统表中系统表中 制作：荆州职业技术学院 彭岚任务实施与讲解任务实施与讲解任务要求：任务要求：创建创建“CPMS”数据库。该数据库包括数据库。该数据库包括3个数个数据库文件。保存路径：据库文件。保存路径：D:CPMS文件夹文件夹主数据文件名为主数据文件名为CPMS_data1.mdf，文件大小为，文件大小为50MB，最大文件大小为最大文件大小为200MB，文件增量为，文

12、件增量为10MB；次数据文件名为次数据文件名为CPMS_data2.ndf ，文件大小为，文件大小为50MB，最大文件大小为最大文件大小为200MB，文件增量为，文件增量为10MB；事务日志文件名为事务日志文件名为CPMS_log.ldf，文件大小为，文件大小为10MB，最，最大文件大小为大文件大小为20MB，文件增量为，文件增量为2MB。 制作：荆州职业技术学院 彭岚方法一：采用对象资源管理器创建数据库方法一：采用对象资源管理器创建数据库制作：荆州职业技术学院 彭岚 注意：注意： 当一个数据库对应于多个文件时，第一行当一个数据库对应于多个文件时，第一行中的文件为主要文件，其扩展名为中的文件为

13、主要文件，其扩展名为.Mdf，其他，其他行中的文件都只能是非主要文件，其扩展名行中的文件都只能是非主要文件，其扩展名为为.Ndf 制作：荆州职业技术学院 彭岚现场操作一现场操作一任务要求：创建一个任务要求：创建一个library数据库，数据库， 保存路径为保存路径为D：， 数据文件名为数据文件名为library _data.mdf，为，为主文件主文件， 初始大小为初始大小为2MB， 增长不受限制；增长不受限制； 事务日志文件事务日志文件名为名为library _log.ldf， 初始容量为初始容量为2mb， 最大值为最大值为10MB， 均以均以1MB增量增加。增量增加。 制作：荆州职业技术学院

14、 彭岚Create Database Create Database 数据库文件名数据库文件名 ON ON PRIMARY PRIMARY （ NAME=NAME=逻辑文件名，逻辑文件名， FILENAME=FILENAME=操作系统下的文件名和路径操作系统下的文件名和路径 ，SIZE=SIZE=文件初始容量文件初始容量 ，MAXSIZE=MAXSIZE=文件最大容量文件最大容量 | UNLIMITED | UNLIMITED ，FILEGROWTH=FILEGROWTH=递增容量递增容量 ） ， n n LOG ON LOG ON （ NAME=NAME=逻辑文件名，逻辑文件名， FILEN

15、AME=FILENAME=操作系统下的文件名和路径操作系统下的文件名和路径 ，SIZE=SIZE=文件初始容量文件初始容量 ，MAXSIZE=MAXSIZE=文件最大容量文件最大容量|UNLIMITED |UNLIMITED ，FILEGROWTH=FILEGROWTH=递增容量递增容量 ） ， n n 1、语语法法格格式式方法二：采用方法二：采用T-SQL语言创建数据库语言创建数据库制作：荆州职业技术学院 彭岚格式说明格式说明 （1） ONON子句子句指定数据库数据文件的磁盘文件清指定数据库数据文件的磁盘文件清单；单；n n表明可指定多个文件；表明可指定多个文件； （2）Primary：指定

16、主文件组的文件，指定主文件组的文件，Create Create databasedatabase语句中列出的第一个数据文件为主文件。语句中列出的第一个数据文件为主文件。 （3） LOG ON子句指定存储数据库事务日志文件子句指定存储数据库事务日志文件的磁盘表单；的磁盘表单； （4） SIZE：指定数据库的初始容量大小，单位可指定数据库的初始容量大小，单位可为：为：KB、MB、GB和和TB，省略主文件，省略主文件SIZE则和模则和模板数据库中的主文件大小一致，且不能小于模板数板数据库中的主文件大小一致，且不能小于模板数据库中的主文件大小。省略其它数据库文件和事务据库中的主文件大小。省略其它数据库

17、文件和事务日志文件日志文件SIZE则默认为则默认为1MB，其最小值为可设为，其最小值为可设为512KB，不能用小数。，不能用小数。 制作：荆州职业技术学院 彭岚 （5） MAXSIZEMAXSIZE：指定文件最大容量，未指指定文件最大容量，未指定及指定定及指定UNLIMITEDUNLIMITED则文件不断增长直到磁则文件不断增长直到磁盘变满为止盘变满为止 （6 6）FILEGrowthFILEGrowth：用于指定文件的增量，用于指定文件的增量，可使用可使用MBMB、KBKB和百分比指定单位，为零时和百分比指定单位，为零时不增长，其设置不能超过不增长，其设置不能超过SIZESIZE的大小，省的

18、大小，省略时默认为略时默认为10%.10%.制作：荆州职业技术学院 彭岚2 2几点说明：几点说明： （1） 创建用户数据库后，应及时备份数据创建用户数据库后，应及时备份数据库；库； （2）创建数据库的用户（）创建数据库的用户（dbo:data base dbo:data base ownerowner）是数据库的所有者，可通过系统存）是数据库的所有者，可通过系统存储过程储过程sp_changedbownersp_changedbowner改变数据库所有者。改变数据库所有者。 （3 3） 创建数据库的权限默认地授予创建数据库的权限默认地授予sysadmin和和dbcreator固定服务器角色的成

19、员。固定服务器角色的成员。 制作：荆州职业技术学院 彭岚 例 用Create Database语句创建一个数据库，名称为名称为“XK”，包含一个数据文件和一个事务日志文件。 数据文件数据文件为主要文件，其逻辑文件名为逻辑文件名为“XK_data”，实际文件名为“xk_data.Mdf”，初始容量为10MB，最大容量为50MB，自动增长时的递增量为2MB。 事务日志文件事务日志文件的逻辑文件名为“xk_log”，实际文件名为“xk_log.Ldf”，初始容量为5MB，最大容量为25MB，自动增长时的递增量为1MB。 制作：荆州职业技术学院 彭岚Create Database XKCreate D

20、atabase XKON PRIMARY ON PRIMARY -定义主数据文件定义主数据文件 ( NAME = xk_data, ( NAME = xk_data, -逻辑名称逻辑名称FILENAME = d:sjk xk_data.Mdf , FILENAME = d:sjk xk_data.Mdf , -操作系统文件路径及名称操作系统文件路径及名称SIZE = 10MB,SIZE = 10MB, -数据文件的初始容量为数据文件的初始容量为10MB MAXSIZE = 50MB, MAXSIZE = 50MB, -数据文件的最大容量为数据文件的最大容量为50MB FILEGROWTH =

21、2MB ) FILEGROWTH = 2MB ) -数据文件的增长容量为数据文件的增长容量为2MBLOG ON LOG ON -定义事务日志文件定义事务日志文件 ( NAME = xk_log, ( NAME = xk_log, -事务日志的逻辑名称事务日志的逻辑名称 FILENAME = d:sjk xk_log.Ldf, FILENAME = d:sjk xk_log.Ldf, -事务日志所在的操事务日志所在的操作系统文件路径及名称作系统文件路径及名称 SIZE = 5MB, SIZE = 5MB, -事务日志的初始容量事务日志的初始容量 MAXSIZE = 25MB, MAXSIZE =

22、 25MB, -事务日志的最大容量事务日志的最大容量 FILEGROWTH =1MB ) -事务日志的增长容量事务日志的增长容量 制作：荆州职业技术学院 彭岚用用T-SQLT-SQL语言查看数据库信息语言查看数据库信息 （1） 查看某个数据库或所有数据库的信息：查看某个数据库或所有数据库的信息： EXEC sp_helpdb EXEC sp_helpdb 数据库名数据库名 （2） 查看有关数据库中所占用空间的报表查看有关数据库中所占用空间的报表： use use 数据库名数据库名 EXEC sp_spaceusedEXEC sp_spaceused （3） 查看数据库中文件的报表：查看数据库中

23、文件的报表： use 数据库名数据库名 EXEC sp_helpfile 制作：荆州职业技术学院 彭岚任务要求任务要求 为为“CPMS”数据库添加一个数据文件，文件名为数据库添加一个数据文件，文件名为CPMS_data3.ndf ，文件大小为，文件大小为50MB，最大文件大小，最大文件大小为为200MB，文件增量为，文件增量为10MB。路径：。路径：D:CPMS文件文件夹。夹。 制作：荆州职业技术学院 彭岚用用T-SQL语言修改数据库信息语言修改数据库信息 ALTER Database ALTER Database 数据库名数据库名Add FileAdd File ，n TO Filegrou

24、p n TO Filegroup 文件组名文件组名 | Add Log | Add Log ，n n |Remove FILE |Remove FILE 逻辑文件名逻辑文件名 | Modify File | Modify File /*指定要增加数据库文件到哪个文件组指定要增加数据库文件到哪个文件组 /*指定要增加的事务日志文件指定要增加的事务日志文件 /*从数据库系统表中删除指定空文件的定从数据库系统表中删除指定空文件的定义，并且删除其物理文件义，并且删除其物理文件 /*修改指定文件的文件名、容量大小、修改指定文件的文件名、容量大小、文件增容方式等属性，使用文件增容方式等属性，使用Size时

25、，新的时，新的容量必须大于当前容量。新文件名在容量必须大于当前容量。新文件名在SQL Server重新启动时才会生效重新启动时才会生效 制作：荆州职业技术学院 彭岚其中其中 为：为：（ NAME = NAME = 逻辑文件名逻辑文件名 ，FILENAME = FILENAME = 实际文件名和路径实际文件名和路径 ，SIZE = SIZE = 容量容量 ，MAXSIZE = MAXSIZE = 最大容量最大容量 | UNLIMITED | UNLIMITED ，FILEGROWTH = FILEGROWTH = 递增量递增量 ）制作：荆州职业技术学院 彭岚数据库的收缩数据库的收缩任务要求：收缩

26、CPMS数据库，使其数据库文件的空间为原来的50%。 制作：荆州职业技术学院 彭岚用用T-SQLT-SQL语言收缩语言收缩 （1 1） 格式：格式： DBCC SHRINKDATABASE (DBCC SHRINKDATABASE (数据库名数据库名 ,收缩后的收缩后的剩余空间百分比剩余空间百分比 ,NOTRUNCATE|TRUNCATEONLY) ,NOTRUNCATE|TRUNCATEONLY) （2 2） 说明：说明：NOTRUNCATE|TRUNCATEONLYNOTRUNCATE|TRUNCATEONLY：为被释：为被释放空间的处理方法。放空间的处理方法。 NOTRUNCATENOT

27、RUNCATE：命名释放的文件空间保留在数据库文命名释放的文件空间保留在数据库文件中；件中； TRUNCATEONLYTRUNCATEONLY：将数据库文件中的任何未使用的空将数据库文件中的任何未使用的空间释放给操作系统间释放给操作系统 制作：荆州职业技术学院 彭岚数据库的更名数据库的更名 若数据库被设为默认数据库，则不允许改名。若数据库被设为默认数据库，则不允许改名。 格式：格式： SP_RENAMEDB 旧数据库名旧数据库名，新数据新数据库名库名 例：例：将已创建的将已创建的CPMS数据库改名为数据库改名为“CSMS” 。 sp_renamedb cpms,csms go制作：荆州职业技术

28、学院 彭岚数据库的删除数据库的删除（1） 格式：格式： DROP DATABASE DROP DATABASE 数据库名数据库名 （2） 说明：说明： 正在使用的数据库不能删除；正在使用的数据库不能删除； 使用使用DROP DATABASEDROP DATABASE命令一次可删除多个，而企命令一次可删除多个，而企业管理器一次只能删除一个数据库；业管理器一次只能删除一个数据库； 4 4个系统数据库个系统数据库mastermaster、 modelmodel、 tempdbtempdb、 msdbmsdb不能删除不能删除 删除前应备份数据库删除前应备份数据库。 例：删除例：删除CPMS数据库数据库

29、 drop database CPMS go制作：荆州职业技术学院 彭岚备份和恢复数据库 将整个数据库完全备份。 当数据库已经被完整备份一次以后，对数据库所做的修改内容所进行的备份制作：荆州职业技术学院 彭岚备份和恢复数据库步骤一：完全备份步骤一：完全备份数据库第一次备份时应采用完全备份数据库第一次备份时应采用完全备份。backup database CPMSto disk=d:CPMSCPMSBAK步骤二：差异备份步骤二：差异备份进行了完整备份后，就可采用差异备份了，其备份的数据主要进行了完整备份后，就可采用差异备份了，其备份的数据主要是完整备份后那些改变的部分。占用空间较少。是完整备份后那些改变的部分。占用空间较少。 backup database CPMSto disk=d:CPMSCPMSBAK with differential任务要求：将任务要求：将CPMS数据库备份至数据库备份至D盘的盘的CPMS文件夹中，文件夹中，文件名为文件名为“CPMSBAK.bak”制作：荆州职业技术学院 彭岚restore database CPMSfrom disk=d:CPMSCP