数据库系统及应用（第5版）课件 第11章 数据库存储管理与数据恢复

文件组文件组2生产数据购买数据销售数据文件组文件组将指定数据存储到指定位置解决硬件空间不够不是文件组的主要目的文件组的主要目的是要提高对数据库的访问速度文件组的目的文件组

文件组分为主文件组和用户定义文件组两大类主文件组：包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有信息存储在主文件组中。用户定义文件组：通过在CREATEDATABASE或ALTERDATABASE语句中使用FILEGROUP关键字指定的任何文件组。文件组是将物理存储文件分组文件组一个物理文件只可以是一个文件组的成员文件组与日志无关，日志空间与数据空间是分开管理的文件组AdventureWorksDatabasePrimaryFilegroupOrderHistoryGroupE:\C:\D:\AdventureWorks_

Log.IdfAdventureWorks_

Data.mdfOrdHist2.ndfOrdHist1.ndfsys...

sys...

sysusers

sysobjects

...

SalesOrderHeader

Customer

Product

OrdHistYear2

OrdHistYear1

？例6创建一个数据库MyDB，该数据库包括一个主数据文件、一个用户定义文件组和一个日志文件。例6例6CREATEDATABASEMyDBONPRIMARY(NAME='MyDB_Primary',FILENAME='c:\MSSQL\data\MyDB_Prm.mdf',SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB),FILEGROUPMyDB_FG1(NAME='MyDB_FG1_Dat1',FILENAME='c:\MSSQL\data\MyDB_FG1_1.ndf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOGON(NAME='MyDB_log',FILENAME='c:\MSSQL\log\MyDB.ldf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB)MyDB_PrimaryMyDB_FG1_Dat1文件组：PRIMARY文件组：MyDB_FG1CREATEDATABASEMyDBONPRIMARY(NAME='MyDB_Primary',FILENAME='c:\MSSQL\data\MyDB_Prm.mdf',SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB),FILEGROUPMyDB_FG1(NAME='MyDB_FG1_Dat1',FILENAME='c:\MSSQL\data\MyDB_FG1_1.ndf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOGON(NAME='MyDB_log',FILENAME='c:\MSSQL\log\MyDB.ldf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB)MyDB_PrimaryMyDB_FG1_Dat1文件组：PRIMARY文件组：MyDB_FG1查看C:\mssql\下的目录例7例7把表MyTable创建到指定文件组MyDB_FG1。CREATETABLEMyTable(colaintPRIMARYKEY,

colbchar(8))ONMyDB_FG1MyDB_PrimaryMyDB_FG1_Dat1文件组：PRIMARY文件组：MyDB_FG1colacolb表：MyTable区别？CREATETABLEMyTable1(colaintPRIMARYKEY,

colbchar(8))和CREATETABLEMyTable(colaintPRIMARYKEY,

colbchar(8))ONMyDB_FG1讨论：如何验证MyTable表及其数据存储到了指定物理文件？讨论如何验证MyTable表及其数据存储到了指定物理文件？1、向MyTable插入几条记录

INSERTINTOMyTableVALUES(1,'student')INSERTINTOMyTableVALUES(2,'teacher')INSERTINTOMyTableVALUES(3,'mother')15MyDB_PrimaryMyDB_FG1_Dat1文件组：PRIMARY文件组：MyDB_FG1表：MyTablecolacolb如何验证MyTable表及其数据存储到了指定物理文件？1、向MyTable插入几条记录

INSERTINTOMyTableVALUES(1,'student')INSERTINTOMyTableVALUES(2,'teacher')INSERTINTOMyTableVALUES(3,'mother')16MyDB_PrimaryMyDB_FG1_Dat1文件组：PRIMARY文件组：MyDB_FG1表：MyTablecolacolb1Student2Teacher3mother2、尝试用如下语句删除物理文件ALTERDATABASEmydbREMOVEFILEMyDB_FG1_Dat1

删除命令不能执行，说明MyTable表及其数据存储在物理文件MyDB_FG1_Dat1中。1717MyDB_PrimaryMyDB_FG1_Dat1文件组：PRIMARY文件组：MyDB_FG1表：MyTable如何验证MyTable表及其数据存储到了指定物理文件？colacolb1Student2Teacher3mother3、删除MyTable表DROPTABLEMyTable4、尝试用如下语句删除物理文件

ALTERDATABASEmydbREMOVEFILEMyDB_FG1_Dat118MyDB_PrimaryMyDB_FG1_Dat1文件组：PRIMARY文件组：MyDB_FG1表：MyTable如何验证MyTable表及其数据存储到了指定物理文件？colacolb1Student2Teacher3mother3、删除MyTable表DROPTABLEMyTable4、尝试用如下语句删除物理文件ALTERDATABASEmydbREMOVEFILEMyDB_FG1_Dat11919MyDB_Primary文件组：PRIMARY文件组：MyDB_FG1删除命令执行成功。如何验证MyTable表及其数据存储到了指定物理文件？例8为MyDB数据库增加一个文件组TestFG2。ALTERDATABASEMyDBADDFILEGROUPTestFG2这里只增加了一个文件组，初始是空的。2020MyDB_Primary文件组：PRIMARY文件组：MyDB_FG1文件组：TestFG2添加文件组例9为文件组TestFG2指定两个物理文件。ALTERDATABASEMyDBADDFILE(NAME=testdat2,FILENAME='C:\MSSQL\DATA\tdat2.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB),(NAME=testdat3,FILENAME='C:\MSSQL\DATA\tdat3.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)

TOFILEGROUPTestFG2

MyDB_Primary文件组：PRIMARY

文件组：MyDB_FG1

testdat2testdat3文件组：TestFG2添加物理文件CREATEDATABASE仓储订货ONPRIMARY(NAME=order_dat,FILENAME='d:\orderdat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat2,FILENAME='d:\orderdat2.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPFG2(NAME=order_dat3,FILENAME='d:\orderdat3.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat4,FILENAME='d:\orderdat4.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPFG3(NAME=order_dat5,FILENAME='d:\orderdat5.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat6,FILENAME='d:\orderdat6.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME=order_log,FILENAME='d:\orderlog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB),(NAME=order_log2,FILENAME='d:\orderlog2.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)22CREATEDATABASE仓储订货ONPRIMARY(NAME=order_dat,FILENAME='d:\orderdat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat2,FILENAME='d:\orderdat2.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPFG2(NAME=order_dat3,FILENAME='d:\orderdat3.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat4,FILENAME='d:\orderdat4.ndf',SIZE=10,MAXSIZE=50),FILEGROWTH=5),FILEGROUPFG3(NAME=order_dat5,FILENAME='d:\orderdat5.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat6,FILENAME='d:\orderdat6.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME=order_log,FILENAME='d:\orderlog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB),(NAME=order_log2,FILENAME='d:\orderlog2.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)23CREATEDATABASE仓储订货ONPRIMARY(NAME=order_dat,FILENAME='d:\orderdat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat2,FILENAME='d:\orderdat2.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPFG2(NAME=order_dat3,FILENAME='d:\orderdat3.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat4,FILENAME='d:\orderdat4.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPFG3(NAME=order_dat5,FILENAME='d:\orderdat5.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat6,FILENAME='d:\orderdat6.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME=order_log,FILENAME='d:\orderlog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB),(NAME=order_log2,FILENAME='d:\orderlog2.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)24CREATEDATABASE仓储订货ONPRIMARY(NAME=order_dat,FILENAME='d:\orderdat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat2,FILENAME='d:\orderdat2.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPFG2(NAME=order_dat3,FILENAME='d:\orderdat3.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat4,FILENAME='d:\orderdat4.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPFG3(NAME=order_dat5,FILENAME='d:\orderdat5.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat6,FILENAME='d:\orderdat6.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME=order_log,FILENAME='d:\orderlog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB),(NAME=order_log2,FILENAME='d:\orderlog2.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)25CREATEDATABASE仓储订货ONPRIMARY(NAME=order_dat,FILENAME='d:\orderdat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat2,FILENAME='d:\orderdat2.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPFG2(NAME=order_dat3,FILENAME='d:\orderdat3.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat4,FILENAME='d:\orderdat4.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPFG3(NAME=order_dat5,FILENAME='d:\orderdat5.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=order_dat6,FILENAME='d:\orderdat6.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME=order_log,FILENAME='d:\orderlog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB),(NAME=order_log2,FILENAME='d:\orderlog2.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)分区表分区的概念建立分区表的步骤主要内容1.分区的概念分区就是把一个大型表的数据分门别类的存放在不同的位置，这样查询数据时，就去指定位置查找，从而提高查询速度。分区是针对大型表，其好处是方便管理、提高效率（特别是提高并行处理能力）。举例假设Bills表每个月都有上百万条的话单记录，由于数据量非常大，可以在创建Bills表时按月分区，将每个月的数据存放在不同位置。Bills1月份数据2月份数据3月份数据12月份数据文件组2文件组3文件组12………………文件组1第二步第三步第一步建立分区函数根据分区函数创建分区方案按分区方案建立表2.建立分区表的步骤(1)建立分区函数建立分区函数的命令：CREATEPARTITIONFUNCTION<分区函数名>(<参数类型>)ASRANGE[LEFT|RIGHT]FORVALUES([<临界值>[,...n]])其中：分区函数名必须唯一；参数类型可以是整型、货币型等。不能是text、image、timestamp等大文本或二进制类型；

ASRANGE表示按范围分区。LEFT|RIGHT说明等于哪一侧的临界值，默认是LEFT临界值指定每个区域，即分区的边界值；临界值指定每个区域，即分区的边界值。第二步第三步第一步建立分区函数根据分区函数创建分区方案按分区方案建立表举例在MyDB数据库中，创建分区表Product。ProductPrice<=100的记录Price>100并且<=1000的记录Price>1000的记录C:\dataPriceData1.ndfD:\dataPriceData2.ndfE:\dataPriceData3.ndfFG1FG2FG3CREATEPARTITIONFUNCTIONmyRangePF(money)ASRANGELEFTFORVALUES(100,1000)例1：建立一个基于货币类型的分区函数。1001000例1.

建立一个基于货币类型的分区函数2.建立分区方案建立分区方案的命令是CREATEPARTITIONSCHEME，命令格式如下：CREATEPARTITIONSCHEME<分区方案名>ASPARTITION<分区函数名>[ALL]TO({<文件组名>|[PRIMARY]}[,...n])第二步第三步第一步建立分区函数根据分区函数创建分区方案按分区方案建立表(2)建立分区函数创建三个文件组（1）ALTERDATABASEMyDBADDFILEGROUPFG1（2）ALTERDATABASEMyDBADDFILEGROUPFG2（3）ALTERDATABASEMyDBADDFILEGROUPFG3

创建分区方案CREATEPARTITIONSCHEMEmyRangePSASPARTITIONmyRangePFTO(FG1,FG2,FG3)例2：根据例1建立的分区函数myRangePF建立分区方案(3)

建立分区表可以在CREATETABLE语句的尾部使用ON短语来指定使用的分区方案。第二步第三步第一步建立分区函数根据分区函数创建分区方案按分区方案建立表先分别为每一个文件组增加1个物理文件第一步第二步第三步（1）ALTERDATABASEMyDBADDFILE(NAME='FG1Data',FILENAME='c:\data\PriceData1.ndf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB)TOFILEGROUPFG1（2）ALTERDATABASEMyDBADDFILE(NAME='FG2Data',FILENAME=‘d:\data\PriceData2.ndf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB)TOFILEGROUPFG2（3）ALTERDATABASEMyDBADDFILE(NAME='FG3Data',FILENAME=‘e:\data\PriceData3.ndf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB)TOFILEGROUPFG3CREATETABLEProduct( PID int, PName nvarchar(30), Price money)ONmyRangePS(Price)例3：使用例2建立的分区方案建立分区表Product创建分区表的三步030201创建分区函数CREATEPARTITIONFUNCTIONmyRangePF(money)ASRANGELEFTFORVALUES(10,100)创建分区方案CREATEPARTITIONSCHEMEmyRangePSASPARTITIONPricePFTO(FG1,FG2,FG3)创建分区表CREATETABLEProduct(PIDint,PNamenvarchar(30),Pricemoney)ONmyRangePS(Price)数据库备份与恢复数据备份与恢复数据库安全性控制防范的是人，目的是拒绝非授权的用户访问数据库，以保证数据库数据的安全。另一类安全性问题是要预防各种非人为因素或人为因素的计算机故障。为了应付这些故障，多数情况下需要为数据库制作备份，在故障排除后，再利用备份的数据进行恢复。事务的原子性、一致性和持久性均需要恢复技术的支持。42故障类型造成事务中断的故障突然掉电引起的事务中断；硬件故障引起的事务中断；客户应用程序出错引起的事务中断；系统程序故障引起的事务中断。

解决这类问题的方法显然就是将数据库恢复到修改之前的状态，即撤消只执行了一半的事务。存储介质故障

解决这类故障的切实有效办法就是备份，在修复或更换磁盘后再恢复。43事务故障的例子BEGINTRANSACTIONUPDATEaccountSETbalance=balance-15000WHEREname='A公司'UPDATEaccountSETbalance=balance+15000WHEREname='B公司'COMMITTRANSACTION44故障如何保证账目平衡？只需要撤销事务

计算机的数据备份类型双机热备份双工备份磁盘镜像冗余磁盘阵列数据库备份技术45阿里巴巴异地双活数据库备份技术？数据库备份技术数据备份和日志的有机结合保证将数据完全恢复46日志的概念备份是定期的、而不是实时的，所以利用备份并不能完全恢复数据库，它只能将数据库恢复到制作备份的那一时刻。日志则是对备份的补充，它可以看作是一个值班日记，它将记录下所有对数据库的更新操作。这样就可以在备份完成时立刻刷新并启用一个数据库日志，数据库日志是实时的，它将忠实地记录下所有对数据库的更新操作。为了保证日志的安全，应该将日志和主数据库安排在不同的存储设备上。47恢复模型简单恢复模型简单恢复允许将数据库恢复到最新的备份，即使用简单恢复模型可以将数据库恢复到上次备份的即时点，而无法将数据库恢复到故障点或特定的即时点。简单恢复模型的数据库只能做数据库备份，不能做日志备份。完全恢复模型完全恢复允许将数据库恢复到故障点状态，即完全恢复模型使用数据库备份和事务日志备份提供对介质故障的完全防范。48设置恢复模型的命令ALTERDATABASE语句的RECOVERY子句49例10-23：将仓储订货数据库的恢复模型设置为完全恢复。ALTERDATABASE仓储订货SETRECOVERYFULL备份的类型全备份增量备份事务日志备份文件和文件组备份系统数据库的备份50备份和恢复策略动态备份和静态备份动态备份也称作在线备份，即在做备份时不中断数据库的运行，不中断数据库上的应用程序和事务处理。静态备份也称作离线或脱机备份，这意味着在做备份时没有任何数据库事务在运行。51备份和恢复策略如果数据库对每天的事务处理都至关重要，那么就必须经常备份；对不变的历史数据可以只备份一次，但要多复制几个备份，以免备份介质损坏而造成数据丢失；如果数据库的使用频率和更新频率非常高，可以考虑每天做一次全备份，做几次增量备份；相反，如果数据库的更新频率不太高，只需要每周、甚至每月做一次全备份。52备份整个数据库BACKUPDATABASEdatabase_name

TO{DISK|TAPE}='physical_backup_device_name'53例10-24：将仓储订货数据库备份到C:\dump\dumpfull.bakBACKUPDATABASE仓储订货TODISK='C:\dump\dumpfull.bak'增量备份BACKUPDATABASEdatabase_name

TO{DISK|TAPE}='physical_backup_device_name'

WITHDIFFERENTIAL54增量备份的基准？上一次全备份。例10-25:对仓储订货数据库做增量备份(备份到C:\dump\diff1.bak)BACKUPDATABASE仓储订货

TODISK='C:\dump\diff1.bak'WITHDIFFERENTIAL数据库恢复模式？如果是简单模式，无日志功能，将数据库设置为完全恢复模式55SELECTrecovery_model,recovery_model_descFROMsys.databasesWHEREname='仓储订货'事务日志备份BACKUPLOGdatabase_name

TO{DISK|TAPE}='physical_backup_device_name'56每次日志备份的基准？是上一次备份。例10-26：备份仓储订货数据库的日志（备份到C:\dump\dumplog.bak）BACKUPLOG仓储订货TODISK='C:\dump\dumplog.bak'备份小结全备份、增量备份、日志备份构成了一个完整的数据库备份方案，当数据库遇到灾难时，利用这些备份可以完全恢复数据库。57文件或文件组备份BACKUPDATABASEdatabase_name{FILE=logic_file_list|FILEGROUP=

filegroup_list}TO{DISK|TAPE}='physical_backup_device_name'[WITHDIFFERENTIAL]58更灵活的备份解决方案文件或文件组备份操作例10-27：完成对仓储订货数据库warehouse文件的备份：BACKUPDATABASE仓储订货FILE='warehouse'TODISK='C:\dump\file_1.bak'例10-28：完成对仓储订货数据库文件组“仓库”的备份：BACKUPDATABASE仓储订货FILEGROUP='仓库'TODISK='C:\dump\file_g.bak'59系统数据库的备份数据库备份不仅仅是要备份用户数据库，系统数据库也需要备份，例如SQLServer中的master、model等系统数据库。备份master等系统数据库是系统管理员的职责。一般在执行了更新系统表的命令之后（如CREATEDATABASE、ALTERDATABASE等命令都将更新系统表）都要备份master数据库，所以必须经常性地、定期地备份master数据库。master数据库只能进行全备份。60恢复或还原与备份类型相对应，恢复可以是：恢复整个数据库恢复数据库的部分内容恢复特定的文件或文件组恢复事务61可以将数据库恢复到做备份的即时点、发生故障的即时点或特定的事务即时点。发生灾难后如何进行恢复？备份尾日志利用全备份进行恢复利用最后一次增量备份进行恢复利用备份的事务日志链完成恢复62备份尾日志当数据库发生灾难时，要做的第一件事是备份事务日志，这是上次备份之后发生在数据库中的更改操作，这时候的日志也称作尾日志。63BACKUPLOG仓储订货TODISK='C:\dump\dumplog1.bak'……备份尾日志（仓储订货数据库损坏后）根据数据库全备份进行恢复RESTOREDATABASEdatabase_nameFROM{DISK|TAPE}='physical_backup_device_name'[WITH[[,]{NORECOVERY|RECOVERY}][[,]REPLACE]]64根据增量备份进行恢复在简单恢复模型和完全恢复模型中都可以选择增量备份，如果存在增量备份，则一般需要进行相应的恢复操作。增量恢复数据库的命令也是RESTOREDATABASE，但是在根据增量备份继续恢复之前应该：已经使用RESTOREDATABASE命令完成了全备份的恢复，同时指定了NORECOVERY子句在进行增量恢复时根据需要指定RECOVERY或NORECOVERY子句65根据事务日志进行恢复利用日志可以将数据库恢复到最新的一致状态或任意的事务点。首先恢复事务日志备份之前的数据库备份或增量数据库备份。如果有多个日志备份，则按先后顺序进行恢复。66根据事务日志进行恢复RESTORELOGdatabase_nameFROM{DISK|TAPE}='physical_backup_device_name'[WITH[[,]{NORECOVERY|RECOVERY

}][[,]STOPAT=date_time|[,]STOPATMARK='mark_name'[AFTER