數(shù)據(jù)庫的建立與維護(hù)課件

1、第 2 章數(shù) 據(jù) 庫的建立與維護(hù) 系統(tǒng)數(shù)據(jù)庫數(shù)據(jù)庫的存儲結(jié)構(gòu)數(shù)據(jù)庫的創(chuàng)建和管理查看數(shù)據(jù)庫狀態(tài)數(shù)據(jù)庫操作數(shù)據(jù)快照的創(chuàng)建系統(tǒng)數(shù)據(jù)庫 所謂系統(tǒng)數(shù)據(jù)庫指的是隨安裝程序一起安裝，用于協(xié)助SQL Server 2008共同完成管理操作的數(shù)據(jù)庫，它們是SQL Server 2008運行的基礎(chǔ)。 在SQL Server 2008中，默認(rèn)有5個系統(tǒng)數(shù)據(jù)庫：master、model、msdb、tempdb和resource數(shù)據(jù)庫。 Master數(shù)據(jù)庫是SQL Server系統(tǒng)最重要的數(shù)據(jù)庫，它記錄了SQL Server系統(tǒng)的所有系統(tǒng)信息。 在 SQL Server 2008 中，系統(tǒng)對象不再存儲在 master

2、數(shù)據(jù)庫中，而是存儲在 Resource 數(shù)據(jù)庫中。 model 數(shù)據(jù)庫用作在 SQL Server 實例上創(chuàng)建的所有數(shù)據(jù)庫的模板。如果修改 model 數(shù)據(jù)庫，之后創(chuàng)建的所有數(shù)據(jù)庫都將繼承這些修改。 Msdb數(shù)據(jù)庫是代理服務(wù)數(shù)據(jù)庫。 Tempdb是一個臨時數(shù)據(jù)庫，它為所有的臨時表、臨時存儲過程及其他臨時操作提供存儲空間。 Tempdb數(shù)據(jù)庫由整個系統(tǒng)的所有數(shù)據(jù)庫使用。SQL Server每次啟動時，tempdb數(shù)據(jù)庫被重新建立。當(dāng)用戶與SQL Server斷開連接時，其臨時表和存儲過程自動被刪除。mastermodeltempdbmsdbAdventureWorksAdventureWorks

3、DW系統(tǒng)數(shù)據(jù)庫用戶數(shù)據(jù)庫User1StuInfo數(shù)據(jù)庫的存儲結(jié)構(gòu)數(shù)據(jù)庫的存儲結(jié)構(gòu)分為邏輯存儲結(jié)構(gòu)和物理存儲結(jié)構(gòu)兩種。1、數(shù)據(jù)庫的邏輯存儲結(jié)構(gòu)指的是數(shù)據(jù)庫是由哪些性質(zhì)的信息所組成，SQL Server的數(shù)據(jù)庫不僅只是數(shù)據(jù)的存儲，所有與數(shù)據(jù)處理操作相關(guān)的信息都存儲在數(shù)據(jù)庫中。 SQL Server的數(shù)據(jù)庫是由諸如表、視圖、索引等各種不同的數(shù)據(jù)庫對象所組成，它們分別用來存儲特定信息并支持特定功能，構(gòu)成數(shù)據(jù)庫的邏輯存儲結(jié)構(gòu)。2、數(shù)據(jù)庫的物理存儲結(jié)構(gòu)則是討論數(shù)據(jù)庫文件是如何在磁盤上存儲的。數(shù)據(jù)庫在磁盤上是以文件為單位存儲的，由數(shù)據(jù)庫文件和事務(wù)日志文件組成。 在 SQL 2008 中，一個數(shù)據(jù)庫至少有一個

4、數(shù)據(jù)文件和一個事務(wù)日志文件。該數(shù)據(jù)庫也可以有多個數(shù)據(jù)文件和多個事務(wù)日志文件。數(shù)據(jù)文件用于存放數(shù)據(jù)庫的數(shù)據(jù)和各種對象，事務(wù)日志文件用于存放事務(wù)日志。 數(shù)據(jù)庫的文件組成：（通常可以由三類文件組成） 主數(shù)據(jù)文件：默認(rèn)的擴展名為.mdf 輔助數(shù)據(jù)文件：默認(rèn)的擴展名為.ndf 事務(wù)日志文件：默認(rèn)的擴展名為.ldf在SQL Server 2008中一個數(shù)據(jù)庫至少包含一個有數(shù)據(jù)庫對象的主數(shù)據(jù)文件和一個事務(wù)日志文件。這些物理文件具有可用于Transact-SQL 語句的操作系統(tǒng)文件名稱和邏輯文件名稱所有的的數(shù)據(jù)文件和事務(wù)日志的默認(rèn)位置是 C: Progrom Files Mirosoft SQL Server

5、 MSSQL.n MSSQL Data最佳的實踐： 將所有的數(shù)據(jù)和對象存儲在輔助數(shù)據(jù)文件中，而主數(shù)據(jù)文件只負(fù)責(zé)存儲數(shù)據(jù)目錄，這種配置可有效的避免訪問時的磁盤爭用。 配置事務(wù)日志： 建立好數(shù)據(jù)庫之后，在進(jìn)行具體的操作之前應(yīng)當(dāng)首先配置事務(wù)日志。事務(wù)日志文件記錄SQL Server 對這個數(shù)據(jù)庫的操作，以便于在系統(tǒng)出現(xiàn)故障時恢復(fù)數(shù)據(jù)庫。 那事務(wù)日志又有什么樣的作用？（看個場景）小張在書店買書。他和書店進(jìn)行一次性交易付款過程包括下面這些數(shù)據(jù)庫操作：更新客戶所購書籍的庫存信息。保存客戶的付款信息和銀行系統(tǒng)進(jìn)行互動。生成訂單并保存到數(shù)據(jù)庫中。更新用戶相關(guān)信息。 上面的場景中每個數(shù)據(jù)庫操作都被稱為事務(wù)。 所

6、有的數(shù)據(jù)庫操作都將保存到相關(guān)的數(shù)據(jù)庫日志中，發(fā)生故障后，系統(tǒng)可以根據(jù)事務(wù)日志將數(shù)據(jù)庫返回到用戶下單前的狀態(tài)。正常情況一切順利進(jìn)行，交易成功，與交易相關(guān)的所有數(shù)據(jù)也成功的更新。 快速了解創(chuàng)建學(xué)校的數(shù)據(jù)庫：管理任務(wù)： 小王的學(xué)校通過數(shù)據(jù)庫管理所有老師和學(xué)生的信息，這臺服務(wù)器配有三個磁盤驅(qū)動器。在安裝了2008之后，作為一個DBA，需要實現(xiàn)以下任務(wù)： 創(chuàng)建數(shù)據(jù)庫； 創(chuàng)建數(shù)據(jù)文件； 規(guī)劃文件大小和位置； 配置數(shù)據(jù)庫選項。 管理操作過程： 小王分別在 SQL 2008 中建立了老師和學(xué)生的數(shù)據(jù)庫的文件，分別用來存儲老師和學(xué)生的信息。并且根據(jù)在校的老師和學(xué)生數(shù)量來估計文件的初始大小，老師的數(shù)據(jù)文件初始大小

7、為20 MB，學(xué)生的數(shù)據(jù)文件初始大小為80 MB，設(shè)置日志文件的總大小為數(shù)據(jù)文件的25。 由于服務(wù)器有三個硬盤驅(qū)動器，操作系統(tǒng)位于C盤，小王將數(shù)據(jù)文件分別放置于D盤和E盤，將日志文件放在C盤，這樣就可以實現(xiàn)最好的性能。 創(chuàng)建數(shù)據(jù)庫的示例： 可以使用SQL Server Management Studio中的可視化工具來創(chuàng)建數(shù)據(jù)庫，圖示：設(shè)置自動增長對話框: 文件增長：可指定文件是否在必要時增長。該選項稱為自動增長，即為啟用文件增長。 最大值：可指定MB或百分比作為文件可增長的最大值，建議指定文件增長的最大值。 排序規(guī)則：數(shù)據(jù)的排序次序原則，用以確定進(jìn)行數(shù)據(jù)排序時數(shù)據(jù)值的具體位置。 文件組： 如

8、果在數(shù)據(jù)庫中存在大量的文件，如何才能方便管理，同時提高數(shù)據(jù)庫的性能呢？探討： SQL Server 2008在數(shù)據(jù)文件中存儲數(shù)據(jù)，數(shù)據(jù)文件可分組為一個或多個文件組。雖然數(shù)據(jù)庫只用一個文件組即可成功運行，但創(chuàng)建多個文件組可能更有利。結(jié)論： 我們要學(xué)會文件組的創(chuàng)建、設(shè)計和維護(hù)，以及利用文件組改善數(shù)據(jù)庫的性能。文件組 創(chuàng)建數(shù)據(jù)庫的方法有以下三種： 1、使用SQL Server管理平臺創(chuàng)建數(shù)據(jù)庫 2、使用模板創(chuàng)建數(shù)據(jù)庫 使用模板創(chuàng)建數(shù)據(jù)庫 ，用戶根據(jù)提示操作，即可創(chuàng)建數(shù)據(jù)庫。 使用 CREATE DATABASE 語句創(chuàng)建數(shù)據(jù)庫：數(shù)據(jù)庫的創(chuàng)建和管理Transact-SQL語言使用CREATE DATA

9、BASE命令來創(chuàng)建數(shù)據(jù)庫。該命令的語法如下： CREATE DATABASE database_name ON PRIMARY ，n ， ，n LOG ON ，n FOR RESTORE :=（NAME=logical_file_name， FILENAME=os_file_name ，SIZE=size ，MAXSIZE=max_size|UNLIMITED ，F(xiàn)ILEGROWTH=growth_increment ） ，n :=FILEGROUP filegroup_name ，n各參數(shù)說明如下： database_name：數(shù)據(jù)庫的名稱，最長為128個字符。 PRIMARY：該選項是一個

10、關(guān)鍵字，指定主文件組中的文件。 LOG ON：指明事務(wù)日志文件的明確定義。 NAME=logical_file_name：指定數(shù)據(jù)庫的邏輯名稱，這是在SQL Server系統(tǒng)中使用的名稱，是數(shù)據(jù)庫在SQL Server中的標(biāo)識符。 FILENAME =os_file_name ：指定數(shù)據(jù)庫所在文件的操作系統(tǒng)文件名稱和路徑，該操作系統(tǒng)文件名和NAME的邏輯名稱一一對應(yīng)。 SIZE：指定數(shù)據(jù)庫的初始容量大小。主文件大小至少與model數(shù)據(jù)庫中的主文件相同。 ON：允許顯式定義用于存儲數(shù)據(jù)的文件。 UNLIMITED：指定了文件將一直增長到磁盤變滿為止。 FILEGROUP filegroup_na

11、me ：這是文件組在數(shù)據(jù)庫中的唯一邏輯名稱。 DEFULT：參數(shù)制定了該文件組為默認(rèn)文件組。 FILEGROWTH=growth_increment：指定文件每次增加容量的大小，該值以當(dāng)前文件大小的百分比或KB、MB、GB和TB來表示。當(dāng)指定數(shù)據(jù)為0時，表示文件不增長。 MAXSIZE=max_size：指定操作系統(tǒng)文件可以增長到的最大尺寸。以KB、MB、GB和TB為單元，默認(rèn)為MB。提示： 區(qū)分NAME與FILENAME。NAME是數(shù)據(jù)庫內(nèi)部處理文件的邏輯名稱，F(xiàn)ILENAME 是數(shù)據(jù)庫在磁盤上存放文件的文件名稱，兩者可以不同。場景： 你正在設(shè)計開發(fā)一個學(xué)校的圖書館系統(tǒng)。這個圖書館系統(tǒng)帶有一

12、個藏書記錄的數(shù)據(jù)庫。數(shù)據(jù)庫系統(tǒng)采用SQL Server 2008 ，數(shù)據(jù)庫名為TestDB。數(shù)據(jù)庫的初始大小為20 MB，日志文件的大小為5 MB。 由于不能確定學(xué)校將會把數(shù)據(jù)庫安裝在哪臺SQL Server 服務(wù)器上，你希望在安裝這個系統(tǒng)的時候，能在服務(wù)器上按照要求自動創(chuàng)建數(shù)據(jù)庫。CREATE DATABASE TestDBON -允許顯式定義用于存儲數(shù)據(jù)的文件 - 定義數(shù)據(jù)庫主文件( NAME = TestDB_Data, - 主文件邏輯名稱 FILENAME = D:DATATransactTestDB.mdf, - 操作系統(tǒng)文件名稱，包含路徑 SIZE = 20 MB, - 主文件初始

13、大小 FILEGROWTH = 0 ) - 未啟用自動增長- 定義數(shù)據(jù)庫日志文件LOG ON ( NAME = TestDB_Log, - 日志文件邏輯名稱 FILENAME = D:DATATestDB_Log.ldf, - 包含路徑的操作系統(tǒng)文件名稱 SIZE = 5 MB, - 日志文件初始大小 FILEGROWTH = 0 ) -未啟用自動增長舉例：創(chuàng)建了一個 Test 數(shù)據(jù)庫，該數(shù)據(jù)庫的主數(shù)據(jù)文件邏輯名稱為Test_data ，物理文件名為 Test.mdf，初始大小為 10MB，最大尺寸為無限大，增長速度為10%；數(shù)據(jù)庫的日志文件邏輯名稱為 Test_log，物理文件名為 Test

14、.ldf，初始大小為1MB，最大尺寸為5MB，增長速度為1MB。程序清單如下：CREATE DATABASE test ON PRIMARY ( NAME = test, FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLdatatest.mdf, 利用SQL Server管理平臺修改數(shù)據(jù)庫： 在 SQL Server 管理平臺中，右擊所要修改的數(shù)據(jù)庫，從彈出的快捷菜單中選擇【屬性】選項，出現(xiàn)如下圖所示的數(shù)據(jù)庫屬性設(shè)置對話框。 可以看到，修改或查看數(shù)據(jù)庫屬性時，屬性頁框比創(chuàng)建數(shù)據(jù)庫時多了兩個，即選項和權(quán)限頁框。利用SQL Server管理平臺修改

15、數(shù)據(jù)庫（選項、權(quán)限） REMOVE FILE ：從數(shù)據(jù)庫系統(tǒng)表中刪除文件描述并刪除物理文件。（只有在文件為空時才能刪除） REMOVE FILEGROUP ：從數(shù)據(jù)庫中刪除文件組并刪除該文件組中所有文件。 （只有在文件為空時才能刪除） MODIFY FILE ：指定要更改給定的文件，更改選項包括 NAME、SIZE 、FILEGROUPTH和MAXSIZE。注意： 若要更改數(shù)據(jù)文件或日志文件的邏輯名稱，應(yīng)在 NAME 選項中指定要改名的邏輯文件名稱，并在 NEWNAME 選項中指定文件的新邏輯名稱。舉例：將兩個數(shù)據(jù)文件和一個事務(wù)日志文件添加到 test 數(shù)據(jù)庫中。程序清單如下：ALTER DA

16、TABASE Test ADD FILE (NAME = Test1, FILENAME=c:Program FilesMicrosoft SQL Server MSSQL Datatest1.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)，(NAME = Test2, FILENAME=c:Program FilesMicrosoft SQL Server MSSQL Datatest2.ndf,SIZE = 3MB,MAXSIZE = 10MB, FILEGROWTH = 1MB)GO ALTER DATABASE Test ADD

17、LOG FILE ( NAME = testlog1， FILENAME=c:Program FilesMicrosoft SQL Server MSSQL Data testlog1.ldf， SIZE = 5MB， MAXSIZE = 100MB，F(xiàn)ILEGROWTH = 5MB)GO 刪除數(shù)據(jù)庫： 利用SQL Server管理平臺刪除數(shù)據(jù)庫和 Drop語句刪除數(shù)據(jù)庫 在SQL Server管理平臺中，右擊所要刪除的數(shù)據(jù)庫，從彈出的快捷菜單中選擇【刪除】選項即可刪除數(shù)據(jù)庫。系統(tǒng)會彈出確認(rèn)是否要刪除數(shù)據(jù)庫對話框，單擊“確定”按鈕則刪除該數(shù)據(jù)庫。 利用 Drop語句可以從SQL Server中

18、一次刪除一個或多個數(shù)據(jù)庫。其語法如下： Drop database database_name,n舉例：刪除創(chuàng)建的數(shù)據(jù)庫Test。程序清單如下： drop database Test 收縮數(shù)據(jù)庫：-減小UserDB用戶數(shù)據(jù)庫中數(shù)據(jù)文件和日志文件的大小，以便在數(shù)據(jù)庫中留出10%的可用空間 自動收縮數(shù)據(jù)庫手動收縮數(shù)據(jù)庫使用DBCC SHRINKDATABASE命令收縮數(shù)據(jù)庫DBCC SHRINKDATABASE (UserDB, 10);GOUSE UserDB;GODBCC SHRINKFILE (DataFile1, 7);GO-將UserDB用戶數(shù)據(jù)庫中名為DataFile1的數(shù)據(jù)文件的大小

19、收縮到7 MB 查看數(shù)據(jù)庫狀態(tài)狀 態(tài)描 述ONLINE在線狀態(tài)或聯(lián)機狀態(tài)，可以執(zhí)行對數(shù)據(jù)庫的訪問OFFLINE離線狀態(tài)或脫機狀態(tài)，數(shù)據(jù)庫不能正常使用。用戶可以人工設(shè)置，可以執(zhí)行對處于這種狀態(tài)的數(shù)據(jù)庫文件的移動等處理RESTORING還原狀態(tài)，正在還原主文件組的一個或多個文件。這時，數(shù)據(jù)庫不能使用RECOVERING恢復(fù)狀態(tài)，正在恢復(fù)數(shù)據(jù)庫。這是一個臨時性狀態(tài)，如果恢復(fù)成功，那么數(shù)據(jù)庫自動處于在線狀態(tài)；如果恢復(fù)失敗，那么數(shù)據(jù)庫處于不能正常使用的可疑狀態(tài)RECOVERY PENDING恢復(fù)未完成狀態(tài)。恢復(fù)過程中缺少資源造成的問題狀態(tài)。這時數(shù)據(jù)庫不可使用，必須執(zhí)行其他操作來解決這種問題SUSPECT

20、可疑狀態(tài)，主文件組可疑或可能被破壞。這時數(shù)據(jù)庫不能使用，必須執(zhí)行其他操作來解決這種問題EMERGENCY緊急狀態(tài)，可以人工通過更改數(shù)據(jù)庫設(shè)置為該狀態(tài)。這時數(shù)據(jù)庫處于單用戶模式和只讀狀態(tài)，只能由sysadmin固定服務(wù)器角色成員訪問。主要用于對數(shù)據(jù)庫的故障排除查看數(shù)據(jù)庫信息： 在 SQL Server 2008系統(tǒng)中，可以使用一些目錄視圖、函數(shù)、存儲過程查看有關(guān)數(shù)據(jù)庫的基本信息。 sys.databases 數(shù)據(jù)庫和文件目錄視圖可以查看有關(guān)數(shù)據(jù)庫的基本信息，sys.database_files可以查看有關(guān)數(shù)據(jù)庫文件的信息，sys.filegroups 可以查看有關(guān)數(shù)據(jù)庫文件組的信息，sys.ma

21、ster_files 可以查看數(shù)據(jù)庫文件的基本信息和狀態(tài)信息。查看數(shù)據(jù)庫狀態(tài)： use 教務(wù)管理系統(tǒng) go select DATABASEPROPERTYEX(教務(wù)管理系統(tǒng),Recovery) 就會顯示出此數(shù)據(jù)庫當(dāng)前的恢復(fù)模式。數(shù)據(jù)庫操作分離數(shù)據(jù)庫： 分離數(shù)據(jù)庫是指將數(shù)據(jù)庫從SQL Server實例中刪除，但是該數(shù)據(jù)庫的數(shù)據(jù)文件和事務(wù)日志文件依然保持不變。這樣可以將該數(shù)據(jù)庫附加到任何的SQL Server實例中。 使用sp_detach_db存儲過程來執(zhí)行數(shù)據(jù)庫分離操作，還可使用SQL Server Management Studio工具來執(zhí)行分離操作。附加數(shù)據(jù)庫： 將分離后的數(shù)據(jù)庫附加到某個Microsoft SQL Server實例中時，可以使用CREATE DATABASE語句；還可使用SSMS工具來執(zhí)行分離操作。 附加數(shù)據(jù)庫時所有的數(shù)據(jù)文件必須都是可用的。在附加數(shù)據(jù)庫過程中，如果沒有日志文件，系統(tǒng)將自動創(chuàng)建一個新的日志文件。復(fù)制和移動數(shù)據(jù)庫： 使用 SQL Server 2008 的【復(fù)制數(shù)據(jù)庫向?qū)А抗ぞ呖梢詮?fù)制或移動數(shù)據(jù)庫，創(chuàng)建數(shù)據(jù)庫的副本，在SQL 不同實例間復(fù)制和移動數(shù)據(jù)庫，以及將SQL 2000的數(shù)據(jù)庫升級到SQL2008。所有這些操作除model、msdb和master系統(tǒng)數(shù)據(jù)庫外都適用。SQL 2008使用兩種技術(shù)來進(jìn)行復(fù)制和移動操作。數(shù)據(jù)庫快照