第2章 數(shù)據(jù)庫的建立及維護(hù)

第二章管理數(shù)據(jù)庫主講教師：衛(wèi)琳2.1數(shù)據(jù)庫組成mastermodeltempdbmsdbAdventureWorksAdventureWorksDW系統(tǒng)數(shù)據(jù)庫用戶數(shù)據(jù)庫User1studentSQLServer系統(tǒng)數(shù)據(jù)庫存儲SQLSERVER的系統(tǒng)信息，它們是管理SQLSERVER的依據(jù)，安裝SQLServer時，將創(chuàng)建4個系統(tǒng)數(shù)據(jù)庫Master包含了SQLServer諸如登錄賬號、系統(tǒng)配置、數(shù)據(jù)庫位置及數(shù)據(jù)庫錯誤信息等，用于控制用戶數(shù)據(jù)庫和SQLServer的運(yùn)行。Model為新創(chuàng)建的數(shù)據(jù)庫提供模板。Msdb為SQLServerAgent調(diào)度信息和作業(yè)記錄提供存儲空間。Tempdb為臨時表和臨時存儲過程提供存儲空間，所有與系統(tǒng)連接的用戶的臨時表和臨時存儲過程都存儲于該數(shù)據(jù)庫中。2.1.1系統(tǒng)數(shù)據(jù)庫2.1.2用戶數(shù)據(jù)庫

用戶數(shù)據(jù)庫與系統(tǒng)數(shù)據(jù)庫在結(jié)構(gòu)上是一樣的。是用戶根據(jù)自己的管理需求而創(chuàng)建的數(shù)據(jù)庫，便于自己管理相應(yīng)的數(shù)據(jù)。例如：圖書館可以針對圖書的管理創(chuàng)建圖書管理數(shù)據(jù)庫。大型超市可以針對貨物創(chuàng)建超市管理數(shù)據(jù)庫2.1.3示例數(shù)據(jù)庫

示例數(shù)據(jù)庫是系統(tǒng)為了讓用戶學(xué)習(xí)和理解SQLServer而設(shè)計的。默認(rèn)情況下，SQLServer2008版本跳會再安裝示例數(shù)據(jù)庫。但可以根據(jù)自己需要附加使用這些示例數(shù)據(jù)庫。

數(shù)據(jù)庫文件和事務(wù)日志文件2.1.4數(shù)據(jù)庫文件及其文件組頁：8KB表、索引數(shù)據(jù)數(shù)據(jù)庫數(shù)據(jù)文件

.mdf或.ndf日志文件

.ldf最多=8060字節(jié)SQLserver的文件使用一組操作系統(tǒng)文件來存儲數(shù)據(jù)庫的各種邏輯成分，包括以下三類文件。（1）主數(shù)據(jù)文件。是數(shù)據(jù)庫的關(guān)鍵文件，包含了數(shù)據(jù)庫的啟動信息，并且存儲數(shù)據(jù)。每個數(shù)據(jù)庫必需有且僅能有一個主文件，默認(rèn)擴(kuò)展名為.MDF（2）輔助數(shù)據(jù)文件。用于存儲未包括在主文件內(nèi)的其他數(shù)據(jù)。默認(rèn)擴(kuò)展名為.NDF。輔助文件是可選的，根據(jù)具體情況,可以創(chuàng)建多個輔助文件，也可以不用輔助文件。一般當(dāng)數(shù)據(jù)庫很大時，有可能需要創(chuàng)建多個輔助文件；而數(shù)據(jù)庫較小時，則只要創(chuàng)建主文件文件不需要輔助文件。（3）日志文件。日志文件用于保存恢復(fù)數(shù)據(jù)庫所需的事務(wù)日志信息。每個數(shù)據(jù)庫至少有一個日志文件，也可能有多個。日志文件的擴(kuò)展名為.LDF.

日志文件的存儲與數(shù)據(jù)文件不同，它包含一系列記錄，這些記錄的存儲不以頁為存儲單位。創(chuàng)建一個數(shù)據(jù)庫后，該數(shù)據(jù)庫中至少包含一個主數(shù)據(jù)文件和日志文件。這些文件是操作系統(tǒng)文件名，它們不是由用戶直接使用的，則是由系統(tǒng)使用的，因此不同于數(shù)據(jù)庫的邏輯名。4)文件組：允許將多個文件歸納為一組稱文件組。Data1.mdf,data2.ndf,data3.ndf數(shù)據(jù)文件分別創(chuàng)建在3個物理磁盤上，組成一組。創(chuàng)建表時，指定一個表在文件組中。此表數(shù)據(jù)分布在3個物理磁盤上，對表查詢，可并行操作，提高查詢效率。說明：一個文件或一個文件組只能被一個數(shù)據(jù)庫使用。一個文件只能隸屬于一個文件組。數(shù)據(jù)庫的數(shù)據(jù)信息和日志信息不能放在同一個文件或文件組中。日志文件不能隸屬于任何一個文件組。文件組有兩類：主文件組：包含主數(shù)據(jù)文件和任何沒有明確指派給其他文件組的其他文件。用戶定義文件組:T_SQL語句中用于創(chuàng)建和修改數(shù)據(jù)庫的語句分別是createdatabase和alterdatabase,這兩語句都可以用filegroup關(guān)鍵字指定文件組。用戶定義文件組就是指使用這兩個語句創(chuàng)建或修改數(shù)據(jù)庫時指定的文件組。

每個數(shù)據(jù)庫中都有一個文件組作為默認(rèn)文件組運(yùn)行。若SQLSERVER創(chuàng)建表或索引時沒有為其指定文件組，那么將從默認(rèn)文件組中進(jìn)行存儲頁分配、查詢等操作?？梢灾付J(rèn)文件組，如果沒有指定默認(rèn)文件組，則主文件組是默認(rèn)文件組。2.1.5數(shù)據(jù)庫對象1.表2.視圖3.存儲過程2.2.1使用SSMS圖形界面創(chuàng)建數(shù)據(jù)庫1.創(chuàng)建數(shù)據(jù)庫例1：創(chuàng)建數(shù)據(jù)庫xscj,初始大小為5MB,最大長度為50MB，數(shù)據(jù)庫自動增長，增長方式是按10%比例增長；日志文件初始為2MB,最大可增長到5MB（為不限制）,按1MB增長（默認(rèn)是按10%比例增長）2.2數(shù)據(jù)庫的維護(hù)2.2.2使用T-SQL命令創(chuàng)建數(shù)據(jù)庫例1：創(chuàng)建數(shù)據(jù)庫xscj1,初始大小為5MB,最大長度為50MB，數(shù)據(jù)庫自動增長，增長方式是按10%比例增長；日志文件初始為2MB,最大可增長到5MB（為不限制）,按1MB增長（默認(rèn)是按10%比例增長）；所有者是Adimistrator.1.創(chuàng)建數(shù)據(jù)庫createdatabasexscj1 on (name='xscj1_data', filename='e:\sql\xscj1_data.mdf', size=5MB, maxsize=50Mb, filegrowth=10% ) logon (name='xscj1_log', filename='e:\sql\xscj1_log.ldf', size=2mb, maxsize=5MB, filegrowth=1MB )gocreatedatabasetest1 on (name='test1', filename='e:\sql\test1.mdf' )go例2創(chuàng)建TEST1數(shù)據(jù)庫說明：TEST1數(shù)據(jù)庫只包含一個主數(shù)據(jù)文件和一個主日志文件，它們均采用系統(tǒng)默認(rèn)文件名，其大小分別為model數(shù)據(jù)庫中主數(shù)據(jù)文件和日志文件的大小。例3創(chuàng)建一個名為TEST2的數(shù)據(jù)庫，它有2個數(shù)據(jù)文件，其中主數(shù)據(jù)文件為100MB，最大大小為200MB，按20MB增長；1個輔助數(shù)據(jù)文件為20MB，最大大小不限，按10%增長；有2個日志文件，大小均為50MB，最大大小均為100MB,按10MB增長。createdatabasetest2 on (name='test2_data1', filename='e:\sql\test2_data1.mdf', size=100mb, maxsize=200mb, filegrowth=20mb ), (name='test_data2', filename='e:\sql\test2_data2.ndf', size=20mb, maxsize=unlimited, filegrowth=10% ) logon (name='test2_log1', filename='e:\sql\test2_log1.ldf', size=50mb, maxsize=100mb, filegrowth=10mb ), (name='test2_log2', filename='e:\sql\test2_log2.ldf', size=50mb, maxsize=100mb, filegrowth=10mb )go例4：創(chuàng)建一個有3個文件組的數(shù)據(jù)庫test3。主文件組包括文件test3_data1,test3_data2,文件初始大小均為20MB，最大為60MB，按5MB增長；第2個文件組名為testgoup1,包括文件test3_data3,test_data4,文件初始大小為10MB,最大為30MB，按10%；第3個文件組名為testgroup3，包括文件test3_data5,文件初始大小為10MB，最大為50MB，按15%增長。該數(shù)據(jù)庫只有一個日志文件，初始大小為20MB，最大為50MB，按5MB增長。createdatabasetest3onprimary (name='test3_data1', filename='e:\學(xué)號姓名\test3_data1.mdf', size=20mb, maxsize=60mb, filegrowth=5mb ), (name='test3_data2', filename='e:\學(xué)號姓名\test3_data2.mdf', size=20mb, maxsize=60mb, filegrowth=5mb ),filegrouptest3group1 (name='test3_data3', filename='e:\學(xué)號姓名\test3_data3.mdf', size=10mb, maxsize=30mb, filegrowth=10% ), (name='test3_data4', filename='e:\學(xué)號姓名\test3_data4.mdf', size=10mb, maxsize=30mb, filegrowth=10% ),filegrouptest3group2 (name='test3_data5', filename='e:\學(xué)號姓名\test3_data5.mdf', size=10mb, maxsize=50mb, filegrowth=15% )logon (name='test3_log', filename='e:\學(xué)號姓名\test3_log.ldf', size=20mb, maxsize=50mb, filegrowth=5mb )2.2.3數(shù)據(jù)庫的查看和修改1.使用SSMS圖形界面查看和修改1).改變數(shù)據(jù)文件的大小和增長方式例5.將xscj數(shù)據(jù)庫的主數(shù)據(jù)文件xscj.mdf的最大大小由50MB修改為不限制.2).增加數(shù)據(jù)文件例6在xscj數(shù)據(jù)庫中增加數(shù)據(jù)文件xscjbak,其屬性均取系統(tǒng)默認(rèn)值.

當(dāng)原有數(shù)據(jù)庫的存儲空間不夠用時，除了可以采用擴(kuò)大原有數(shù)據(jù)文件的存儲量的方法之外，還可以增加新的數(shù)據(jù)文件；或者從系統(tǒng)管理的需求出發(fā)，采用多個數(shù)據(jù)文件來存儲數(shù)據(jù)，以免數(shù)據(jù)文件過大，此時，也會用到向數(shù)據(jù)庫中增加數(shù)據(jù)文件的操作。增加的數(shù)據(jù)文件是輔助文件。2.2.3數(shù)據(jù)庫的查看和修改3).刪除數(shù)據(jù)文件例7將xscj數(shù)據(jù)庫中剛增加的輔助xscjbak刪除。當(dāng)數(shù)據(jù)庫中的某些數(shù)據(jù)文件不再需要時，應(yīng)及時刪除，在SQL中只能刪除輔助數(shù)據(jù)文件，而不能刪除主數(shù)據(jù)文件。因?yàn)樵谥鲾?shù)據(jù)文件中存放著數(shù)據(jù)庫的啟動信息，刪除后，數(shù)據(jù)庫將無法啟動。2.2.3數(shù)據(jù)庫的查看和修改4).增加或刪除文件組例8在xscj數(shù)據(jù)庫中增加一個名為Fgroup的文件組.2.2.3數(shù)據(jù)庫的查看和修改例9將剛才新增的Fgroup文件組刪除。說明：不能刪除主文件組(PRIMARY)?？梢詣h除用戶定義的文件組。例10：設(shè)已經(jīng)創(chuàng)建了數(shù)據(jù)庫xscj,它只有一個主數(shù)據(jù)文件，其邏輯文件名為xscj_data,物理文件名為e:\sql\xscj_data.mdf,大小為5MB，最大大小為50MB，按10%增長；有一個日志文件，邏輯名為xscj_log,物理名為e:\sql\xscj_log.ldf,大小為2MB,最大大小為5MB,每次增長1MB.修改數(shù)據(jù)庫xscj現(xiàn)有數(shù)據(jù)文件的屬性，將主數(shù)據(jù)文件的最大大小修改為不限制，增長方式修改為每次5MB增長。2.修改數(shù)據(jù)庫(使用T_SQL命令)createdatabasexscj on (name='xscj_data', filename='e:\sql\xscj_data.mdf', size=5MB, maxsize=50MB, filegrowth=10% ) logon (name='xscj_log', filename='e:\sql\xscj_log.ldf', size=2MB, maxsize=5MB, filegrowth=1MB )go alterdatabasexscj modifyfile (name='xscj_data', maxsize=unlimited )goalterdatabasexscj modifyfile (name='xscj_data', filegrowth=5MB )go例11：修改數(shù)據(jù)庫xscj現(xiàn)在數(shù)據(jù)文件的屬性，將主數(shù)據(jù)文件的最大大小修改為不限制，增長方式修改為按每次5MB增長。說明：AlterdatabaseAlter

database語句一次只能修改數(shù)據(jù)文件的一個屬性，若修改主數(shù)據(jù)文件的兩個屬性，需執(zhí)行兩次alterdatabase命令例12：先為數(shù)據(jù)庫xscj增加數(shù)據(jù)文件xscjbak,初始大小為10MB，最大為50MB，增長方式為5%。然后刪除xscjbak.alterdatabasexscj addfile (name='xscjbak', filename='e:\sql\xscjbak.ndf', size=10MB, maxsize=50MB, filegrowth=5% )Goalterdatabasexscj removefilexscjbakgo例13：為數(shù)據(jù)庫xscj添加文件組Fgroup,并為文件組添加兩個大小均為10MB,最大大小為30MB，增長方式為5MB的數(shù)據(jù)文件。alterdatabasexscj addfilegroupFgroupgoalterdatabasexscj addfile (name='xscj_data2', filename='e:\sql\xscj_data2.ndf', size=10MB, maxsize=30MB, filegrowth=5MB ), (name='xscj_data3', filename='e:\sql\xscj_data3.ndf', size=10MB, maxsize=30MB, filegrowth=5MB ) tofilegroupFgroupgoalterdatabasexscj removefilexscj_data2goalterdatabasexscj removefilexscj_data3goalterdatabasexscj removefilegroupFgroupGo說明：使用T_SQL命令刪除文件組時必須為空，使用菜單可一同刪除。例14：從數(shù)據(jù)庫中刪除文件組，將xscj數(shù)據(jù)庫中的文件組Fgroup刪除，同時刪除其中的數(shù)據(jù)文件。例15：為數(shù)據(jù)庫添加一個日志文件，初始大小5MB，最大大小為10MB，文件按1MB方式增長。alterdatabasexscj addlogfile (name=xscj_log2, filename='e:\sql\xscj_log2.ldf', size=5MB, maxsize=10MB, filegrowth=1MB )go

例16：從數(shù)據(jù)庫xscj中刪除一個xscj_Log2日志文件，注意不能刪除主日志文件alterdatabasexscj removefilexscj_log2go

例17：將數(shù)據(jù)庫xscj中更名為just_test,注意進(jìn)行此操作時保證該數(shù)據(jù)庫不能被其他任何用戶使用。alterdatabasexscj modifyname=just_testgo

1.使用SSMS圖形界面例18刪除數(shù)據(jù)庫xscj切記：當(dāng)前數(shù)據(jù)庫不能刪除2.使用T-SQL語句例19Dropdatabasexscj2.2.4.刪除數(shù)據(jù)庫1.分離數(shù)據(jù)庫可以將數(shù)據(jù)庫從SQLserver實(shí)例中刪除，同時確保數(shù)據(jù)庫在其數(shù)據(jù)文件和事務(wù)日志文件中保持不變。除了系統(tǒng)數(shù)據(jù)庫外，其余的數(shù)據(jù)庫都可以從服務(wù)器的管理中分離出來。分離數(shù)據(jù)庫不是刪除數(shù)據(jù)庫，只是從服務(wù)器中分離出來，保證了數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件完整無損。2.2.5數(shù)據(jù)庫操作分離注意事項：數(shù)據(jù)庫存在數(shù)據(jù)庫快照時不能分離，在分離前，必須刪除所有快照。數(shù)據(jù)庫正在被鏡像時，不能被分離分離數(shù)據(jù)庫列表中各選項功能：刪除鏈接：表示是否斷開與指定數(shù)據(jù)庫的連接。更新統(tǒng)計信息：表示在分離數(shù)據(jù)庫之前是否更新過時的優(yōu)化信息。保留全文目錄：表示是否與數(shù)據(jù)庫相關(guān)聯(lián)的所有全文目錄，以用于全文索引。2.附加數(shù)據(jù)庫

將分離的數(shù)據(jù)庫重新附加服務(wù)器中，但在附加數(shù)據(jù)庫時必須指定主數(shù)據(jù)文件（MDF）的名稱和物理位置。示例：usemastergosp_detach_db'abc'createdatabaseabcon (filename='C:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\abc.mdf')forattach說明：路徑被寫入一行上usemastergosp_detach_db'abc‘createdatabaseabcon (filename='C:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\abc.mdf')forattach2.附加數(shù)據(jù)庫

將分離的數(shù)據(jù)庫重新附加服務(wù)器中，但在附加數(shù)據(jù)庫時必須指定主數(shù)據(jù)文件（MDF）的名稱和物理位置。3.脫機(jī)與聯(lián)機(jī)數(shù)據(jù)庫如果需要暫時關(guān)閉某個數(shù)據(jù)庫的服務(wù)，用戶可以通過選擇脫機(jī)方式來實(shí)現(xiàn)，脫機(jī)后在需要的時候可以暫時關(guān)閉數(shù)據(jù)庫。1.快照復(fù)制數(shù)據(jù)庫快照是一個只讀、靜態(tài)數(shù)據(jù)庫視圖，它是數(shù)據(jù)庫恢復(fù)到特定時間點(diǎn)的有效途徑。一個數(shù)據(jù)庫可能有多個數(shù)據(jù)庫快照。數(shù)據(jù)庫快照一直保持和源數(shù)據(jù)庫快照被創(chuàng)建的時刻一致。通過快照首先，可將數(shù)據(jù)庫恢復(fù)到創(chuàng)建快照時刻，比其他方法速度快，占用系統(tǒng)資源少。其次，快照可作為用戶保留一份可供讀取的歷史數(shù)據(jù)，如歷年的客戶訂單信息。第三，因?yàn)槭侵蛔x的，可替代源數(shù)據(jù)庫實(shí)現(xiàn)某些只需查詢而不需寫入服務(wù)。2.2.6復(fù)制和移動數(shù)據(jù)庫

數(shù)據(jù)庫快照在創(chuàng)建初始是一個空的數(shù)據(jù)庫殼，并未包含源數(shù)據(jù)庫中的數(shù)據(jù)，但保包含了一個指向源數(shù)據(jù)庫頁面的指針。

如果此時，要從空的快照數(shù)據(jù)庫讀取數(shù)據(jù)，SQLServer會從源數(shù)據(jù)庫中讀取數(shù)據(jù)。只有當(dāng)源數(shù)據(jù)庫發(fā)生數(shù)據(jù)變化時，如需要將數(shù)據(jù)寫到數(shù)據(jù)頁時，SQLServer才會執(zhí)行復(fù)制。如：“頁面A”復(fù)制到快照數(shù)據(jù)庫文件對應(yīng)位置。源數(shù)據(jù)庫中未發(fā)生變化的數(shù)據(jù)頁面不會被復(fù)制到快照數(shù)據(jù)庫文件中。這種模式在快照中稱“copy_on_write”。即先將數(shù)據(jù)頁面復(fù)制到快照文件中，然后再將更新數(shù)據(jù)寫入以源數(shù)據(jù)庫文件的數(shù)據(jù)頁中。由于源數(shù)據(jù)庫的更改可能只是其中的幾頁，所以復(fù)制到快照數(shù)據(jù)庫文件的也只是對應(yīng)的頁，而源數(shù)據(jù)庫中其他未修改的頁不會被復(fù)制。因此，快照數(shù)據(jù)庫數(shù)據(jù)文件比源數(shù)據(jù)庫的數(shù)據(jù)文件小很多，稱‘稀疏文件’。源數(shù)據(jù)庫快照數(shù)據(jù)庫頁面頁面頁面A頁面頁面頁面………頁面頁面頁面A’頁面頁面頁面………頁面A稀疏文件數(shù)據(jù)文件

稀疏文件會因?yàn)樵磾?shù)據(jù)庫中復(fù)制數(shù)據(jù)增加而擴(kuò)大。2008版會以每次64KB（即8個頁）大小為稀疏文件分配空間，這種機(jī)制可以有效地提高空間使用效益，避免相同數(shù)據(jù)存儲兩份。但有時會增加很快。如源數(shù)據(jù)庫重建索引，因索引會改變源數(shù)據(jù)庫中的數(shù)據(jù)頁很多，造成很多數(shù)據(jù)頁復(fù)制到快照中。快照復(fù)制注意事項：如果源數(shù)據(jù)庫中有多個數(shù)據(jù)文件，需在文件列表中，列出這些文件在快照中對應(yīng)的邏輯名稱(與源數(shù)據(jù)文件可以不同名)，物理名稱(與源數(shù)據(jù)文件可以不同名)及路徑?？煺罩挥袛?shù)據(jù)文件，沒有事務(wù)日志文件。原因是快照是只讀的，而要記錄寫入操作。因些不需要事務(wù)日志文件?？煺瘴募仨毥⒃贜TFS盤區(qū)，否則無法創(chuàng)建’稀疏文件而失敗?？煺諗?shù)據(jù)庫與源數(shù)據(jù)庫有關(guān)，必須位于同一服務(wù)器實(shí)例上。一個源數(shù)據(jù)庫可以建立多個快照數(shù)據(jù)庫，反映不同時間點(diǎn)狀態(tài)，若源數(shù)據(jù)庫不能使用，所有快照數(shù)據(jù)庫將不能使用。快照不需要時，需要手工刪除。不能對master,temdb,model系統(tǒng)數(shù)據(jù)庫創(chuàng)建快照。createdatabasetest_data_8_10on (name=test, filename='E:\db\test_data_8_10.mdf' ) assnapshotoftestGo查看快照方法：對象資源管理器T-SQL命令SSMS不支持創(chuàng)建快照，只能使用T-sql命令（企業(yè)版），為快照命名時，建議名稱中含有數(shù)據(jù)庫名及快照創(chuàng)建時間。createdatabasetest_2011820on (name='test', filename='e:\test\test_2011820.mdf'), (name='sale2009', filename='e:\test\sale2009_2011820.ndf'), (name='sale2010', filename='e:\test\sale2010_2011820.ndf'), (name='sale2011', filename='e:\test\sale2011_2011820.mdf'), (name='sale2012', filename='e:\test\sale2012_2011820.ndf'), (name='sale2012_2', filename='e:\test\sale2012_2_2011820.ndf'), (name='sale2013', filename='e:\test\sale2013_2011820.ndf')assnapshotoftestrestore