1、3 数据库管理,3.1 数据库的存储结构 3.2 创建、修改和删除数据库 3.3 数据库备份 3.4 恢复数据库 3.5 数据库的维护,上一章,返回目录,3.1 数据库的存储结构,3.1.1 数据库文件3.1.2 数据库文件组,3.1.1 数据库文件,逻辑存储结构和物理存储结构 一、数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成，SQL Server的数据库不仅仅只是数据的存储，所有与数据处理操作相关的信息都存储在数据库中。 二、数据库的物理存储结构则是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个数
2、据库文件和一个事务日志文件。,3.1.1 数据库文件,1. 主数据库文件（Primary Database File） 包含数据库的启动信息，并用于存储数据。每个数据库都有且仅有一个主数据文件。扩展名为.mdf。 2. 辅助数据库文件（Secondary Database File）次数据文件。 包含不能存于主数据文件中的所有数据。次数据文件扩展名为.ndf。 3. 事务日志文件 事务日志文件。 存放事务日志，扩展名为.ldf。,3.1.2 数据库文件组,为了便于分配和管理，SQL Server允许将多个文件归纳为同一组，并赋予此组一个名称，这就是文件组。 与数据库文件一样，文件组也分为主文件
3、组.mdf（Primary File Group）和次文件组（Secondary File Group）.ndf。,3.2 创建、修改 、查看和删除数据库,3.2.1 创建数据库 3.2.2 查看、修改数据库 3.2.3 删除数据库,3.2.1 创建数据库,每个数据库都由以下几个部分的数据库对象所组成： 关系图、表、视图、存储过程、用户、角色、规则、默认、用户自定义数据类型和用户自定义函数。,创建数据库的方法,使用向导创建数据库 使用企业管理器（Enterprise Manager）创建数据库 使用Transact-SQL语言创建数据库。,（一）使用向导创建数据库,以下图3-1 到图3-8是使
4、用向导创建数据库 ，用户根据提示操作，即可创建数据库。,图3-1 选择向导对话框,图3-2 欢迎使用创建数据库向导对话框,图3-3 输入数据库名称和文件位置对话框,图3-4 定义数据库文件逻辑名称及初始大小对话话框,图3-5 定义数据库增长信息对话框,图3-6 定义事务日志文件名称及初始大小对话框,图3-7 定义事务日志文件增长信息对话框,图3-8 确认创建数据库对话框,（2）使用企业管理器创建数据库,（）在企业管理器中，单击工具栏中的 图标，或在数据库文件夹或其下属任一数据库图标上单击右键，选择新建数据库选项，就会出现如图4-9所示的对话框。 （）在常规（General）页框中，要求用户输入
5、数据库名称以及排序规则名称。 （）点击数据文件（Data Files）页框，该页框用来输入数据库文件的逻辑名称、存储位置、初始容量大小和所属文件组名称，如图4-10所示。 （）点击事务日志（Transaction Log）页框，该页框用来设置事务日志文件信息，如图4-11所示。 （）单击图4-11中的“确定”按钮，则开始创建新的数据库。,图3-9 创建数据库对话框中的常规页框,图3-10 创建数据库对话框中的数据文件页框,图3-11 创建数据库对话框中的事务日志页框,（3） 使用Transact-SQL语言创建数据库，语法如下：,CREATE DATABASE database_name ON
6、 PRIMARY ，n ， ，n LOG ON ，nFOR RESTORE :=(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：指定数据库的逻辑名称，这是在SQL Ser
7、ver系统中使用的名称，是数据库在SQL Server中的标识符。,各参数说明如下：,FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。 SIZE：指定数据库的初始容量大小。 MAXSIZE：指定操作系统文件可以增长到的最大尺寸。 FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。,例子3-1：创建了一个Company数据库,创建了一个Company数据库，该数据库的主数据文件逻辑名称为Company_data，物理文件名为Company.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；
8、数据库的日志文件逻辑名称为Company_log，物理文件名为Company.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。 程序清单4-1：,例子3-2：创建一个指定多个数据文件和日志文件的数据库,该数据库名称为employees，有1个10MB和1个20MB的数据文件和2个10MB的事务日志文件。数据文件逻辑名称为employee1和employee2，物理文件名为employee1.mdf和employee2.mdf。主文件是employee1，由primary指定，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件的逻辑名为emp
9、loyeelog1和employeelog2，物理文件名为employeelog1.ldf和employeelog2.ldf，最大尺寸均为50MB，文件增长速度为1MB。 其源程序为：,例3-3用T-SQL命令来创建“贴吧”数据库TB,CREATE DATABASE tbON PRIMARY(NAME=tb_data,FILENAME=d:datatb_data.mdf,SIZE=5,FILEGROWTH=2,MAXSIZE=64)LOG ON(NAME=tb_log,FILENAME= d:datatb_log.ldf,SIZE=3,FILEGROWTH=1,MAXSIZE=32),3.2.
10、2数据库的日常检查和维护,监控错误日志 通过文本编辑器查看 通过SQL Server Management Studio查看。,记录配置信息 如果SQL Server不能启动时，可以借助服务器的配置信息恢复服务器的运行，所以应该把配置信息的维护列入日常维护计划，尤其是当配置信息修改时。 可以使用命令EXEC sp_configure 来生成服务器的配置信息列表,监视系统运行状况，及时处理系统错误 使用系统过程sp_who可以查看到进程号、当前状态、注册用户名、主机名、数据库名以及当前命令等信息。,保证系统数据安全，周期更改用户口令sp_password old_password,new_pas
11、sword,login_name 其中的三个参数的意义分别是：旧口令、新口令和登录名。,3.2.3查看数据库,查看数据库信息 （1）查看系统当前所有可使用数据库的名称和大小等信息。EXEC sp_databases （2）查看某个数据库基本信息EXEC sp_helpdb stud,3.2.4 修改数据库,1. 利用企业管理器修改数据库 2. 使用ALTER DATABASE语句修改数据库,1. 利用企业管理器修改数据库,设置中的各选项说明如下 ： ANSI NULL默认设置：允许在数据库表的列中输入空（NULL）值。 递归触发器：允许触发器递归调用。SQL Server设定的触发器递归调用的
12、层数最多为32层。 自动更新统计信息：允许使用SELECT INTO或BCP、WRITETEXT、UPDATETEXT命令向表中大量插入数据。残缺页检测：允许自动检测有损坏的页。 自动关闭：当数据库中无用户时，自动关闭该数据库，并将所占用的资源交还给操作系统。,自动收缩：允许定期对数据库进行检查，当数据库文件或日志文件的未用空间超过其大小的25%时，系统将会自动缩减文件使其未用空间等于25%。 自动创建统计信息：在优化查询（Query Optimizer）时，根据需要自动创建统计信息。 使用被引用的标识符：标识符必须用双引号括起来，且可以不遵循Transact-SQL命名标准。,2. 使用AL
13、TER DATABASE语句修改数据库语法形式如下：,Alter database databasename add file,n to filegroup filegroupname |add log file ,n |remove file logical_file_name with delete |modify file |modify name=new_databasename |add filegroup filegroup_name |remove filegroup filegroup_name |modify filegroup filegroup_name filegrou
14、p_property|name=new_filegroup_name,例3-3为xsgl数据库增加数文件，xsglak，文件初始大小为2MB,最大为5MB，增长幅度为5%,Alter database xsgl Add file (name= xsglak, Filename=d:sql xsglak_data.ndf, Size=2MB, Maxsize=5MB, Filegrowth=5%),例3-4修改xsgl数据库中的第二个数据文件xsglak最大大小为10MB。,Alter database xsgl Modify file (name= xsglak, Maxsize=10MB),
15、例子3-5,例子3-5：添加一个包含两个数据文件的文件组和一个事务日志文件到employees数据库中。,3.2.5 删除数据库,1.利用企业管理器删除数据库 2. 利用Drop语句删除数据库Drop语句可以从SQL Server中一次删除一个或多个数据库。其语法如下： Drop database database_name,n,例子3-6：删除创建的数据库company。,drop database company 输出结果为： Deleting database file d:mssql7datacompany.ldf. Deleting database file d:mssql7dat
16、acompany.mdf.,例3-7删除xsgl数据库中的第二个数据库,并将数据库更名为xsxxgl,Alter database xsgl Modify file Remove file xsglbakAlter database xsgl Modify name=xsxxgl,3.3 数据库备份,3.3.1 备份概述 3.3.2 创建备份设备 3.3.3 备份的执行,3.3.1 备份概述,备份就是对SQL Server数据库或事务日志进行备份，数据库备份记录了在进行备份这一操作时数据库中所有数据的状态，以便在数据库遭到破坏时能够及时地将其恢复。,SQL Server 2000四种备份方式,
17、（一）完全数据库备份（Dadabase-complete） （二）差异备份或称增量备份（Dadabase-differential） （三）事务日志备份（Transaction log） （四）数据库文件和文件组备份（File and filegroup）,3.3.2 创建备份设备,在进行备份以前首先必须指定或创建备份设备，备份设备是用来存储数据库、事务日志或文件和文件组备份的存储介质，备份设备可以是硬盘、磁带或管道。当使用磁盘时，SQL Server允许将本地主机硬盘和远程主机上的硬盘作为备份设备，备份设备在硬盘中是以文件的方式存储的。,创建备份设备的两种方法 ：,1 .使用SQL Serv
18、er 企业管理器创建备份设备 2. 使用系统存储过程创建备份设备 在SQL Serve可以使用sp_addumpdevice语句创建备份设备，其语法形式如下： sp_addumpdevice device_type ,logical_name,physical_name,controller_type|device_status,使用系统存储过程创建备份设备,sp_addumpdevice设备类型（disk），逻辑设备名，物理设备名 例：为xsgl数据创建一个磁盘备份设备，其逻辑名为xsgl，物理名为E:wrzxsgl_bak.bak sp_addumpdevice disk,xsgl,E:w
19、rzxsgl_bak.bak,图3-17 使用企业管理器创建备份设备,图3-18 输入备份设备属性对话框,例子3-8：在磁盘上创建了一个备份设备,use masterexec sp_addumpdevice disk, backup_company, c:mssql7backupbk_company.bak,删除备份设备,删除备份设备与创建的过程类似，只须在企业管理器中选中要删除的备份设备，在弹出的菜单中选择删除选项即可删除该备份设备。或者使用sp_dropdevice语句来删除备份设备。其语法如下： sp_dropdevice logical_name,delfile 例子4-6：删除上面创
20、建的备份设备 sp_dropdevice backup_company, c:mssql7backupbk_company.bak,3.3.3 备份的执行,SQL Server系统提供了三种数据库备份操作的方法： (1)SQL Server 企业管理器 (2)备份向导 (3)Transact-SQL语句。,. 使用SQL Server 企业管理器进行备份,（）启动企业管理器，登录到指定的数据库服务器，打开数据库文件夹，用右键单击所要进行备份的数据库图标，在弹出的快捷菜单中选择所有任务，再选择备份数据库 。 （）出现SQL Server备份对话框，对话框中有两个页框，即常规和选项页框。 （）在常
21、规页框中，选择备份数据库的名称、操作的名称、描述信息、备份的类型、备份的介质、备份的执行时间。 （）通过单击添加按钮选择备份设备 。 （）选择调度复选框，来改变备份的时间安排。 （ 6 ）在选项页框中进行附加设置 。,. 使用备份向导 （图4-25 -图4-31）,图3-25 欢迎使用向导对话框,图3-26 选择数据库对话框,图3-27 输入备份名称和描述信息对话框,图3-28 选择备份数据库类型对话框,图3-29 设置备份介质类型和属性对话框,图3-30 备份验证和调度对话框,图3-31 确认执行备份操作对话框,3. 使用Transact-SQL语句备份数据库,其语法形式如下： Backup
22、 database database_name|database_name_var to ,n with ,format ,init|noinit ,restart :=backup_file_name|backup_file_evar|disk|tape|pipe =temp_file_name|temp_file_name_evar,备份数据库： Backup database 数据库名 to disk=物理磁盘名,完全数据备份： Backup database 数据库名 to 备份设备名 差异数据库备份：Backup database 数据库名 to 备份设备名 with differe
23、ntial 数据库日志备份 backup log 数据库名 to 备份设备名 例1 将整个xsgl数据库备份到磁盘文件中 Backup database xsgl to disk=E:wrzxsgl_bak.bak Backup database stubook to backup_company with differential Backup database stubook to backup_company backup log stubook to backup_company,3.4 恢复数据库,3.4.1 恢复概述 3.4.2 恢复数据库 3.4.3 恢复系统数据库,3.4.1
24、恢复概述,数据库备份后，一旦系统发生崩溃或者执行了错误的数据库操作，就可以从备份文件中恢复数据库。数据库恢复是指将数据库备份加载到系统中的过程。系统在恢复数据库的过程中，自动执行安全性检查、重建数据库结构以及完整数据库内容。,3.4.2 恢复数据库,使用企业管理器恢复数据库 （1）打开企业管理器，单击要登录的数据库服务器，然后从主菜单中选择工具，在菜单中选择还原数据库命令。 （2）在还原为数据库旁的下拉列表中选择要恢复的数据库，在还原组中通过单击单选按钮来选择相应的数据库备份类型。 （3）选中选项页框，进行其它选项的设置 。,其语法形式如下： Restore database from wit
25、h ,file=file_number ,move logical_file_name to operating_system_file_name ,replace ,norecovery|recovery|standby=undo_file_name :=backup_device_name|backup_device_name_evar |disk|tape|pipe =temp_backup_device|temp_backup_device_var,2. 使用Transact-SQL语句恢复数据库,例子3-7：从备份设备中恢复数据库,从backup_company备份设备中恢复数据库c
26、ompany。 use master restore database company From backup_company with file=1,norecovery,3.4.3 恢复系统数据库,1. 关闭SQL Server，运行系统安装目录下的bin子目录下的rebuilem.exe文件，这是个命令行程序，运行后可以重新创建系统数据库。 2. 系统数据库重新建立后，启动SQL Server。 3. SQL Server启动后，系统数据库是空的，没有任何系统信息。因此，需要从备份数据库中恢复。一般是先恢复master数据库，再恢复msdb数据库，最后恢复model数据库。,3.5 数据
27、库的维护,利用数据库的维护计划向导可以方便地设置数据库的核心维护任务，以便于定期地执行这些任务，其创建数据库维护计划的步骤如下： 如图4-35-图4-49所示,图3-35 选择数据库维护计划对话框,图3-36 欢迎使用维护计划向导对话框,图3-37 选择数据库对话框,图3-38 更新数据优化信息对话框,图3-39 编辑循环作业调度对话框,图3-40 检查数据库完整性对话框,图3-41 指定数据库备份计划对话框,图3-42 指定数据库备份文件存放目录对话框,图3-43 指定事务日志备份计划对话框,图3-44 指定事务志文件备份存放目录对话框,图3-45 维护计划生成报表对话框,图3-46 指定维护计划历史记录存放位置对话框,图3-47 指定数据库维护计划名称对话框,图3-48 数据库维护计划对象选择对话框,图3-49 数据库维护计划编辑对话框,
