网站数据库技术chap8-sql server数据库系统管理

1、第8章 SQL SERVER数据库系统管理本章目标本章结束时，学员能够了解和掌握：SQL SERVER 2000 DBA职能简介数据库设计及规划SQL SERVER 2000数据库操作分离数据库附加数据库删除数据库收缩数据库SQL SERVER 2000备份备份设备简介备份的类型备份的内容规划备份策略SQL SERVER 2000数据库恢复 恢复的概念 从不同的备份中恢复数据库数据转换与导入导出 数据转换的方法8.1 DBA （DataBase Administrator） 简介DBA所要掌握的技能SQL ServerWindows 2000/NT关系型数据库SQL语言备份和恢复过程业务处理D

2、BA的职责安装SQL Server建立和维护数据库管理用户管理权限备份和恢复数据调度任务监视和调整服务器管理SQL Server复制。8.2 认识系统数据库系统数据库用户数据库mastermodeltempdbmsdbpubsNorthwindUser1distributionDistribution在分发复制时系统自动创建8.3 数据库设计及规划Microsoft SQL Server 2000 使用一组操作系统文件映射数据库。数据库中的所有数据和对象（如表、存储过程、触发器和视图）都存储在下列操作系统文件中： 主文件：该文件包含数据库的启动信息，并用于存储数据。每个数据库都有一个主要数据文

3、件辅助文件：这些文件含有不能置于主要数据文件中的所有数据。如果主文件可以包含数据库中的所有数据，那么数据库就不需要次要数据文件。有些数据库可能足够大，因而需要多个次要数据文件，或使用位于不同磁盘驱动器上的辅助文件将数据扩展到多个磁盘事务日志：这些文件包含用于恢复数据库的日志信息。每个数据库都必须至少有一个日志文件。8.3数据库设计及规划(续)在设计数据库时，应考虑以下事项：数据库的用途及该用途将如何影响设计。应创建符合用途的数据库计划数据库规范化规则，防止数据库设计中出现错误对数据完整性的保护数据库和用户权限的安全要求应用程序的性能需求维护估计数据库大小8.3数据库设计及规划(续)设计规则:获

4、得合理的数据库设计的规则：表应该有一个标识符表应只能存储单一类型实体的数据表应避免可为空的列表不应有重复的值或列考虑将重复的数据置于与主表有链接的另一个表中 8.3数据库设计及规划(续)文件设计规则:文件或文件组不能由一个以上的数据库使用文件只能是一个文件组的成员数据和事务日志信息不能属于同一文件或文件组事务日志文件不能属于任何文件组8.4 数据库管理创建数据库数据库属性收缩数据库和数据库文件删除数据库移动数据库数据库维护计划 8.4.1创建数据库在创建数据库之前，请考虑如下事项： 创建数据库的权限默认授予 sysadmin 和 dbcreator 固定服务器角色的成员，但是它仍可以授予其他用

5、户创建数据库的用户将成为该数据库的所有者在一个服务器上，最多可以创建 32,767 个数据库数据库名称必须遵循标识符规则创建数据库方法TransactSQL企业管理器企业管理创建数据库向导8.4.1.1用TransactSQL创建数据库CREATE DATABASE SampleON PRIMARY ( NAME=SampleData, FILENAME=c:Program Files.DataSample.mdf, SIZE=10MB, MAXSIZE=15MB, FILEGROWTH=20%) LOG ON ( NAME=SampleLog, FILENAME= c:Program Fil

6、es.DataSample.ldf, SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=1MB) 8.4.1.1用TransactSQL创建数据库（续）在这里输入创建数据库的SQL语句SQL语句执行结果，显示创建成功单击运行按钮执行创建数据库的语句 8.4.1.2利用企业管理器来创建数据库 8.4.1.2利用企业管理器来创建数据库（续） 8.4.1.2利用企业管理器来创建数据库（续）新创建的数据库 8.4.1.3利用数据库向导创建数据库 8.4.1.3利用数据库向导创建数据库（续） 8.4.1.3利用数据库向导创建数据库（续） 8.4.1.3利用数据库向导创建数据库（续） 8

7、.4.2数据库属性 8.4.2数据库属性（续）8.4.3收缩数据库和数据库文件经常对数据库做删除插入操作就会产生空白页为了节省磁盘空间我们可以定期进行数据库收缩数据库收缩并不是压缩数据而是降低空白页的数目数据库不能收缩到比创建时还小不能在备份数据库或者日志时执行收缩任务 8.4.3 收缩数据库和数据库文件（续）使用设定的压缩计划来压缩数据库设定空闲空间占文件大小的百分比单独指定数据库中各数据文件和事务日志文件的压缩操 8.4.3收缩数据库和数据库文件（续） 8.4.3收缩数据库和数据库文件（续） 8.4.4删除数据库DROP DATABASE Northwind, pubs 8.4.4删除数据

8、库(续)下列情况下不能删除数据库：当有用户连接到数据库时当作为复制的一部分正在被出版时当为系统数据库时 8.4.5 移动数据库分离、附加如果想按以下方式移动数据库，则分离和附加数据库很有用： 从一台计算机移到另一台计算机，而不必重新创建数据库，然后手动还原数据库备份。移到另一物理磁盘上，例如，当包含该数据库文件的磁盘空间已用完，您希望扩充现有的文件而又不愿将新文件添加到其它磁盘上的数据库。 将数据库或数据库文件移动到另一服务器或磁盘： 分离数据库。 将数据库文件移到另一服务器或磁盘。 通过指定移动文件的新位置附加数据库。 8.4.5 移动数据库（续）分离 8.4.5 移动数据库(续)附加数据库

9、 8.4.6数据库维护计划 8.4.6数据库维护计划（续） 8.4.6数据库维护计划（续） 8.4.6数据库维护计划（续） 8.4.6数据库维护计划（续） 8.4.6数据库维护计划（续） 8.4.6数据库维护计划（续） 8.4.6 数据库维护计划（续） 8.4.6数据库维护计划（续） 8.4.6数据库维护计划（续） 8.4.6数据库维护计划（续）8.5数据库的备份备份的概念及作用制作数据库结构和数据库的拷贝防止数据库遭到破坏备份数据库的频繁程度取决于许损失的数据量及数据库活动量备份的准则处于在线事务处理(OLTP)环境中，要经常性地备份系统活动不多或主要用于决策支持，不用太频繁的备份把备份安排

10、在SQL Server没有被大量更新的过程中8.5.1备份的概述使用恢复模型可以为 Microsoft SQL Server 2000 中的每个数据库选择三种恢复模型中的一种，以确定如何备份数据以及能承受何种程度的数据丢失。 简单恢复 :简单恢复允许将数据库恢复到最新的备份。完全恢复 :完全恢复允许将数据库恢复到故障点状态。大容量日志记录恢复: 大容量日志记录恢复允许大容量日志记录操作。当创建新的数据库时，新的数据库继承 model 数据库的恢复模型8.5.1备份的概述(续)使用恢复模型故障还原模型8.5.1备份的概述(续)SQL Server备份备份的角色成员 sysadmin、 db_ow

11、ner、 db_backupoperator备份的介质 硬盘、磁带或命名管道Named Pipe SQL Server提供备份到Named Pipe的能力，以便用户充分利用其它厂家软件包的备份和恢复特性 8.5.1备份的概述(续)备份的内容备份系统数据库 主要指master、 msdb和 model数据库备份用户数据库备份过程中限制的活动 8.5.1备份的概述(续)备份系统数据库修改master数据库之后Mssq180BinnRebuildm.exe执行下列语句后，自动修改CREATE DATABASE、ALTER DATABASE 或 DROP DATABASEsp_logdevice系统存

12、储过程sp_addserver, sp_dropserver 和sp_addlinkedserver修改msdb数据库之后修改model数据库之后 8.5.1备份的概述(续)备份用户数据库在创建数据库后在创建索引后在清除了事务日志后若创建了索引之后，您只备份了事务日志，并在未来某一时刻恢复该事务日志，则SOL Server必须重建索引。要求用来重建索引的时间量，可能会比恢复复全库备份的时间还要长。在执行完不记日志的操作后不被记录到事务日志的操作BACKUP LOG WITH NO_LOG语句WRITETEXT或UPDATETEXT语句SELECT INTO语句8.5.1备份的概述(续) 备份过

13、程中受限制的活动创建或修改数据库创建索引执行不记日志的操作8.5.2 执行备份使用永久性备份文件使用临时性备份文件 8.5.2.1创建永久性备份文件永久性备份的含义自动化备份任务8.5.2.1创建永久性备份文件(续) 8.5.2.1创建永久性备份文件(续)新创建的备份设备 8.5.2.2临时性的备份文件临时性备份文件的含义执行一次性备份 使用BACKUP DATABASE 语句8.5.2.2临时性的备份文件（续）下面这个例子在硬盘上创建一个临时性备份文件，并且把northwind数据库备份到此临时性备份文件中。USE masterBACKUP DATABASE northwind to DIS

14、K=C:TempMycustomers.bak8.5.3备份类型 全库备份 差异备份事务日志备份一个数据库文件或文件组的备份 8.5.3.1执行一个全库备份 如果您的数据库主要是一个只读数据库，全库备份或许足以防止数据损失。在系统出故障的 情况下，可把全库备份用作基线。当您执行全库备份时，SQL Server要：备份在备份过程中发生的任何活动。把任何未提交的事务备份到事务日志。 恢复备份时，SQL Server利用备份文件中捕捉到的部分事务日志来确保数据一致性。BACKUP DATABASE northwind to nwndbac 8.5.3.2执行差异备份用于经常被修改的数据库要求一个全库

15、备份备份自上次全库备份以来的数据库变化因备份集小而节省时间语法： BACKUP DATABASE northwind TO DISK=D:MydataMydiffbackup.bak WITH DIFFERENTIAL 8.5.3.3执行一个事务日志备份提供对任何数据库变化的备份 要求一个全库备份备份从最近执行一次BACKUPLOG语句之后到当前事务日志末尾的全部数据库更改截断事务日志 8.5.3.3执行一个事务日志备份(续)BACKUP LOG的语法形式BACKUP LOG northwind TO nwndbac使用NO-TRUNCATE选项允许在数据库损坏时备份日志使用TRUNCATE-

16、ONLY选项 8.5.3.4执行一个数据库文件或文件组的备份用与特大型数据库（VLDB）单独备份数据库文件确保备份一个文件组中的所有的数据库文件备份事务日志语法： BACKUP DATABASE phoneorders FILE=orders2 to orderbackup2 BACKUP LOG phoneorders to orderbackuplog 8.5.4规划一个备份策略全库备份策略全库及事务日志备份策略差异备份策略数据库文件或文件组备份策略 8.5.4规划一个备份策略（续）全库备份策略实施的情况：数据库较小，备份一个小数据库所需的时间量是可接受的；数据库没有什么数据修改或是只读型

17、的 8.5.4规划一个备份策略（续）全库和事务日志备份策略实施的情况：对经常被修改的数据库实行全库和事务日志备份备份策略及恢复策略:存储方式全库备份及事务日志备份时间恢复的方法和时间 8.5.4规划一个备份策略（续）差异备份策略实施的情况对数据库做更改时使用缩短还原时间备份策略及恢复策略全库备份时间及差异备份时间全库恢复时间及差异恢复时间 8.5.4规划一个备份策略（续）数据库文件和文件组备份策略 实施的情况:应用于划分多个文件的数据库保证数据的一致性备份策略和恢复策略:数据划分全库备份时间和文件备份时间，日志备份时间恢复时间8. 6数据库的恢复 数据库恢复是指将数据库备份加载到系统中的过程

18、恢复是与备份相对应的操作8.6.1恢复的概念 系统在恢复进程中的活动：安全性检查、重建数据库及文件Restore语句不能正确恢复的情况备份文件中数据库不同于指定的数据库服务器上的文件集不同于备份中的文件集没有提供恢复数据库的全部文件或文件组恢复进程自动进行8.6.1恢复的概念(续)验证备份 两种验证的方法：使用SQL Server Enterprise Manager查看每一个备份设备的属性 使用一些Transact-SQL语句，可以得到更加详细的备份信息。验证备份信息常用的Transact-SQL语 句有：RESTORE HEADERONLY、RESTORE FILELISTONLY、RES

19、TORE LABELONLY、RESTORE VERIFYONLY8.6.1恢复的概念(续)在恢复之前执行指定的任务必须限制用户对数据库的访问以及事务日志的备份应该设置数据库的dbo use only选项使用SQL Server Enterprise Manager执行系统存储过程sp_dboption。语法： sp_dboptiondbname=database ,optname=option_name ,optvalue=value 8.6.1恢复的概念在恢复之前执行指定的任务（续）8.6.1恢复的概念(续)恢复数据库的方法两种恢复操作的方法如下所示：使用RESTORE语句使用SQL Se

20、rver 企业管理器 8.6.1恢复的概念(续)使用RESTORE语句恢复数据库RESTORE DATABASE database_name_varFROM,.nWITH , FILE=file_number , MOVE logical_file_name TO operating_system_file_name , REPLACE , NORECOVERYRECOVERY STANDBY=undo_file_name其中是backup_file_namebackup_file_name_var DISKTAPEPIPE=temp_backup_filetemp_backup_file_v

21、ar 8.6.1恢复的概念使用RESTORE语句恢复数据库（续）在RESTORE语句中，可以使用一些选项 ：RECOVERY RECOVERY选项是系统的默认选项。该选项用于恢复最后一个事务日志或者完全数据库恢复，可以保证数据库的一致性。当使用该选项时，系统取消事务日志中任何未提交的事务，并提交任何完成的事务。在数据库恢复进程完成之后，就可以使用数据库。如果必须使用增量备份恢复数据库，就不能使用该选项。NORECOVERY 当需要恢复多个备份时，应使用NORECOVERY选项。这时，系统既不取消事务日志中任何未提交的事务，也不提交任何已完成的事务。在数据库恢复之前，数据库是不能使用的8.6.1

22、恢复的概念使用RESTORE语句恢复数据库（续）示例 ：从一个已经存在的永久性备份文件saledbk中恢复数据库sales。USE masterRESTORE DATABASE sales FROM saledbk 8.6.1恢复的概念(续)使用企业管理器恢复数据库 8.6.1恢复的概念使用企业管理器恢复数据库（续） 8.6.2 从不同的备份中恢复数据库 从完全数据库备份中恢复从差异备份中恢复 从事务日志备份中恢复 从文件或者文件组备份中恢复 8.6.2.1从完全数据库备份中恢复 重新创建数据库以及与数据库相关的全部文件所有的数据库对象都由系统自动创建用户应指定RECOVERY选项例如：USE

23、 masterRESTORE DATABASE northwindFROM nwindbacWITH FILE =2RECOVERY对于用户来说，没有必要在恢复数据库之前重新创建数据库。一般在数据库的物理磁盘文件损坏，或者整个数据库被删除、破坏时，应该从完全数据库备份中恢复8.6.2.2从差异备份中恢复 SQL Server系统只恢复从最近一次完全数据库备份以后数据库的变化部分，并且将数据库返回到执行增量备份时的状态从增量备份中恢复数据库所需要的时间比从事务日志备份中恢复数据库要少还原差异数据库备份的顺序为： 还原最新的数据库备份。还原最后一次的差异数据库备份。如果使用完全恢复或大容量日志记录恢复，则应用自上次创建差异数据库备份后创建的所有事务日志备份。 8.6.2.2从差异备份中恢复(续)下例还原 MyNwind 数据库的全库和差异数据库备份: RESTORE DATABASE MyNwind FROM MyNwind_1 WITH NORECOVERY GO RESTORE DATABASE MyNwind FROM MyNwind_1 WITH FILE = 2 RECOVERY8.6.2.3从事务日