第4章 數(shù)據(jù)庫管理

1、第四章第四章 數(shù)據(jù)庫管理數(shù)據(jù)庫管理計(jì)算機(jī)系 胡駿4.1 數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)分為兩類： 邏輯存儲(chǔ)結(jié)構(gòu)：指數(shù)據(jù)庫的若干對(duì)象 物理存儲(chǔ)結(jié)構(gòu)：數(shù)據(jù)庫文件,以MDF,NDF,LDF結(jié)尾的文件4.1.1 數(shù)據(jù)庫文件數(shù)據(jù)庫文件1.主要數(shù)據(jù)文件(*.MDF) 用于存儲(chǔ)數(shù)據(jù)庫的啟動(dòng)信息和部分?jǐn)?shù)據(jù)或全部數(shù)據(jù)。每個(gè)數(shù)據(jù)庫只有一個(gè)主要文件。2.次要數(shù)據(jù)文件(*.NDF) 次要數(shù)據(jù)文件是主要數(shù)據(jù)文件的輔助文件，用于存儲(chǔ)主要數(shù)據(jù)文件沒有存儲(chǔ)的剩余數(shù)據(jù)和剩余對(duì)象。4.1.1 數(shù)據(jù)庫文件數(shù)據(jù)庫文件l次要數(shù)據(jù)文件存在的必要性？操作系統(tǒng)中磁盤分區(qū)的要求。 FAT32支持單個(gè)文件最大為4G NTFS支

2、持單個(gè)文件最大為64G EXT3 支持單個(gè)文件最大為4G 有些主要數(shù)據(jù)文件大小很有可能大于4G因此必須將剩余的數(shù)據(jù)放到次要文件當(dāng)中。4.1.1 數(shù)據(jù)庫文件數(shù)據(jù)庫文件l次要數(shù)據(jù)文件存在的必要性？ 數(shù)據(jù)處理效率的要求。 假設(shè)要對(duì)某個(gè)數(shù)據(jù)庫中的某個(gè)表進(jìn)行修改， 假設(shè)第一種方式速度為1S，第二種方式速度為1/2S。 處理時(shí)間1s處理時(shí)間1/2s4.1.1 數(shù)據(jù)庫文件數(shù)據(jù)庫文件3.事務(wù)日志文件(*.LDF) 每個(gè)數(shù)據(jù)庫至少有一個(gè)事務(wù)日志文件，它用于記錄數(shù)據(jù)庫的更新等情況。當(dāng)數(shù)據(jù)庫遭到破壞的時(shí)候，可以根據(jù)事務(wù)日志文件進(jìn)行恢復(fù)。4.1.2 數(shù)據(jù)庫文件組數(shù)據(jù)庫文件組l為了便于分配和管理SQL Server 2

3、000，將多個(gè)數(shù)據(jù)庫文件歸為一個(gè)組，并賦予一個(gè)組名，這就是數(shù)據(jù)庫文件組。l規(guī)則: 一個(gè)數(shù)據(jù)庫文件只能存于一個(gè)文件組 一個(gè)文件組也只能被一個(gè)數(shù)據(jù)庫使用 日志文件是獨(dú)立的，不能存入任何文件組4.1.2 數(shù)據(jù)庫文件組數(shù)據(jù)庫文件組l把文件歸入文件組實(shí)際上是提升系統(tǒng)性能的手段！ 下圖中建立兩個(gè)次要文件并把它們加入到文件組中。然后在文件組上創(chuàng)建一個(gè)表，此時(shí)通過文件組，這個(gè)表上的數(shù)據(jù)分配到兩個(gè)次要文件上，在對(duì)表中內(nèi)容進(jìn)行查詢的時(shí)候會(huì)在兩個(gè)次要文件中同時(shí)開始查詢，提高查詢性能。處理時(shí)間1/2s4.1.1 數(shù)據(jù)庫文件組數(shù)據(jù)庫文件組l數(shù)據(jù)庫文件組分為三種類型： 主要文件組、用戶自定義文件組，默認(rèn)文件組。默認(rèn)情況下

4、主要文件組就是默認(rèn)文件組。4.2 創(chuàng)建和管理數(shù)據(jù)庫創(chuàng)建和管理數(shù)據(jù)庫l4.2.1 創(chuàng)建數(shù)據(jù)庫 創(chuàng)建數(shù)據(jù)庫要做的事情： 命名 設(shè)計(jì)數(shù)據(jù)庫占用的存儲(chǔ)空間 存放數(shù)據(jù)庫文件的位置 創(chuàng)建數(shù)據(jù)庫有三種方法：使用企業(yè)管理器，T-SQL語言，以及向?qū)А?1 使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫l見錄像2 使用使用T-SQL語言創(chuàng)建數(shù)據(jù)庫語言創(chuàng)建數(shù)據(jù)庫CREATE DATABASE database_nameON PRIMARY ，n ， ，n LOG ON ，nFOR RESTORE :=（NAME=logical_file_name，F(xiàn)ILENAME=os_file_name，SIZE=size，

5、MAXSIZE=max_size|UNLIMITED，F(xiàn)ILEGROWTH=growth_increment ） ，n:=FILEGROUP filegroup_name ，n 參數(shù)說明如下： DATABASE_NAME：數(shù)據(jù)庫的名稱，最長為128個(gè)字符。 PRIMARY：該選項(xiàng)是一個(gè)關(guān)鍵字，指定主文件組中的文件。 LOG ON：指明事務(wù)日志文件的明確定義。 NAME：指定數(shù)據(jù)庫的邏輯名稱，這是在SQL Server系統(tǒng)中使用的名稱，是數(shù)據(jù)庫在SQL Server中的標(biāo)識(shí)符。 FILENAME：指定數(shù)據(jù)庫所在文件的操作系統(tǒng)文件名稱和路徑，該操作系統(tǒng)文件名和NAME的邏輯名稱一一對(duì)應(yīng)。 SIZE

6、：指定數(shù)據(jù)庫的初始容量大小。 MAXSIZE：指定操作系統(tǒng)文件可以增長到的最大尺寸。如果沒有指定，則文件可以不斷增長直到充滿磁盤。 FILEGROWTH：指定文件每次增加容量的大小，當(dāng)指定數(shù)據(jù)為0時(shí)，表示文件不增長。2 使用使用T-SQL語言創(chuàng)建數(shù)據(jù)庫語言創(chuàng)建數(shù)據(jù)庫CREATE DATABASE TSGL1ON PRIMARY(NAME=TSGL_DATA,FILENAME=C:DATATSGL.MDF,SIZE=20,FILEGROWTH=10%)LOG ON (NAME=TSGL1_LOG,FILENAME=C:DATATSGL.LDF,SIZE=1,MAXSIZE=5,FILEGROWT

7、H=1)練習(xí)練習(xí)l創(chuàng)建一個(gè)名為AAA的數(shù)據(jù)庫，主要文件和日志文件名稱自選，主要文件起始大小3M，增長為15%，最大值為50M；日志文件起始大小1M,增長為1M，最大值為10M。3 使用模板創(chuàng)建數(shù)據(jù)庫使用模板創(chuàng)建數(shù)據(jù)庫l見錄像4.2.2 查看數(shù)據(jù)庫查看數(shù)據(jù)庫4.2.2 查看數(shù)據(jù)庫查看數(shù)據(jù)庫4.2.3 修改數(shù)據(jù)庫修改數(shù)據(jù)庫1 使用企業(yè)管理器修改數(shù)據(jù)庫使用企業(yè)管理器修改數(shù)據(jù)庫1 使用企業(yè)管理器修改數(shù)據(jù)庫使用企業(yè)管理器修改數(shù)據(jù)庫1 使用企業(yè)管理器修改數(shù)據(jù)庫使用企業(yè)管理器修改數(shù)據(jù)庫1 使用企業(yè)管理器修改數(shù)據(jù)庫使用企業(yè)管理器修改數(shù)據(jù)庫2 使用使用T-SQL語句修改數(shù)據(jù)庫語句修改數(shù)據(jù)庫使用ALTER DAT

8、ABASE語句修改數(shù)據(jù)庫 ALTER 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

9、filegroup_property|name=new_filegroup_name 2 使用使用T-SQL語句修改數(shù)據(jù)庫語句修改數(shù)據(jù)庫l例：使用T-SQL語言添加一個(gè)含有一個(gè)數(shù)據(jù)文件的文件組到TSGL數(shù)據(jù)庫。2 使用使用T-SQL語句修改數(shù)據(jù)庫語句修改數(shù)據(jù)庫ALTER DATABASE TSGLAdd filegroup DATA1ALTER DATABASE TSGLAdd file(name=TSGL_DATA,FILENAME=路徑,SIZE=1,MAXSIZE=50,FILEGROWTH=1)TO FILEGROUP DATA14.2.4 刪除數(shù)據(jù)庫刪除數(shù)據(jù)庫l兩種方法： 1.在企業(yè)

10、管理器中，右擊要?jiǎng)h除的數(shù)據(jù)庫，從快捷菜單中選擇“刪除”。 2.T-SQL語言 DROP DATABASE DATABASE_NAME小技巧之附加數(shù)據(jù)庫小技巧之附加數(shù)據(jù)庫l同學(xué)們?cè)谑褂脭?shù)據(jù)庫的時(shí)候可能遇到這種問題： 自己在某臺(tái)電腦上好不容易輸入的數(shù)據(jù)，由于下課而帶不走，下次做實(shí)驗(yàn)的時(shí)候還是要再輸入一遍。為了節(jié)省大家的體力，在這里介紹一種附加數(shù)據(jù)庫的方法供大家使用。 具體內(nèi)容見錄像4.3 數(shù)據(jù)庫備份數(shù)據(jù)庫備份4.3.1概述 數(shù)據(jù)庫的備份還原為存儲(chǔ)在SQL Server數(shù)據(jù)庫中的關(guān)鍵數(shù)據(jù)提供重要的保護(hù)手段?？梢詮亩喾N故障中恢復(fù)數(shù)據(jù)庫。備份數(shù)據(jù)庫的前期工作備份數(shù)據(jù)庫的前期工作l根據(jù)客戶對(duì)數(shù)據(jù)庫的需求，

11、制定數(shù)據(jù)庫備份策略。1.分析可用性和恢復(fù)要求2.制定災(zāi)難恢復(fù)計(jì)劃3.選擇恢復(fù)模型分析可用性和恢復(fù)要求分析可用性和恢復(fù)要求1.您的可用性要求是什么？每天的什么時(shí)間數(shù)據(jù)庫必須處于聯(lián)機(jī)狀態(tài)？2.服務(wù)器故障時(shí)間將對(duì)公司造成多大的經(jīng)濟(jì)損失？3.一旦發(fā)生災(zāi)難，如因火災(zāi)丟失服務(wù)器，可接受的故障時(shí)間是多長？（一般來說48小時(shí)以內(nèi)） 4.重新創(chuàng)建丟失的數(shù)據(jù)的難易程度如何？制定災(zāi)難恢復(fù)計(jì)劃制定災(zāi)難恢復(fù)計(jì)劃1.硬件獲取2.相關(guān)數(shù)據(jù)庫管理人員的聯(lián)系3.安裝操作系統(tǒng)以及數(shù)據(jù)庫系統(tǒng)，還原MASTER數(shù)據(jù)庫。選擇恢復(fù)模型選擇恢復(fù)模型l恢復(fù)模型分為三種，分別為： 簡(jiǎn)單模型、完全模型、大容量日志模型步驟三：選擇恢復(fù)模型步驟三：

12、選擇恢復(fù)模型怎樣在怎樣在SQL企業(yè)管理其中設(shè)定還企業(yè)管理其中設(shè)定還原模型原模型怎樣在怎樣在SQL企業(yè)管理其中設(shè)定還企業(yè)管理其中設(shè)定還原模型原模型恢復(fù)模型的選擇恢復(fù)模型的選擇A.簡(jiǎn)單恢復(fù)(Simple Recovery) 所謂簡(jiǎn)單恢復(fù)就是指在進(jìn)行數(shù)據(jù)庫恢復(fù)時(shí)僅使用了數(shù)據(jù)庫備份或差異備份，而不涉及事務(wù)日志備份。 恢復(fù)模型的選擇恢復(fù)模型的選擇B.完全恢復(fù)(Full Recovery) 完全數(shù)據(jù)庫恢復(fù)模式是指通過使用數(shù)據(jù)庫備份和事務(wù)日志備份將數(shù)據(jù)庫恢復(fù)到發(fā)生失敗的時(shí)刻，因此幾乎不造成任何數(shù)據(jù)丟失，這成為對(duì)付因存儲(chǔ)介質(zhì)損壞而數(shù)據(jù)丟失的最佳方法。 恢復(fù)模型的選擇恢復(fù)模型的選擇C.大容量日志記錄恢復(fù) 大容量

13、日志記錄恢復(fù)在性能上要優(yōu)于簡(jiǎn)單恢復(fù)和完全恢復(fù)模式，它能盡最大努力減少批操作所需要的存儲(chǔ)空間。四種備份方式四種備份方式1.完全數(shù)據(jù)庫備份2.事務(wù)日志備份3.差異備份4.文件和文件組備份（完全）數(shù)據(jù)庫備份（完全）數(shù)據(jù)庫備份l實(shí)際上備份數(shù)據(jù)庫過程就是首先將事務(wù)日志寫到磁盤上，然后根據(jù)事務(wù)創(chuàng)建相同的數(shù)據(jù)庫和數(shù)據(jù)庫對(duì)象以及拷貝數(shù)據(jù)的過程。l備份速度慢，最好只在晚間進(jìn)行，備份時(shí)最好不要進(jìn)行其他操作，影響備份速度。備份過程中的任何操作都不會(huì)備份。l恢復(fù)速度也較慢（完全）數(shù)據(jù)庫備份（完全）數(shù)據(jù)庫備份創(chuàng)建數(shù)據(jù)庫并執(zhí)行創(chuàng)建數(shù)據(jù)庫并執(zhí)行完全數(shù)據(jù)庫備份完全數(shù)據(jù)庫備份完全數(shù)據(jù)庫備份完全數(shù)據(jù)庫備份 完全數(shù)據(jù)庫備份完全數(shù)據(jù)

14、庫備份 星期日星期日 星期一星期一 星期二星期二 數(shù)據(jù)數(shù)據(jù) 日志日志 數(shù)據(jù)數(shù)據(jù) 日志日志 數(shù)據(jù)數(shù)據(jù) 日志日志 事務(wù)日志備份事務(wù)日志備份 l事務(wù)日志備份是指對(duì)數(shù)據(jù)庫發(fā)生的事務(wù)進(jìn)行備份，包括從上次進(jìn)行事務(wù)日志備份、差異備份和數(shù)據(jù)庫完全備份之后，所有已經(jīng)完成的事務(wù)。l備份時(shí)間較短l恢復(fù)時(shí)間較長，需要完整數(shù)據(jù)庫備份。事務(wù)日志備份事務(wù)日志備份星期日星期日 星期一星期一 完全數(shù)據(jù)庫備份完全數(shù)據(jù)庫備份 完全數(shù)據(jù)庫備份完全數(shù)據(jù)庫備份 日志日志 日志日志 日志日志 LogLog數(shù)據(jù)數(shù)據(jù) 日志日志 數(shù)據(jù)數(shù)據(jù) 日志日志 中間的任何中間的任何時(shí)間點(diǎn)都可時(shí)間點(diǎn)都可以被恢復(fù)以被恢復(fù)差異備份差異備份l差異備份是指將最近一次

15、數(shù)據(jù)庫備份以來發(fā)生的數(shù)據(jù)變化備份起來，因此差異備份實(shí)際上是一種增量數(shù)據(jù)庫備份。l備份和恢復(fù)時(shí)間較短l缺點(diǎn)：無法提供失敗點(diǎn)的數(shù)據(jù)恢復(fù)。差異、數(shù)據(jù)庫、事務(wù)日志混合備差異、數(shù)據(jù)庫、事務(wù)日志混合備份方案份方案星期一星期一 星期二星期二 完全數(shù)據(jù)庫備份完全數(shù)據(jù)庫備份 差異備份差異備份 差異備份差異備份 日志日志數(shù)據(jù)數(shù)據(jù)日志日志日志日志日志日志日志日志日志日志日志日志 LogLogDataData 文件和文件組備份文件和文件組備份l可以備份和還原數(shù)據(jù)庫中的個(gè)別文件。這種備份可以只還原已損壞的文件，而不用還原數(shù)據(jù)庫的其余部分，從而加快了恢復(fù)速度。例如，如果數(shù)據(jù)庫由幾個(gè)位于不同磁盤上的文件組成，當(dāng)其中一個(gè)磁盤

16、發(fā)生故障時(shí)，只需還原發(fā)生了故障的磁盤上的文件。文件和文件組備份文件和文件組備份星期一星期一 星期二星期二 星期三星期三 星期四星期四 數(shù)據(jù)數(shù)據(jù)文件文件1 1數(shù)據(jù)數(shù)據(jù)文件文件3 3數(shù)據(jù)數(shù)據(jù)文件文件2 2完全數(shù)據(jù)庫備份完全數(shù)據(jù)庫備份 日志日志數(shù)據(jù)數(shù)據(jù)日志日志日志日志日志日志日志日志日志日志日志日志日志日志日志日志總結(jié)總結(jié)1：三種備份之間的關(guān)系：三種備份之間的關(guān)系l（完全）數(shù)據(jù)庫備份是差異備份的基礎(chǔ)，若沒有完全備份則進(jìn)行差異備份無效！l差異備份和事務(wù)日志備份的速度都很快，但是他們之間的主要差異是事務(wù)日志備份含有上次備份以來的所有修改，而差異備份只含有最后一次的修改。一天中應(yīng)用程序訪問數(shù)據(jù)庫的時(shí)間有多

17、長？如果存在一個(gè)可預(yù)測(cè)的非高峰時(shí)段，則建議您將完整數(shù)據(jù)庫備份安排在此時(shí)段。 更改和更新可能發(fā)生的頻率如何？ 如果更改經(jīng)常發(fā)生，請(qǐng)考慮下列事項(xiàng)：在簡(jiǎn)單恢復(fù)模式下，請(qǐng)考慮將差異備份安排在完整數(shù)據(jù)庫備份之間。差異備份只能捕獲自上次完整數(shù)據(jù)庫備份之后的更改。 在完整恢復(fù)模式下，應(yīng)安排經(jīng)常的日志備份。在完整備份之間安排差異備份可減少數(shù)據(jù)還原后需要還原的日志備份數(shù)，從而縮短還原時(shí)間。l可能只是更改數(shù)據(jù)庫的小部分內(nèi)容，還是需要更改數(shù)據(jù)庫的大部分內(nèi)容？ 對(duì)于更改集中于部分文件或文件組的大型數(shù)據(jù)庫，部分備份和/或文件備份非常有用?？偨Y(jié)總結(jié)2：成熟的備份方案：成熟的備份方案選擇大容量日志恢復(fù)模型1.首先有規(guī)律地進(jìn)

18、行數(shù)據(jù)庫備份，比如每晚進(jìn)行備份；2.其次以較小的時(shí)間間隔進(jìn)行差異備份，比如三個(gè)小時(shí)或四個(gè)小時(shí)；3.最后在相臨的兩次差異備份之間進(jìn)行事務(wù)日志備份，可以每二十或三十分鐘一次。真實(shí)的案例真實(shí)的案例l 公司備份方案如下：星期天早晨8：00： 完整數(shù)據(jù)庫備份每隔1小時(shí) ： 事務(wù)日志備份星期一早晨8：00： 差異備份每隔1小時(shí) ： 事務(wù)日志備份星期二早晨8：00： 差異備份每隔1小時(shí) ： 事務(wù)日志備份星期天早晨8：00： 完整數(shù)據(jù)庫備份4.3.2 創(chuàng)建備份設(shè)備創(chuàng)建備份設(shè)備1.利用企業(yè)管理創(chuàng)建（見錄像）2.利用T-SQL語言創(chuàng)建使用使用T-SQL語言創(chuàng)建備份設(shè)備語言創(chuàng)建備份設(shè)備 SQL Server 中，可

19、以使用存儲(chǔ)過程sp_addumpdevice語句創(chuàng)建備份設(shè)備，其語法形式如下： sp_addumpdevice device_type ,logical_name,physical_name 其中，device_type表示設(shè)備類型，其值可為disk和tape。 logical_name表示設(shè)備的邏輯名稱。physical_name表示設(shè)備的實(shí)際名稱。使用使用T-SQL語言創(chuàng)建備份設(shè)備語言創(chuàng)建備份設(shè)備 在磁盤上創(chuàng)建了一個(gè)備份設(shè)備。 EXEC sp_addumpdevice disk, PUBS數(shù)據(jù)庫備份, C: PUBS數(shù)據(jù)庫備份.bak 練習(xí)練習(xí)l創(chuàng)建一個(gè)名稱為AAA的備份設(shè)備4.3.3 備

20、份數(shù)據(jù)庫的操作備份數(shù)據(jù)庫的操作1.在企業(yè)管理器中備份 錄像2.利用T-SQL語言備份利用利用T-SQL語言備份數(shù)據(jù)庫語言備份數(shù)據(jù)庫1.(完全）數(shù)據(jù)庫備份 BACKUP DATABASE database_nameTO ,.n 2.差異備份 BACKUP DATABASE database_nameTO ,.n WITH DIFFERENTIAL 利用利用T-SQL語言備份數(shù)據(jù)庫語言備份數(shù)據(jù)庫3.事務(wù)日志備份 BACKUP LOG database_name TO ,.n 4.文件和文件組備份 BACKUP DATABASE database_name ,.n TO ,.n 例子：例子：1.完全備份：Backup