第10章数据库存储管理与数据恢复

1、第10章 数据库存储管理与数据恢复数据库存储管理与存储优化备份与恢复1本章主要内容文件组、分区表和索引备份、恢复技术在SQL Server中的备份/恢复操作210.1 数据库存储管理与存储优化SQL Server数据库的存储结构调整数据库文件组分区索引3SQL Server数据库的存储结构SQL用户SQL Server系统数据库master数据库tempdb数据库model数据库用户数据库基本表基本表基本表视图视图用户数据库物理文件物理文件物理文件图2-2 SQL Server的数据库结构4优化存储的手段文件组：通过文件组可以实现分类存储，可以把指定的数据存储到指定的物理文件。分区：通过建立分

2、区表，可以将超大型的表按指定的分区函数存储到指定的物理文件。索引：索引是提高查询性能的常用手段。5调整数据库调整或修改数据库的命令是ALTER DATABASE。ALTER DATABASE database ADD FILE ,.n | ADD LOG FILE ,.n | REMOVE FILE logical_file_name | MODIFY FILE 增加新的数据文件（ADD FILE）增加新的日志文件（ADD LOG FILE）删除逻辑文件（REMOVE FILE），同时自动删除对应的物理文件（只有文件为空才可以删除）修改已有物理文件的相关属性（MODIFY FILE）6例10-

3、1：为im08数据库增加一个5M大小的物理文件ALTER DATABASE im08 ADD FILE ( NAME = Test1dat2, FILENAME = C:2009_isdata t1dat2.ndf, SIZE = 5MB, MAXSIZE = 100MB,FILEGROWTH = 5MB)7例10-2：将im08数据库test1dat2所对应的物理文件增加到10M大小。ALTER DATABASE im08 MODIFY FILE (NAME = test1dat2,SIZE = 10MB)8例10-3：将im08数据库的test1dat2文件改名为test1dat1。ALT

4、ER DATABASE im08 MODIFY FILE ( NAME = Test1dat2,NEWNAME = Test1dat1 )例10-4：将im08数据库的test1dat1文件删除。ALTER DATABASE im08REMOVE FILE test1dat110例10-5：将is2009数据库的主数据文件student.mdf（逻辑文件名是student）移动到c:2009_isdata目录下。实现步骤：手工将文件移动到指定目录执行如下命令：ALTER DATABASE is2009 MODIFY FILE(NAME = student, FILENAME = c:2009_

5、isdatastudent.mdf) 重新启动SQL Server服务1110.1.3 文件组文件组是将物理存储文件分组。文件组分为主文件组和用户定义文件组两大类。主文件组包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有信息存储在主文件组中。用户定义文件组是通过在CREATE DATABASE或ALTER DATABASE语句中使用FILEGROUP关键字指定的任何文件组。一个物理文件只可以是一个文件组的成员。文件组与日志无关，日志空间与数据空间是分开管理的。12CREATE DATABASE MyDBON PRIMARY ( NAME=MyDB_Primary, FILE

6、NAME=c:MSSQLdataMyDB_Prm.mdf, SIZE=4MB, MAXSIZE=10MB, FILEGROWTH=1MB),FILEGROUP MyDB_FG1 ( NAME = MyDB_FG1_Dat1, FILENAME =c:MSSQLdataMyDB_FG1_1.ndf, SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB) LOG ON ( NAME=MyDB_log, FILENAME =c:MSSQLlogMyDB.ldf, SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=1MB)例10-6：创建一个数据库My

7、DB，该数据库包括一个主数据文件、一个用户定义文件组和一个日志文件。 13例10-7：把表创建到指定文件组。CREATE TABLE MyTable ( cola int PRIMARY KEY, colb char(8) )ON MyDB_FG1目的是把表创建到指定物理磁盘。14添加文件组、添加物理文件例10-8 增加文件组例10-9 为文件组增加文件 通过文件组可以把指定对象创建到指定物理磁盘。1610.1.4 分区分区就是把一个大型表的数据分门别类的分割、存储到不同的物理文件，以方便管理、提高效率（特别是提高并行处理能力）。分区是针对大型表，所以只有SQL Server Enterpri

8、se Edition（企业版）才支持分区。17建立分区表的步骤建立分区函数；根据分区函数创建分区方案；按分区方案建立表。18建立分区函数建立分区函数的命令是CREATE PARTITION FUNCTION ( )AS RANGE LEFT | RIGHT FOR VALUES ( ,.n ) 19例10-10：建立一个基于整数类型的分区函数。CREATE PARTITION FUNCTION myRangePF1 (int)AS RANGE LEFT FOR VALUES (1, 100, 1000)1 100 1000建立分区方案建立分区方案的命令是CREATE PARTITION SCH

9、EME，命令格式如下：CREATE PARTITION SCHEME AS PARTITION ALL TO ( | PRIMARY ,.n )21例10-12：根据例10-10建立的分区函数myRangePF1建立分区方案。CREATE PARTITION SCHEME myRangePS1AS PARTITION myRangePF1TO (test1fg, test2fg, test3fg, test4fg)建立分区表可以在CREATE TABLE语句的尾部使用ON短语来指定使用的分区方案。23例10-13：使用例10-12建立的分区方案建立一个分区表。CREATE TABLE Test

10、PartitionTable(col1 int, col2 char(10)ON myRangePS1 (col1)创建分区表的三步：定义分区函数建立分区方案建立分区表CREATE PARTITION FUNCTION myRangePF1 (int)AS RANGE LEFT FOR VALUES (1, 100, 1000)CREATE PARTITION SCHEME myRangePS1AS PARTITION myRangePF1TO (test1fg, test2fg, test3fg, test4fg)CREATE TABLE TestPartitionTable(col1 in

11、t, col2 char(10)ON myRangePS1 (col1)10.1.5 索引在关系数据库中索引是提高查询性能的主要手段。索引一般创建在表的某个或某些列上，索引关键字将会存储在一种B+树的数据结构中，使数据库管理系统（如SQL Server）可以快速有效地根据索引关键字查找到相关的记录。查询优化器在执行查询时通常会选择最有效的方法，它的依据就是索引。如果没有索引，查询优化器就必须扫描整张表。26索引的分类聚集索引聚集索引是一种物理排序的索引，所以在每个表上最多只能有一个聚集索引，并且聚集索引关键字是唯一的。当在表上创建PRIMARY KEY约束时就会自动建立聚集索引。非聚集索引唯一

12、索引保证索引关键字的唯一性当使用UNIQUE 约束时就会自动创建唯一索引普通索引为提高查询速度建立的索引27设计索引当表较小时一般不需要索引；当数据库处于频繁修改期时不宜建立过多的索引，因为系统必须为维护索引付出代价；当数据库主要用于查询时，可以根据需要多建立一些索引； 建立索引的字段一定是经常用来做查询条件的字段；如果视图包含聚合或连接运算，在视图上建立相关索引也可以显著提高性能。 聚集索引和唯一性索引有约束的作用，所以以上原则一般只适用于普通索引。28建立索引建立索引的一般命令是CREATE INDEX，基本格式如下：CREATE UNIQUE CLUSTERED | NONCLUSTER

13、ED INDEX ON ( ASC | DESC ,.n ) ON ( ) | 29例10-14：在订购单上经常需要按供应商号（供货方）进行查询，则可以为之建立一个普通索引。CREATE INDEX sup_idx ON 订货.订购单(供货方)30例10-15：如果经常需要根据多个字段的条件进行查询，也可以在多个字段上建立索引。 CREATE INDEX sup_emp_idx ON 订货.订购单(供货方,经手人 DESC) 例10-16：在仓库关系上规定一个城市只设立一个仓库，为此可以在仓库关系的城市属性上建立一个唯一索引：CREATE UNIQUE INDEX city_idx ON 仓储

14、.仓库(城市)如果表中某城市已经有两个仓库？注意：最好在创建任何非聚集索引之前创建聚集索引。 PRIMARY KEY自动创建聚集索引。UNIQUE约束自动创建唯一索引。为提高查询速度建立普通索引。索引会降低更新性能。在视图上建立索引对视图的操作将会转换为对表的操作，所以一般不需要在视图上建立索引。视图的某些列如果不是直接来自表、而是运算的结果，可以在这样的列上建立索引来提高查询速度。要建立索引的视图需要使用如下短语绑定架构： WITH SCHEMABINGING在视图上建立索引举例P262优化索引任何对数据库的插入、更新和删除操作数据库管理系统都会自动维护索引，从而会产生大量碎片，导致应用程序

15、响应缓慢。在SQL Server中可以通过sys.dm_db_index_physical_stats函数诊断碎片，然后通过ALTER INDEX命令重组或重新生成索引。36函数sys.dm_db_index_physical_stats的返回结果 列说明avg_fragmentation_in_percent 逻辑碎片（索引中的无序页）的百分比。fragment_count 索引中的碎片（物理上连续的叶页）数量。avg_fragment_size_in_pages 索引中一个碎片的平均页数。 一般可以按照avg_fragmentation_in_percent的值决定如何进行优化：当 5%

16、且 30%时，使用ALTER INDEX REBUILD重新生成索引。37获取碎片信息P263 例10-20重组或重新生成索引可用ALTER INDEX命令对索引进行重组或重新生成ALTER INDEX命令的基本格式是：ALTER INDEX ON REBUILD | DISABLE| REORGANIZE 其中：REBUILD：将使用相同的列、索引类型、唯一性属性和排序顺序重新生成索引（包括被DISABLE禁用的索引）；REORGANIZE：将重新组织的索引的叶子节点；DISABLE：把索引标记为禁用，被禁用的索引DBMS将不对它进行维护；如果要重新启用则使用ALTER INDEX REBU

17、ILD重新生成索引。39例10-21：使用ALTER INDEX REBUILD重新生成订购单表上的sup_idx索引。ALTER INDEX sup_idx ON 订货.订购单 REBUILD40删除索引删除索引的命令是DROP INDEX，基本格式如下：DROP INDEX ON 例10-22：删除订购单表上的sup_idx索引DROP INDEX sup_idx ON 订货.订购单4110.2 备份与恢复数据库恢复概述故障类型备份类型日志的概念恢复模型备份和恢复策略备份操作恢复或还原42数据库恢复概述数据库安全性控制防范的是人，目的是拒绝非授权的用户访问数据库，以保证数据库数据的安全。另

18、一类安全性问题是要预防各种非人为因素或人为因素的计算机故障。为了应付这些故障，多数情况下需要为数据库制作备份，在故障排除后，再利用备份的数据进行恢复。事务的原子性、一致性和持久性均需要恢复技术的支持。43故障类型造成事务中断的故障突然掉电引起的事务中断；硬件故障引起的事务中断；客户应用程序出错引起的事务中断；系统程序故障引起的事务中断。解决这类问题的方法显然就是将数据库恢复到修改之前的状态，即撤消只执行了一半的事务。存储介质故障解决这类故障的切实有效办法就是备份，在修复或更换磁盘后再恢复。44备份类型双机热备份双工备份磁盘镜像冗余磁盘阵列数据库备份技术45日志的概念备份是定期的、而不是实时的，

19、所以利用备份并不能完全恢复数据库，它只能将数据库恢复到制作备份的那一时刻。日志则是对备份的补充，它可以看作是一个值班日记，它将记录下所有对数据库的更新操作。这样就可以在备份完成时立刻刷新并启用一个数据库日志，数据库日志是实时的，它将忠实地记录下所有对数据库的更新操作。为了保证日志的安全，应该将日志和主数据库安排在不同的存储设备上。46恢复模型简单恢复模型简单恢复允许将数据库恢复到最新的备份，即使用简单恢复模型可以将数据库恢复到上次备份的即时点，而无法将数据库恢复到故障点或特定的即时点。简单恢复模型的数据库只能做数据库备份，不能做日志备份。完全恢复模型完全恢复允许将数据库恢复到故障点状态，即完全

20、恢复模型使用数据库备份和事务日志备份提供对介质故障的完全防范。47设置恢复模型的命令ALTER DATABASE语句的RECOVERY子句48例10-23：将仓储订货数据库的恢复模型设置为完全恢复。ALTER DATABASE 仓储订货SET RECOVERY FULL备份的类型全备份增量备份事务日志备份文件和文件组备份50备份和恢复策略动态备份和静态备份动态备份也称作在线备份，即在做备份时不中断数据库的运行，不中断数据库上的应用程序和事务处理。静态备份也称作离线或脱机备份，这意味着在做备份时没有任何数据库事务在运行。51备份和恢复策略如果数据库对每天的事务处理都至关重要，那么就必须经常备份；

21、对不变的历史数据可以只备份一次，但要多复制几个备份，以免备份介质损坏而造成数据丢失；如果数据库的使用频率和更新频率非常高，可以考虑每天做一次全备份，做几次增量备份；相反，如果数据库的更新频率不太高，只需要每周、甚至每月做一次全备份。更多细节？P26852备份全数据库备份增量备份事务日志备份文件或文件组备份系统数据库的备份备份整个数据库BACKUP DATABASE database_name TO DISK | TAPE =physical_backup_device_name 54例10-24：将仓储订货数据库备份到C:dumpdumpfull.bakBACKUP DATABASE 仓储订货

22、 TO DISK=C:dumpdumpfull.bak增量备份BACKUP DATABASE database_name TO DISK | TAPE =physical_backup_device_name WITH DIFFERENTIAL增量备份的基准？上一次全备份。56例10-25：对仓储订货数据库做增量备份（备份到C:dumpdiff1.bak）BACKUP DATABASE 仓储订货 TO DISK=C:dumpdiff1.bak WITH DIFFERENTIAL事务日志备份BACKUP LOG database_name TO DISK | TAPE =physical_bac

23、kup_device_name每次日志备份的基准？是上一次备份。58例10-26：备份仓储订货数据库的日志（备份到C:dumpdumplog.bak）BACKUP LOG 仓储订货 TO DISK= C:dumpdumplog.bak备份小结全备份、增量备份、日志备份构成了一个完整的数据库备份方案，当数据库遇到灾难时，利用这些备份可以完全恢复数据库。文件或文件组备份BACKUP DATABASE database_nameFILE = logic_file_list | FILEGROUP = filegroup_list TO DISK | TAPE =physical_backup_dev

24、ice_name WITH DIFFERENTIAL 更灵活的备份解决方案61文件或文件组备份操作例10-27：完成对仓储订货数据库warehouse文件的备份：BACKUP DATABASE 仓储订货FILE = warehouseTO DISK =C:dumpfile_1.bak例10-28：完成对仓储订货数据库文件组“仓库”的备份：BACKUP DATABASE 仓储订货FILEGROUP = 仓库TO DISK = C:dumpfile_g.bak 62系统数据库的备份数据库备份不仅仅是要备份用户数据库，系统数据库也需要备份，例如SQL Server中的master、model等系统数

25、据库。备份master等系统数据库是系统管理员的职责。一般在执行了更新系统表的命令之后（如CREATE DATABASE、ALTER DATABASE等命令都将更新系统表）都要备份master数据库，所以必须经常性地、定期地备份master数据库。master数据库只能进行全备份。63恢复或还原与备份类型相对应，恢复可以是：恢复整个数据库恢复数据库的部分内容恢复特定的文件或文件组恢复事务 可以将数据库恢复到做备份的即时点、发生故障的即时点或特定的事务即时点。 64发生灾难后如何进行恢复？备份尾日志利用全备份进行恢复利用最后一次增量备份进行恢复利用备份的事务日志链完成恢复备份尾日志当数据库发生灾

26、难时，要做的第一件事是备份事务日志，这是上次备份之后发生在数据库中的更改操作，这时候的日志也称作尾日志。66根据数据库全备份进行恢复RESTORE DATABASE database_nameFROM DISK | TAPE =physical_backup_device_name WITH , NORECOVERY | RECOVERY , REPLACE 67根据增量备份进行恢复 在简单恢复模型和完全恢复模型中都可以选择增量备份，如果存在增量备份，则一般需要进行相应的恢复操作。 增量恢复数据库的命令也是RESTORE DATABASE，但是在根据增量备份继续恢复之前应该：已经使用RESTO

27、RE DATABASE命令完成了全备份的恢复，同时指定了NORECOVERY子句在进行增量恢复时根据需要指定RECOVERY或NORECOVERY子句68根据事务日志进行恢复 利用日志可以将数据库恢复到最新的一致状态或任意的事务点。 首先恢复事务日志备份之前的数据库备份或增量数据库备份。 如果有多个日志备份，则按先后顺序进行恢复。 69根据事务日志进行恢复RESTORE LOG database_nameFROM DISK | TAPE =physical_backup_device_name WITH , NORECOVERY | RECOVERY , STOPAT = date_time

28、| , STOPATMARK = mark_name AFTER datetime | , STOPBEFOREMARK=mark_name AFTER datetime 70 对仓储订货数据库进行了如下所示的备份，现在该数据库崩溃，要求完全恢复数据库。1. BACKUP LOG 仓储订货 TO DISK=C:dumpraillog.bak2. RESTORE DATABASE 仓储订货FROM DISK = C:dumpdumpfull.bak WITH NORECOVERY 3. RESTORE DATABASE 仓储订货FROM DISK = C:dumpdiff1.bak WITH N

29、ORECOVERY4. RESTORE LOG 仓储订货FROM DISK = C:dumpdumplog.bak WITH NORECOVERY5. RESTORE LOG 仓储订货FROM DISK = C:dumpraillog.bak WITH RECOVERY完全备份到dumpfull.bak增量备份到diff1.bak日志备份到dumplog.bak灾难71根据文件或文件组备份进行恢复 如果数据库的某个文件损坏了，并且按文件或文件组做了备份，则可以考虑根据文件或文件组备份进行恢复。 当使用文件或文件组备份进行恢复时，最后一个文件或文件组恢复操作完成后，必须将事务日志应用于数据库文件

30、，以便使之与数据库的其余部分保持一致。如果被恢复的文件自上次备份后没有做过任何修改操作，则不必应用事务日志。 72根据文件或文件组备份进行恢复RESTORE DATABASE database_name FILE = logical_file_name | FILEGROUP = logical_filegroup_name FROM DISK | TAPE =physical_backup_device_name WITH NORECOVERY | RECOVERY 73恢复系统数据库 备份系统数据库与备份用户数据库的方式相同，除master数据库之外其他系统数据库的恢复也与恢复用户数据库类

31、似。 master数据库是所有数据库的主数据库，也是管理所有数据库的数据库。恢复其他数据库都是在SQL Server能够正常运行的基础上进行的，而master数据库的损坏可能导致SQL server根本不能运行，所以恢复master数据库是一件特殊的任务。74恢复master数据库如果master数据库只是轻微损坏或信息丢失，master数据库的内容至少部分可用，从而能够启动SQL Server实例，则可以直接根据master数据库的完整备份恢复master数据库。 如果由于master数据库严重损坏而无法启动SQL Server实例，则不能立即恢复master数据库。因为SQL Server实例需要处于运行状态才能恢复任何数据库。75恢复master数据库SQL Server 2000可以使用重建master数据库实用