第10章數(shù)據(jù)庫存儲管理與數(shù)據(jù)恢復

第10章數(shù)據(jù)庫存儲管理與數(shù)據(jù)恢復本章主要內(nèi)容、分區(qū)表備份、恢復技術(shù)在SQLServer中的備份/恢復操作210.1數(shù)據(jù)庫存儲管理與存儲優(yōu)化SQLServer數(shù)據(jù)庫的存儲結(jié)構(gòu)調(diào)整數(shù)據(jù)庫文件組分區(qū)索引3SQLServer數(shù)據(jù)庫的存儲結(jié)構(gòu)SQL用戶SQLServer系統(tǒng)數(shù)據(jù)庫master數(shù)據(jù)庫tempdb數(shù)據(jù)庫model數(shù)據(jù)庫用戶數(shù)據(jù)庫基本表基本表基本表視圖視圖用戶數(shù)據(jù)庫………………物理文件物理文件物理文件……圖2-2SQLServer的數(shù)據(jù)庫結(jié)構(gòu)4優(yōu)化存儲的手段文件組：通過文件組可以實現(xiàn)分類存儲，可以把指定的數(shù)據(jù)存儲到指定的物理文件。分區(qū)：通過建立分區(qū)表，可以將超大型的表按指定的分區(qū)函數(shù)存儲到指定的物理文件。索引：索引是提高查詢性能的常用手段。5調(diào)整數(shù)據(jù)庫調(diào)整或修改數(shù)據(jù)庫的命令是ALTERDATABASE。ALTERDATABASEdatabase{ADDFILE<filespec>[,...n]

|ADDLOGFILE<filespec>[,...n]

|REMOVEFILElogical_file_name

|MODIFYFILE<filespec>}增加新的數(shù)據(jù)文件（ADDFILE）增加新的日志文件（ADDLOGFILE）刪除邏輯文件（REMOVEFILE），同時自動刪除對應的物理文件（只有文件為空才可以刪除）修改已有物理文件的相關(guān)屬性（MODIFYFILE）6例10-1：為im08數(shù)據(jù)庫增加一個5M大小的物理文件ALTERDATABASEim08ADDFILE(NAME=Test1dat2,FILENAME='C:\2009_is\data\t1dat2.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)7例10-2：將im08數(shù)據(jù)庫test1dat2所對應的物理文件增加到10M大小。ALTERDATABASEim08MODIFYFILE(NAME=test1dat2,SIZE=10MB)8例10-3：將im08數(shù)據(jù)庫的test1dat2文件改名為test1dat1。ALTERDATABASEim08MODIFYFILE(NAME=Test1dat2,NEWNAME=Test1dat1)例10-4：將im08數(shù)據(jù)庫的test1dat1文件刪除。ALTERDATABASEim08REMOVEFILEtest1dat110例10-5：將is2009數(shù)據(jù)庫的主數(shù)據(jù)文件student.mdf（邏輯文件名是student）移動到c:\2009_is\data\目錄下。實現(xiàn)步驟：手工將文件移動到指定目錄執(zhí)行如下命令：ALTERDATABASEis2009MODIFYFILE(NAME=student,FILENAME='c:\2009_is\data\student.mdf')

重新啟動SQLServer服務1110.1.3文件組文件組是將物理存儲文件分組。文件組分為主文件組和用戶定義文件組兩大類。主文件組包含主數(shù)據(jù)文件和任何沒有明確分配給其他文件組的其他文件。系統(tǒng)表的所有信息存儲在主文件組中。用戶定義文件組是通過在CREATEDATABASE或ALTERDATABASE語句中使用FILEGROUP關(guān)鍵字指定的任何文件組。一個物理文件只可以是一個文件組的成員。文件組與日志無關(guān)，日志空間與數(shù)據(jù)空間是分開管理的。12CREATEDATABASEMyDBONPRIMARY(NAME='MyDB_Primary',FILENAME='c:\MSSQL\data\MyDB_Prm.mdf',SIZE=4MB,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)例10-6：創(chuàng)建一個數(shù)據(jù)庫MyDB，該數(shù)據(jù)庫包括一個主數(shù)據(jù)文件、一個用戶定義文件組和一個日志文件。

13例10-7：把表創(chuàng)建到指定文件組。CREATETABLEMyTable(colaintPRIMARYKEY,

colbchar(8))ONMyDB_FG1目的是把表創(chuàng)建到指定物理磁盤。14添加文件組、添加物理文件例10-8增加文件組例10-9為文件組增加文件通過文件組可以把指定對象創(chuàng)建到指定物理磁盤。1610.1.4分區(qū)分區(qū)就是把一個大型表的數(shù)據(jù)分門別類的分割、存儲到不同的物理文件，以方便管理、提高效率（特別是提高并行處理能力）。分區(qū)是針對大型表，所以只有SQLServerEnterpriseEdition（企業(yè)版）才支持分區(qū)。17建立分區(qū)表的步驟建立分區(qū)函數(shù)；根據(jù)分區(qū)函數(shù)創(chuàng)建分區(qū)方案；按分區(qū)方案建立表。18建立分區(qū)函數(shù)建立分區(qū)函數(shù)的命令是CREATEPARTITIONFUNCTION<分區(qū)函數(shù)名>(<參數(shù)類型>

)ASRANGE[LEFT|RIGHT]FORVALUES([<臨界值>[,...n]])

19例10-10：建立一個基于整數(shù)類型的分區(qū)函數(shù)。CREATEPARTITIONFUNCTIONmyRangePF1(int)ASRANGELEFTFORVALUES(1,100,1000)11001000建立分區(qū)方案建立分區(qū)方案的命令是CREATEPARTITIONSCHEME，命令格式如下：CREATEPARTITIONSCHEME<分區(qū)方案名>ASPARTITION<分區(qū)函數(shù)名>[ALL]TO({<文件組名>|[PRIMARY]}[,...n])21例10-12：根據(jù)例10-10建立的分區(qū)函數(shù)myRangePF1建立分區(qū)方案。CREATEPARTITIONSCHEMEmyRangePS1ASPARTITIONmyRangePF1TO(test1fg,test2fg,test3fg,test4fg)建立分區(qū)表可以在CREATETABLE語句的尾部使用ON短語來指定使用的分區(qū)方案。23例10-13：使用例10-12建立的分區(qū)方案建立一個分區(qū)表。CREATETABLETestPartitionTable( col1int, col2char(10))ONmyRangePS1(col1)創(chuàng)建分區(qū)表的三步：定義分區(qū)函數(shù)建立分區(qū)方案建立分區(qū)表CREATEPARTITIONFUNCTIONmyRangePF1(int)ASRANGELEFTFORVALUES(1,100,1000)CREATEPARTITIONSCHEMEmyRangePS1ASPARTITIONmyRangePF1TO(test1fg,test2fg,test3fg,test4fg)CREATETABLETestPartitionTable( col1int,col2char(10))ONmyRangePS1(col1)10.1.5索引在關(guān)系數(shù)據(jù)庫中索引是提高查詢性能的主要手段。索引一般創(chuàng)建在表的某個或某些列上，索引關(guān)鍵字將會存儲在一種B+樹的數(shù)據(jù)結(jié)構(gòu)中，使數(shù)據(jù)庫管理系統(tǒng)（如SQLServer）可以快速有效地根據(jù)索引關(guān)鍵字查找到相關(guān)的記錄。查詢優(yōu)化器在執(zhí)行查詢時通常會選擇最有效的方法，它的依據(jù)就是索引。如果沒有索引，查詢優(yōu)化器就必須掃描整張表。26索引的分類聚集索引聚集索引是一種物理排序的索引，所以在每個表上最多只能有一個聚集索引，并且聚集索引關(guān)鍵字是唯一的。當在表上創(chuàng)建PRIMARYKEY約束時就會自動建立聚集索引。非聚集索引唯一索引保證索引關(guān)鍵字的唯一性當使用UNIQUE約束時就會自動創(chuàng)建唯一索引普通索引為提高查詢速度建立的索引27設(shè)計索引當表較小時一般不需要索引；當數(shù)據(jù)庫處于頻繁修改期時不宜建立過多的索引，因為系統(tǒng)必須為維護索引付出代價；當數(shù)據(jù)庫主要用于查詢時，可以根據(jù)需要多建立一些索引；建立索引的字段一定是經(jīng)常用來做查詢條件的字段；如果視圖包含聚合或連接運算，在視圖上建立相關(guān)索引也可以顯著提高性能。聚集索引和唯一性索引有約束的作用，所以以上原則一般只適用于普通索引。28建立索引建立索引的一般命令是CREATEINDEX，基本格式如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX<索引名>

ON<表或視圖>(<列>

[ASC|DESC][,...n])

[ON{<分區(qū)>

(<列名>)|<文件組>

}

]29例10-14：在訂購單上經(jīng)常需要按供應商號（供貨方）進行查詢，則可以為之建立一個普通索引。CREATEINDEXsup_idxON訂貨.訂購單(供貨方)30例10-15：如果經(jīng)常需要根據(jù)多個字段的條件進行查詢，也可以在多個字段上建立索引。

CREATEINDEXsup_emp_idxON訂貨.訂購單(供貨方,經(jīng)手人

DESC)

例10-16：在倉庫關(guān)系上規(guī)定一個城市只設(shè)立一個倉庫，為此可以在倉庫關(guān)系的城市屬性上建立一個唯一索引：CREATEUNIQUEINDEXcity_idxON倉儲.倉庫(城市)如果表中某城市已經(jīng)有兩個倉庫？注意：最好在創(chuàng)建任何非聚集索引之前創(chuàng)建聚集索引。

PRIMARYKEY自動創(chuàng)建聚集索引。UNIQUE約束自動創(chuàng)建唯一索引。為提高查詢速度建立普通索引。索引會降低更新性能。在視圖上建立索引對視圖的操作將會轉(zhuǎn)換為對表的操作，所以一般不需要在視圖上建立索引。視圖的某些列如果不是直接來自表、而是運算的結(jié)果，可以在這樣的列上建立索引來提高查詢速度。要建立索引的視圖需要使用如下短語綁定架構(gòu)：

WITHSCHEMABINGING在視圖上建立索引舉例P262優(yōu)化索引任何對數(shù)據(jù)庫的插入、更新和刪除操作數(shù)據(jù)庫管理系統(tǒng)都會自動維護索引，從而會產(chǎn)生大量碎片，導致應用程序響應緩慢。在SQLServer中可以通過sys.dm_db_index_physical_stats函數(shù)診斷碎片，然后通過ALTERINDEX命令重組或重新生成索引。36函數(shù)sys.dm_db_index_physical_stats的返回結(jié)果

列說明avg_fragmentation_in_percent邏輯碎片（索引中的無序頁）的百分比。fragment_count索引中的碎片（物理上連續(xù)的葉頁）數(shù)量。avg_fragment_size_in_pages索引中一個碎片的平均頁數(shù)。一般可以按照avg_fragmentation_in_percent的值決定如何進行優(yōu)化：當<5%時，不需要對索引進行優(yōu)化；當>5%且<=30%時，使用ALTERINDEXREORGANIZE重新組織索引；當>30%時，使用ALTERINDEXREBUILD重新生成索引。37獲取碎片信息P263例10-20重組或重新生成索引可用ALTERINDEX命令對索引進行重組或重新生成ALTERINDEX命令的基本格式是：ALTERINDEX<索引名>

ON<表或視圖>{REBUILD|DISABLE

|REORGANIZE}其中：REBUILD：將使用相同的列、索引類型、唯一性屬性和排序順序重新生成索引（包括被DISABLE禁用的索引）；REORGANIZE：將重新組織的索引的葉子節(jié)點；DISABLE：把索引標記為禁用，被禁用的索引DBMS將不對它進行維護；如果要重新啟用則使用ALTERINDEX…REBUILD重新生成索引。39例10-21：使用ALTERINDEX…REBUILD重新生成訂購單表上的sup_idx索引。ALTERINDEXsup_idxON訂貨.訂購單REBUILD40刪除索引刪除索引的命令是DROPINDEX，基本格式如下：DROPINDEX<索引名>ON<表或視圖>例10-22：刪除訂購單表上的sup_idx索引DROPINDEXsup_idxON訂貨.訂購單4110.2備份與恢復數(shù)據(jù)庫恢復概述故障類型備份類型日志的概念恢復模型備份和恢復策略備份操作恢復或還原42數(shù)據(jù)庫恢復概述數(shù)據(jù)庫安全性控制防范的是人，目的是拒絕非授權(quán)的用戶訪問數(shù)據(jù)庫，以保證數(shù)據(jù)庫數(shù)據(jù)的安全。另一類安全性問題是要預防各種非人為因素或人為因素的計算機故障。為了應付這些故障，多數(shù)情況下需要為數(shù)據(jù)庫制作備份，在故障排除后，再利用備份的數(shù)據(jù)進行恢復。事務的原子性、一致性和持久性均需要恢復技術(shù)的支持。43故障類型造成事務中斷的故障突然掉電引起的事務中斷；硬件故障引起的事務中斷；客戶應用程序出錯引起的事務中斷；系統(tǒng)程序故障引起的事務中斷。解決這類問題的方法顯然就是將數(shù)據(jù)庫恢復到修改之前的狀態(tài)，即撤消只執(zhí)行了一半的事務。存儲介質(zhì)故障解決這類故障的切實有效辦法就是備份，在修復或更換磁盤后再恢復。44備份類型雙機熱備份雙工備份磁盤鏡像冗余磁盤陣列數(shù)據(jù)庫備份技術(shù)45日志的概念備份是定期的、而不是實時的，所以利用備份并不能完全恢復數(shù)據(jù)庫，它只能將數(shù)據(jù)庫恢復到制作備份的那一時刻。日志則是對備份的補充，它可以看作是一個值班日記，它將記錄下所有對數(shù)據(jù)庫的更新操作。這樣就可以在備份完成時立刻刷新并啟用一個數(shù)據(jù)庫日志，數(shù)據(jù)庫日志是實時的，它將忠實地記錄下所有對數(shù)據(jù)庫的更新操作。為了保證日志的安全，應該將日志和主數(shù)據(jù)庫安排在不同的存儲設(shè)備上。46恢復模型簡單恢復模型簡單恢復允許將數(shù)據(jù)庫恢復到最新的備份，即使用簡單恢復模型可以將數(shù)據(jù)庫恢復到上次備份的即時點，而無法將數(shù)據(jù)庫恢復到故障點或特定的即時點。簡單恢復模型的數(shù)據(jù)庫只能做數(shù)據(jù)庫備份，不能做日志備份。完全恢復模型完全恢復允許將數(shù)據(jù)庫恢復到故障點狀態(tài)，即完全恢復模型使用數(shù)據(jù)庫備份和事務日志備份提供對介質(zhì)故障的完全防范。47設(shè)置恢復模型的命令ALTERDATABASE語句的RECOVERY子句48例10-23：將倉儲訂貨數(shù)據(jù)庫的恢復模型設(shè)置為完全恢復。ALTERDATABASE倉儲訂貨SETRECOVERYFULL備份的類型全備份增量備份事務日志備份文件和文件組備份50備份和恢復策略動態(tài)備份和靜態(tài)備份動態(tài)備份也稱作在線備份，即在做備份時不中斷數(shù)據(jù)庫的運行，不中斷數(shù)據(jù)庫上的應用程序和事務處理。靜態(tài)備份也稱作離線或脫機備份，這意味著在做備份時沒有任何數(shù)據(jù)庫事務在運行。51備份和恢復策略如果數(shù)據(jù)庫對每天的事務處理都至關(guān)重要，那么就必須經(jīng)常備份；對不變的歷史數(shù)據(jù)可以只備份一次，但要多復制幾個備份，以免備份介質(zhì)損壞而造成數(shù)據(jù)丟失；如果數(shù)據(jù)庫的使用頻率和更新頻率非常高，可以考慮每天做一次全備份，做幾次增量備份；相反，如果數(shù)據(jù)庫的更新頻率不太高，只需要每周、甚至每月做一次全備份。更多細節(jié)？P26852備份全數(shù)據(jù)庫備份增量備份事務日志備份文件或文件組備份系統(tǒng)數(shù)據(jù)庫的備份備份整個數(shù)據(jù)庫BACKUPDATABASEdatabase_name

TO{DISK|TAPE}='physical_backup_device_name'54例10-24：將倉儲訂貨數(shù)據(jù)庫備份到C:\dump\dumpfull.bakBACKUPDATABASE倉儲訂貨TODISK=‘C:\dump\dumpfull.bak’增量備份BACKUPDATABASEdatabase_name

TO{DISK|TAPE}='physical_backup_device_name'WITHDIFFERENTIAL增量備份的基準？上一次全備份。56例10-25：對倉儲訂貨數(shù)據(jù)庫做增量備份（備份到C:\dump\diff1.bak）BACKUPDATABASE倉儲訂貨

TODISK='C:\dump\diff1.bak'WITHDIFFERENTIAL事務日志備份BACKUPLOGdatabase_name

TO{DISK|TAPE}='physical_backup_device_name'每次日志備份的基準？是上一次備份。58例10-26：備份倉儲訂貨數(shù)據(jù)庫的日志（備份到C:\dump\dumplog.bak）BACKUPLOG倉儲訂貨

TODISK='C:\dump\dumplog.bak'備份小結(jié)全備份、增量備份、日志備份構(gòu)成了一個完整的數(shù)據(jù)庫備份方案，當數(shù)據(jù)庫遇到災難時，利用這些備份可以完全恢復數(shù)據(jù)庫。文件或文件組備份BACKUPDATABASEdatabase_name{FILE=logic_file_list|FILEGROUP=

filegroup_list}TO{DISK|TAPE}='physical_backup_device_name'[WITHDIFFERENTIAL]更靈活的備份解決方案61文件或文件組備份操作例10-27：完成對倉儲訂貨數(shù)據(jù)庫warehouse文件的備份：BACKUPDATABASE倉儲訂貨FILE='warehouse'TODISK='C:\dump\file_1.bak'例10-28：完成對倉儲訂貨數(shù)據(jù)庫文件組“倉庫”的備份：BACKUPDATABASE倉儲訂貨FILEGROUP='倉庫'TODISK='C:\dump\file_g.bak'62系統(tǒng)數(shù)據(jù)庫的備份數(shù)據(jù)庫備份不僅僅是要備份用戶數(shù)據(jù)庫，系統(tǒng)數(shù)據(jù)庫也需要備份，例如SQLServer中的master、model等系統(tǒng)數(shù)據(jù)庫。備份master等系統(tǒng)數(shù)據(jù)庫是系統(tǒng)管理員的職責。一般在執(zhí)行了更新系統(tǒng)表的命令之后（如CREATEDATABASE、ALTERDATABASE等命令都將更新系統(tǒng)表）都要備份master數(shù)據(jù)庫，所以必須經(jīng)常性地、定期地備份master數(shù)據(jù)庫。master數(shù)據(jù)庫只能進行全備份。63恢復或還原與備份類型相對應，恢復可以是：恢復整個數(shù)據(jù)庫恢復數(shù)據(jù)庫的部分內(nèi)容恢復特定的文件或文件組恢復事務可以將數(shù)據(jù)庫恢復到做備份的即時點、發(fā)生故障的即時點或特定的事務即時點。64發(fā)生災難后如何進行恢復？備份尾日志利用全備份進行恢復利用最后一次增量備份進行恢復利用備份的事務日志鏈完成恢復備份尾日志當數(shù)據(jù)庫發(fā)生災難時，要做的第一件事是備份事務日志，這是上次備份之后發(fā)生在數(shù)據(jù)庫中的更改操作，這時候的日志也稱作尾日志。66根據(jù)數(shù)據(jù)庫全備份進行恢復RESTOREDATABASEdatabase_nameFROM{DISK|TAPE}='physical_backup_device_name'[WITH[[,]{NORECOVERY|RECOVERY}][[,]REPLACE]]67根據(jù)增量備份進行恢復在簡單恢復模型和完全恢復模型中都可以選擇增量備份，如果存在增量備份，則一般需要進行相應的恢復操作。增量恢復數(shù)據(jù)庫的命令也是RESTOREDATABASE，但是在根據(jù)增量備份繼續(xù)恢復之前應該：已經(jīng)使用RESTOREDATABASE命令完成了全備份的恢復，同時指定了NORECOVERY子句在進行增量恢復時根據(jù)需要指定RECOVERY或NORECOVERY子句68根據(jù)事務日志進行恢復利用日志可以將數(shù)據(jù)庫恢復到最新的一致狀態(tài)或任意的事務點。首先恢復事務日志備份之前的數(shù)據(jù)庫備份或增量數(shù)據(jù)庫備份。如果有多個日志備份，則按先后順序進行恢復。69根據(jù)事務日志進行恢復RESTORELOGdatabase_nameFROM{DISK|TAPE}='physical_backup_device_name'[WITH[[,]{NORECOVERY|RECOVERY

}][[,]STOPAT=date_time|[,]STOPATMARK='mark_name'[AFTERdatetime]|[,]STOPBEFOREMARK='mark_name'[AFTERdatetime]]]70對倉儲訂貨數(shù)據(jù)庫進行了如下所示的備份，現(xiàn)在該數(shù)據(jù)庫崩潰，要求完全恢復數(shù)據(jù)庫。1.BACKUPLOG倉儲訂貨TODISK='C:\dump\raillog.bak'2.RESTOREDATABASE倉儲訂貨 FROMDISK='C:\dump\dumpfull.bak'

WITHNORECOVERY3.RESTOREDATABASE倉儲訂貨 FROMDISK='C:\dump\diff1.bak'

WITHNORECOVERY4.RESTORELOG倉儲訂貨 FROMDISK='C:\dump\dumplog.bak'

WITHNORECOVERY5.RESTORELOG倉儲訂貨 FROMDISK='C:\dump\raillog.bak'

WITHRECOVERY完全備份到dumpfull.bak增量備份到diff1.bak日志備份到dumplog.bak災難71根據(jù)文件或文件組備份進行恢復如果數(shù)據(jù)庫的某個文件損壞了，并且按文件或文件組做了備份，則可以考慮根據(jù)文件或文件組備份進行恢復。當使用文件或文件組備份進行恢復時，最后一個文件或文件組恢復操作完成后，必須將事務日志應用于數(shù)據(jù)庫文件，以便使之與數(shù)據(jù)庫的其余部分保持一致。如果被恢復的文件自上次備份后沒有做過任何修改操作，則不必應用事務日志。72根據(jù)文件或文件組備份進行恢復RESTOREDATABASEdatabase_name{FILE=

logical_file_name| FILEGROUP=

logical_filegroup_name}FROM{DISK|TAPE}='physical_backup_device_name'[WITH{NORECOVERY|RECOVERY}]73恢復系統(tǒng)數(shù)據(jù)庫備份系統(tǒng)數(shù)據(jù)庫與備份用戶數(shù)據(jù)庫的方式相同，除master數(shù)據(jù)庫之外其他系統(tǒng)數(shù)據(jù)庫的恢復也與恢復用戶數(shù)據(jù)庫類似。master數(shù)據(jù)庫是所有數(shù)據(jù)庫的主數(shù)據(jù)庫，也是管理所有數(shù)據(jù)庫的數(shù)據(jù)庫?；謴推渌麛?shù)據(jù)庫都是在SQLServer能夠正常運行的基礎(chǔ)上進行的，而master數(shù)據(jù)庫的損壞可能導致SQLserver根本不能運行，所以恢復