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

用戶數據庫與系統數據庫在結構上是一樣的。是用戶根據自己的管理需求而創建的數據庫，便于自己管理相應的數據。例如：圖書館可以針對圖書的管理創建圖書管理數據庫。大型超市可以針對貨物創建超市管理數據庫2.1.3示例數據庫

示例數據庫是系統為了讓用戶學習和理解SQLServer而設計的。默認情況下，SQLServer2008版本跳會再安裝示例數據庫。但可以根據自己需要附加使用這些示例數據庫。

數據庫文件和事務日志文件2.1.4數據庫文件及其文件組頁：8KB表、索引數據數據庫數據文件

.mdf或.ndf日志文件

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

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

每個數據庫中都有一個文件組作為默認文件組運行。若SQLSERVER創建表或索引時沒有為其指定文件組，那么將從默認文件組中進行存儲頁分配、查詢等操作。可以指定默認文件組，如果沒有指定默認文件組，則主文件組是默認文件組。2.1.5數據庫對象1.表2.視圖3.存儲過程2.2.1使用SSMS圖形界面創建數據庫1.創建數據庫例1：創建數據庫xscj,初始大小為5MB,最大長度為50MB，數據庫自動增長，增長方式是按10%比例增長；日志文件初始為2MB,最大可增長到5MB（為不限制）,按1MB增長（默認是按10%比例增長）2.2數據庫的維護2.2.2使用T-SQL命令創建數據庫例1：創建數據庫xscj1,初始大小為5MB,最大長度為50MB，數據庫自動增長，增長方式是按10%比例增長；日志文件初始為2MB,最大可增長到5MB（為不限制）,按1MB增長（默認是按10%比例增長）；所有者是Adimistrator.1.創建數據庫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創建TEST1數據庫說明：TEST1數據庫只包含一個主數據文件和一個主日志文件，它們均采用系統默認文件名，其大小分別為model數據庫中主數據文件和日志文件的大小。例3創建一個名為TEST2的數據庫，它有2個數據文件，其中主數據文件為100MB，最大大小為200MB，按20MB增長；1個輔助數據文件為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：創建一個有3個文件組的數據庫test3。主文件組包括文件test3_data1,test3_data2,文件初始大小均為20MB，最大為60MB，按5MB增長；第2個文件組名為testgoup1,包括文件test3_data3,test_data4,文件初始大小為10MB,最大為30MB，按10%；第3個文件組名為testgroup3，包括文件test3_data5,文件初始大小為10MB，最大為50MB，按15%增長。該數據庫只有一個日志文件，初始大小為20MB，最大為50MB，按5MB增長。createdatabasetest3onprimary (name='test3_data1', filename='e:\學號姓名\test3_data1.mdf', size=20mb, maxsize=60mb, filegrowth=5mb ), (name='test3_data2', filename='e:\學號姓名\test3_data2.mdf', size=20mb, maxsize=60mb, filegrowth=5mb ),filegrouptest3group1 (name='test3_data3', filename='e:\學號姓名\test3_data3.mdf', size=10mb, maxsize=30mb, filegrowth=10% ), (name='test3_data4', filename='e:\學號姓名\test3_data4.mdf', size=10mb, maxsize=30mb, filegrowth=10% ),filegrouptest3group2 (name='test3_data5', filename='e:\學號姓名\test3_data5.mdf', size=10mb, maxsize=50mb, filegrowth=15% )logon (name='test3_log', filename='e:\學號姓名\test3_log.ldf', size=20mb, maxsize=50mb, filegrowth=5mb )2.2.3數據庫的查看和修改1.使用SSMS圖形界面查看和修改1).改變數據文件的大小和增長方式例5.將xscj數據庫的主數據文件xscj.mdf的最大大小由50MB修改為不限制.2).增加數據文件例6在xscj數據庫中增加數據文件xscjbak,其屬性均取系統默認值.

當原有數據庫的存儲空間不夠用時，除了可以采用擴大原有數據文件的存儲量的方法之外，還可以增加新的數據文件；或者從系統管理的需求出發，采用多個數據文件來存儲數據，以免數據文件過大，此時，也會用到向數據庫中增加數據文件的操作。增加的數據文件是輔助文件。2.2.3數據庫的查看和修改3).刪除數據文件例7將xscj數據庫中剛增加的輔助xscjbak刪除。當數據庫中的某些數據文件不再需要時，應及時刪除，在SQL中只能刪除輔助數據文件，而不能刪除主數據文件。因為在主數據文件中存放著數據庫的啟動信息，刪除后，數據庫將無法啟動。2.2.3數據庫的查看和修改4).增加或刪除文件組例8在xscj數據庫中增加一個名為Fgroup的文件組.2.2.3數據庫的查看和修改例9將剛才新增的Fgroup文件組刪除。說明：不能刪除主文件組(PRIMARY)。可以刪除用戶定義的文件組。例10：設已經創建了數據庫xscj,它只有一個主數據文件，其邏輯文件名為xscj_data,物理文件名為e:\sql\xscj_data.mdf,大小為5MB，最大大小為50MB，按10%增長；有一個日志文件，邏輯名為xscj_log,物理名為e:\sql\xscj_log.ldf,大小為2MB,最大大小為5MB,每次增長1MB.修改數據庫xscj現有數據文件的屬性，將主數據文件的最大大小修改為不限制，增長方式修改為每次5MB增長。2.修改數據庫(使用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：修改數據庫xscj現在數據文件的屬性，將主數據文件的最大大小修改為不限制，增長方式修改為按每次5MB增長。說明：AlterdatabaseAlter

database語句一次只能修改數據文件的一個屬性，若修改主數據文件的兩個屬性，需執行兩次alterdatabase命令例12：先為數據庫xscj增加數據文件xscjbak,初始大小為10MB，最大為50MB，增長方式為5%。然后刪除xscjbak.alterdatabasexscj addfile (name='xscjbak', filename='e:\sql\xscjbak.ndf', size=10MB, maxsize=50MB, filegrowth=5% )Goalterdatabasexscj removefilexscjbakgo例13：為數據庫xscj添加文件組Fgroup,并為文件組添加兩個大小均為10MB,最大大小為30MB，增長方式為5MB的數據文件。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：從數據庫中刪除文件組，將xscj數據庫中的文件組Fgroup刪除，同時刪除其中的數據文件。例15：為數據庫添加一個日志文件，初始大小5MB，最大大小為10MB，文件按1MB方式增長。alterdatabasexscj addlogfile (name=xscj_log2, filename='e:\sql\xscj_log2.ldf', size=5MB, maxsize=10MB, filegrowth=1MB )go

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

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

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

將分離的數據庫重新附加服務器中，但在附加數據庫時必須指定主數據文件（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.附加數據庫

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

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

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

稀疏文件會因為源數據庫中復制數據增加而擴大。2008版會以每次64KB（即8個頁）大小為稀疏文件分配空間，這種機制可以有效地提高空間使用效益，避免相同數據存儲兩份。但有時會增加很快。如源數據庫重建索引，因索引會改變源數據庫中的數據頁很多，造成很多數據頁復制到快照中。快照復制注意事項：如果源數據庫中有多個數據文件，需在文件列表中，列出這些文件在快照中對應的邏輯名稱(與源數據文件可以不同名)，物理名稱(與源數據文件可以不同名)及路徑。快照只有數據文件，沒有事務日志文件。原因是快照是只讀的，而要記錄寫入操作。因些不需要事務日志文件。快照文件必須建立在NTFS盤區，否則無法創建’稀疏文件而失敗。快照數據庫與源數據庫有關，必須位于同一服務器實例上。一個源數據庫可以建立多個快照數據庫，反映不同時間點狀態，若源數據庫不能使用，所有快照數據庫將不能使用。快照不需要時，需要手工刪除。不能對master,temdb,model系統數據庫創建快照。createdatabasetest_data_8_10on (name=test, filename='E:\db\test_data_8_10.mdf' ) assnapshotoftestGo查看快照方法：對象資源管理器T-SQL命令SSMS不支持創建快照，只能使用T-sql命令（企業版），為快照命名時，建議名稱中含有數據庫名及快照創建時間。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