網(wǎng)站數(shù)據(jù)庫技術(shù)chap8-sql server數(shù)據(jù)庫系統(tǒng)管理

1、第8章 SQL SERVER數(shù)據(jù)庫系統(tǒng)管理本章目標本章結(jié)束時，學員能夠了解和掌握：SQL SERVER 2000 DBA職能簡介數(shù)據(jù)庫設計及規(guī)劃SQL SERVER 2000數(shù)據(jù)庫操作分離數(shù)據(jù)庫附加數(shù)據(jù)庫刪除數(shù)據(jù)庫收縮數(shù)據(jù)庫SQL SERVER 2000備份備份設備簡介備份的類型備份的內(nèi)容規(guī)劃備份策略SQL SERVER 2000數(shù)據(jù)庫恢復 恢復的概念 從不同的備份中恢復數(shù)據(jù)庫數(shù)據(jù)轉(zhuǎn)換與導入導出 數(shù)據(jù)轉(zhuǎn)換的方法8.1 DBA （DataBase Administrator） 簡介DBA所要掌握的技能SQL ServerWindows 2000/NT關系型數(shù)據(jù)庫SQL語言備份和恢復過程業(yè)務處理D

2、BA的職責安裝SQL Server建立和維護數(shù)據(jù)庫管理用戶管理權(quán)限備份和恢復數(shù)據(jù)調(diào)度任務監(jiān)視和調(diào)整服務器管理SQL Server復制。8.2 認識系統(tǒng)數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫用戶數(shù)據(jù)庫mastermodeltempdbmsdbpubsNorthwindUser1distributionDistribution在分發(fā)復制時系統(tǒng)自動創(chuàng)建8.3 數(shù)據(jù)庫設計及規(guī)劃Microsoft SQL Server 2000 使用一組操作系統(tǒng)文件映射數(shù)據(jù)庫。數(shù)據(jù)庫中的所有數(shù)據(jù)和對象（如表、存儲過程、觸發(fā)器和視圖）都存儲在下列操作系統(tǒng)文件中： 主文件：該文件包含數(shù)據(jù)庫的啟動信息，并用于存儲數(shù)據(jù)。每個數(shù)據(jù)庫都有一個主要數(shù)據(jù)文

3、件輔助文件：這些文件含有不能置于主要數(shù)據(jù)文件中的所有數(shù)據(jù)。如果主文件可以包含數(shù)據(jù)庫中的所有數(shù)據(jù)，那么數(shù)據(jù)庫就不需要次要數(shù)據(jù)文件。有些數(shù)據(jù)庫可能足夠大，因而需要多個次要數(shù)據(jù)文件，或使用位于不同磁盤驅(qū)動器上的輔助文件將數(shù)據(jù)擴展到多個磁盤事務日志：這些文件包含用于恢復數(shù)據(jù)庫的日志信息。每個數(shù)據(jù)庫都必須至少有一個日志文件。8.3數(shù)據(jù)庫設計及規(guī)劃(續(xù))在設計數(shù)據(jù)庫時，應考慮以下事項：數(shù)據(jù)庫的用途及該用途將如何影響設計。應創(chuàng)建符合用途的數(shù)據(jù)庫計劃數(shù)據(jù)庫規(guī)范化規(guī)則，防止數(shù)據(jù)庫設計中出現(xiàn)錯誤對數(shù)據(jù)完整性的保護數(shù)據(jù)庫和用戶權(quán)限的安全要求應用程序的性能需求維護估計數(shù)據(jù)庫大小8.3數(shù)據(jù)庫設計及規(guī)劃(續(xù))設計規(guī)則:獲

4、得合理的數(shù)據(jù)庫設計的規(guī)則：表應該有一個標識符表應只能存儲單一類型實體的數(shù)據(jù)表應避免可為空的列表不應有重復的值或列考慮將重復的數(shù)據(jù)置于與主表有鏈接的另一個表中 8.3數(shù)據(jù)庫設計及規(guī)劃(續(xù))文件設計規(guī)則:文件或文件組不能由一個以上的數(shù)據(jù)庫使用文件只能是一個文件組的成員數(shù)據(jù)和事務日志信息不能屬于同一文件或文件組事務日志文件不能屬于任何文件組8.4 數(shù)據(jù)庫管理創(chuàng)建數(shù)據(jù)庫數(shù)據(jù)庫屬性收縮數(shù)據(jù)庫和數(shù)據(jù)庫文件刪除數(shù)據(jù)庫移動數(shù)據(jù)庫數(shù)據(jù)庫維護計劃 8.4.1創(chuàng)建數(shù)據(jù)庫在創(chuàng)建數(shù)據(jù)庫之前，請考慮如下事項： 創(chuàng)建數(shù)據(jù)庫的權(quán)限默認授予 sysadmin 和 dbcreator 固定服務器角色的成員，但是它仍可以授予其他用

5、戶創(chuàng)建數(shù)據(jù)庫的用戶將成為該數(shù)據(jù)庫的所有者在一個服務器上，最多可以創(chuàng)建 32,767 個數(shù)據(jù)庫數(shù)據(jù)庫名稱必須遵循標識符規(guī)則創(chuàng)建數(shù)據(jù)庫方法TransactSQL企業(yè)管理器企業(yè)管理創(chuàng)建數(shù)據(jù)庫向?qū)?.4.1.1用TransactSQL創(chuàng)建數(shù)據(jù)庫CREATE DATABASE SampleON PRIMARY ( NAME=SampleData, FILENAME=c:Program Files.DataSample.mdf, SIZE=10MB, MAXSIZE=15MB, FILEGROWTH=20%) LOG ON ( NAME=SampleLog, FILENAME= c:Program Fil

6、es.DataSample.ldf, SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=1MB) 8.4.1.1用TransactSQL創(chuàng)建數(shù)據(jù)庫（續(xù)）在這里輸入創(chuàng)建數(shù)據(jù)庫的SQL語句SQL語句執(zhí)行結(jié)果，顯示創(chuàng)建成功單擊運行按鈕執(zhí)行創(chuàng)建數(shù)據(jù)庫的語句 8.4.1.2利用企業(yè)管理器來創(chuàng)建數(shù)據(jù)庫 8.4.1.2利用企業(yè)管理器來創(chuàng)建數(shù)據(jù)庫（續(xù)） 8.4.1.2利用企業(yè)管理器來創(chuàng)建數(shù)據(jù)庫（續(xù)）新創(chuàng)建的數(shù)據(jù)庫 8.4.1.3利用數(shù)據(jù)庫向?qū)?chuàng)建數(shù)據(jù)庫 8.4.1.3利用數(shù)據(jù)庫向?qū)?chuàng)建數(shù)據(jù)庫（續(xù)） 8.4.1.3利用數(shù)據(jù)庫向?qū)?chuàng)建數(shù)據(jù)庫（續(xù)） 8.4.1.3利用數(shù)據(jù)庫向?qū)?chuàng)建數(shù)據(jù)庫（續(xù)） 8

7、.4.2數(shù)據(jù)庫屬性 8.4.2數(shù)據(jù)庫屬性（續(xù)）8.4.3收縮數(shù)據(jù)庫和數(shù)據(jù)庫文件經(jīng)常對數(shù)據(jù)庫做刪除插入操作就會產(chǎn)生空白頁為了節(jié)省磁盤空間我們可以定期進行數(shù)據(jù)庫收縮數(shù)據(jù)庫收縮并不是壓縮數(shù)據(jù)而是降低空白頁的數(shù)目數(shù)據(jù)庫不能收縮到比創(chuàng)建時還小不能在備份數(shù)據(jù)庫或者日志時執(zhí)行收縮任務 8.4.3 收縮數(shù)據(jù)庫和數(shù)據(jù)庫文件（續(xù)）使用設定的壓縮計劃來壓縮數(shù)據(jù)庫設定空閑空間占文件大小的百分比單獨指定數(shù)據(jù)庫中各數(shù)據(jù)文件和事務日志文件的壓縮操 8.4.3收縮數(shù)據(jù)庫和數(shù)據(jù)庫文件（續(xù)） 8.4.3收縮數(shù)據(jù)庫和數(shù)據(jù)庫文件（續(xù)） 8.4.4刪除數(shù)據(jù)庫DROP DATABASE Northwind, pubs 8.4.4刪除數(shù)據(jù)

8、庫(續(xù))下列情況下不能刪除數(shù)據(jù)庫：當有用戶連接到數(shù)據(jù)庫時當作為復制的一部分正在被出版時當為系統(tǒng)數(shù)據(jù)庫時 8.4.5 移動數(shù)據(jù)庫分離、附加如果想按以下方式移動數(shù)據(jù)庫，則分離和附加數(shù)據(jù)庫很有用： 從一臺計算機移到另一臺計算機，而不必重新創(chuàng)建數(shù)據(jù)庫，然后手動還原數(shù)據(jù)庫備份。移到另一物理磁盤上，例如，當包含該數(shù)據(jù)庫文件的磁盤空間已用完，您希望擴充現(xiàn)有的文件而又不愿將新文件添加到其它磁盤上的數(shù)據(jù)庫。 將數(shù)據(jù)庫或數(shù)據(jù)庫文件移動到另一服務器或磁盤： 分離數(shù)據(jù)庫。 將數(shù)據(jù)庫文件移到另一服務器或磁盤。 通過指定移動文件的新位置附加數(shù)據(jù)庫。 8.4.5 移動數(shù)據(jù)庫（續(xù)）分離 8.4.5 移動數(shù)據(jù)庫(續(xù))附加數(shù)據(jù)庫

9、 8.4.6數(shù)據(jù)庫維護計劃 8.4.6數(shù)據(jù)庫維護計劃（續(xù)） 8.4.6數(shù)據(jù)庫維護計劃（續(xù)） 8.4.6數(shù)據(jù)庫維護計劃（續(xù)） 8.4.6數(shù)據(jù)庫維護計劃（續(xù)） 8.4.6數(shù)據(jù)庫維護計劃（續(xù)） 8.4.6數(shù)據(jù)庫維護計劃（續(xù)） 8.4.6 數(shù)據(jù)庫維護計劃（續(xù)） 8.4.6數(shù)據(jù)庫維護計劃（續(xù)） 8.4.6數(shù)據(jù)庫維護計劃（續(xù)） 8.4.6數(shù)據(jù)庫維護計劃（續(xù)）8.5數(shù)據(jù)庫的備份備份的概念及作用制作數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)庫的拷貝防止數(shù)據(jù)庫遭到破壞備份數(shù)據(jù)庫的頻繁程度取決于許損失的數(shù)據(jù)量及數(shù)據(jù)庫活動量備份的準則處于在線事務處理(OLTP)環(huán)境中，要經(jīng)常性地備份系統(tǒng)活動不多或主要用于決策支持，不用太頻繁的備份把備份安排

10、在SQL Server沒有被大量更新的過程中8.5.1備份的概述使用恢復模型可以為 Microsoft SQL Server 2000 中的每個數(shù)據(jù)庫選擇三種恢復模型中的一種，以確定如何備份數(shù)據(jù)以及能承受何種程度的數(shù)據(jù)丟失。 簡單恢復 :簡單恢復允許將數(shù)據(jù)庫恢復到最新的備份。完全恢復 :完全恢復允許將數(shù)據(jù)庫恢復到故障點狀態(tài)。大容量日志記錄恢復: 大容量日志記錄恢復允許大容量日志記錄操作。當創(chuàng)建新的數(shù)據(jù)庫時，新的數(shù)據(jù)庫繼承 model 數(shù)據(jù)庫的恢復模型8.5.1備份的概述(續(xù))使用恢復模型故障還原模型8.5.1備份的概述(續(xù))SQL Server備份備份的角色成員 sysadmin、 db_ow

11、ner、 db_backupoperator備份的介質(zhì) 硬盤、磁帶或命名管道Named Pipe SQL Server提供備份到Named Pipe的能力，以便用戶充分利用其它廠家軟件包的備份和恢復特性 8.5.1備份的概述(續(xù))備份的內(nèi)容備份系統(tǒng)數(shù)據(jù)庫 主要指master、 msdb和 model數(shù)據(jù)庫備份用戶數(shù)據(jù)庫備份過程中限制的活動 8.5.1備份的概述(續(xù))備份系統(tǒng)數(shù)據(jù)庫修改master數(shù)據(jù)庫之后Mssq180BinnRebuildm.exe執(zhí)行下列語句后，自動修改CREATE DATABASE、ALTER DATABASE 或 DROP DATABASEsp_logdevice系統(tǒng)存

12、儲過程sp_addserver, sp_dropserver 和sp_addlinkedserver修改msdb數(shù)據(jù)庫之后修改model數(shù)據(jù)庫之后 8.5.1備份的概述(續(xù))備份用戶數(shù)據(jù)庫在創(chuàng)建數(shù)據(jù)庫后在創(chuàng)建索引后在清除了事務日志后若創(chuàng)建了索引之后，您只備份了事務日志，并在未來某一時刻恢復該事務日志，則SOL Server必須重建索引。要求用來重建索引的時間量，可能會比恢復復全庫備份的時間還要長。在執(zhí)行完不記日志的操作后不被記錄到事務日志的操作BACKUP LOG WITH NO_LOG語句WRITETEXT或UPDATETEXT語句SELECT INTO語句8.5.1備份的概述(續(xù)) 備份過

13、程中受限制的活動創(chuàng)建或修改數(shù)據(jù)庫創(chuàng)建索引執(zhí)行不記日志的操作8.5.2 執(zhí)行備份使用永久性備份文件使用臨時性備份文件 8.5.2.1創(chuàng)建永久性備份文件永久性備份的含義自動化備份任務8.5.2.1創(chuàng)建永久性備份文件(續(xù)) 8.5.2.1創(chuàng)建永久性備份文件(續(xù))新創(chuàng)建的備份設備 8.5.2.2臨時性的備份文件臨時性備份文件的含義執(zhí)行一次性備份 使用BACKUP DATABASE 語句8.5.2.2臨時性的備份文件（續(xù)）下面這個例子在硬盤上創(chuàng)建一個臨時性備份文件，并且把northwind數(shù)據(jù)庫備份到此臨時性備份文件中。USE masterBACKUP DATABASE northwind to DIS

14、K=C:TempMycustomers.bak8.5.3備份類型 全庫備份 差異備份事務日志備份一個數(shù)據(jù)庫文件或文件組的備份 8.5.3.1執(zhí)行一個全庫備份 如果您的數(shù)據(jù)庫主要是一個只讀數(shù)據(jù)庫，全庫備份或許足以防止數(shù)據(jù)損失。在系統(tǒng)出故障的 情況下，可把全庫備份用作基線。當您執(zhí)行全庫備份時，SQL Server要：備份在備份過程中發(fā)生的任何活動。把任何未提交的事務備份到事務日志。 恢復備份時，SQL Server利用備份文件中捕捉到的部分事務日志來確保數(shù)據(jù)一致性。BACKUP DATABASE northwind to nwndbac 8.5.3.2執(zhí)行差異備份用于經(jīng)常被修改的數(shù)據(jù)庫要求一個全庫

15、備份備份自上次全庫備份以來的數(shù)據(jù)庫變化因備份集小而節(jié)省時間語法： BACKUP DATABASE northwind TO DISK=D:MydataMydiffbackup.bak WITH DIFFERENTIAL 8.5.3.3執(zhí)行一個事務日志備份提供對任何數(shù)據(jù)庫變化的備份 要求一個全庫備份備份從最近執(zhí)行一次BACKUPLOG語句之后到當前事務日志末尾的全部數(shù)據(jù)庫更改截斷事務日志 8.5.3.3執(zhí)行一個事務日志備份(續(xù))BACKUP LOG的語法形式BACKUP LOG northwind TO nwndbac使用NO-TRUNCATE選項允許在數(shù)據(jù)庫損壞時備份日志使用TRUNCATE-

16、ONLY選項 8.5.3.4執(zhí)行一個數(shù)據(jù)庫文件或文件組的備份用與特大型數(shù)據(jù)庫（VLDB）單獨備份數(shù)據(jù)庫文件確保備份一個文件組中的所有的數(shù)據(jù)庫文件備份事務日志語法： BACKUP DATABASE phoneorders FILE=orders2 to orderbackup2 BACKUP LOG phoneorders to orderbackuplog 8.5.4規(guī)劃一個備份策略全庫備份策略全庫及事務日志備份策略差異備份策略數(shù)據(jù)庫文件或文件組備份策略 8.5.4規(guī)劃一個備份策略（續(xù)）全庫備份策略實施的情況：數(shù)據(jù)庫較小，備份一個小數(shù)據(jù)庫所需的時間量是可接受的；數(shù)據(jù)庫沒有什么數(shù)據(jù)修改或是只讀型

17、的 8.5.4規(guī)劃一個備份策略（續(xù)）全庫和事務日志備份策略實施的情況：對經(jīng)常被修改的數(shù)據(jù)庫實行全庫和事務日志備份備份策略及恢復策略:存儲方式全庫備份及事務日志備份時間恢復的方法和時間 8.5.4規(guī)劃一個備份策略（續(xù)）差異備份策略實施的情況對數(shù)據(jù)庫做更改時使用縮短還原時間備份策略及恢復策略全庫備份時間及差異備份時間全庫恢復時間及差異恢復時間 8.5.4規(guī)劃一個備份策略（續(xù)）數(shù)據(jù)庫文件和文件組備份策略 實施的情況:應用于劃分多個文件的數(shù)據(jù)庫保證數(shù)據(jù)的一致性備份策略和恢復策略:數(shù)據(jù)劃分全庫備份時間和文件備份時間，日志備份時間恢復時間8. 6數(shù)據(jù)庫的恢復 數(shù)據(jù)庫恢復是指將數(shù)據(jù)庫備份加載到系統(tǒng)中的過程

18、恢復是與備份相對應的操作8.6.1恢復的概念 系統(tǒng)在恢復進程中的活動：安全性檢查、重建數(shù)據(jù)庫及文件Restore語句不能正確恢復的情況備份文件中數(shù)據(jù)庫不同于指定的數(shù)據(jù)庫服務器上的文件集不同于備份中的文件集沒有提供恢復數(shù)據(jù)庫的全部文件或文件組恢復進程自動進行8.6.1恢復的概念(續(xù))驗證備份 兩種驗證的方法：使用SQL Server Enterprise Manager查看每一個備份設備的屬性 使用一些Transact-SQL語句，可以得到更加詳細的備份信息。驗證備份信息常用的Transact-SQL語 句有：RESTORE HEADERONLY、RESTORE FILELISTONLY、RES

19、TORE LABELONLY、RESTORE VERIFYONLY8.6.1恢復的概念(續(xù))在恢復之前執(zhí)行指定的任務必須限制用戶對數(shù)據(jù)庫的訪問以及事務日志的備份應該設置數(shù)據(jù)庫的dbo use only選項使用SQL Server Enterprise Manager執(zhí)行系統(tǒng)存儲過程sp_dboption。語法： sp_dboptiondbname=database ,optname=option_name ,optvalue=value 8.6.1恢復的概念在恢復之前執(zhí)行指定的任務（續(xù)）8.6.1恢復的概念(續(xù))恢復數(shù)據(jù)庫的方法兩種恢復操作的方法如下所示：使用RESTORE語句使用SQL Se

20、rver 企業(yè)管理器 8.6.1恢復的概念(續(xù))使用RESTORE語句恢復數(shù)據(jù)庫RESTORE DATABASE database_name_varFROM,.nWITH , FILE=file_number , MOVE logical_file_name TO operating_system_file_name , REPLACE , NORECOVERYRECOVERY STANDBY=undo_file_name其中是backup_file_namebackup_file_name_var DISKTAPEPIPE=temp_backup_filetemp_backup_file_v

21、ar 8.6.1恢復的概念使用RESTORE語句恢復數(shù)據(jù)庫（續(xù)）在RESTORE語句中，可以使用一些選項 ：RECOVERY RECOVERY選項是系統(tǒng)的默認選項。該選項用于恢復最后一個事務日志或者完全數(shù)據(jù)庫恢復，可以保證數(shù)據(jù)庫的一致性。當使用該選項時，系統(tǒng)取消事務日志中任何未提交的事務，并提交任何完成的事務。在數(shù)據(jù)庫恢復進程完成之后，就可以使用數(shù)據(jù)庫。如果必須使用增量備份恢復數(shù)據(jù)庫，就不能使用該選項。NORECOVERY 當需要恢復多個備份時，應使用NORECOVERY選項。這時，系統(tǒng)既不取消事務日志中任何未提交的事務，也不提交任何已完成的事務。在數(shù)據(jù)庫恢復之前，數(shù)據(jù)庫是不能使用的8.6.1

22、恢復的概念使用RESTORE語句恢復數(shù)據(jù)庫（續(xù)）示例 ：從一個已經(jīng)存在的永久性備份文件saledbk中恢復數(shù)據(jù)庫sales。USE masterRESTORE DATABASE sales FROM saledbk 8.6.1恢復的概念(續(xù))使用企業(yè)管理器恢復數(shù)據(jù)庫 8.6.1恢復的概念使用企業(yè)管理器恢復數(shù)據(jù)庫（續(xù)） 8.6.2 從不同的備份中恢復數(shù)據(jù)庫 從完全數(shù)據(jù)庫備份中恢復從差異備份中恢復 從事務日志備份中恢復 從文件或者文件組備份中恢復 8.6.2.1從完全數(shù)據(jù)庫備份中恢復 重新創(chuàng)建數(shù)據(jù)庫以及與數(shù)據(jù)庫相關的全部文件所有的數(shù)據(jù)庫對象都由系統(tǒng)自動創(chuàng)建用戶應指定RECOVERY選項例如：USE

23、 masterRESTORE DATABASE northwindFROM nwindbacWITH FILE =2RECOVERY對于用戶來說，沒有必要在恢復數(shù)據(jù)庫之前重新創(chuàng)建數(shù)據(jù)庫。一般在數(shù)據(jù)庫的物理磁盤文件損壞，或者整個數(shù)據(jù)庫被刪除、破壞時，應該從完全數(shù)據(jù)庫備份中恢復8.6.2.2從差異備份中恢復 SQL Server系統(tǒng)只恢復從最近一次完全數(shù)據(jù)庫備份以后數(shù)據(jù)庫的變化部分，并且將數(shù)據(jù)庫返回到執(zhí)行增量備份時的狀態(tài)從增量備份中恢復數(shù)據(jù)庫所需要的時間比從事務日志備份中恢復數(shù)據(jù)庫要少還原差異數(shù)據(jù)庫備份的順序為： 還原最新的數(shù)據(jù)庫備份。還原最后一次的差異數(shù)據(jù)庫備份。如果使用完全恢復或大容量日志記錄恢復，則應用自上次創(chuàng)建差異數(shù)據(jù)庫備份后創(chuàng)建的所有事務日志備份。 8.6.2.2從差異備份中恢復(續(xù))下例還原 MyNwind 數(shù)據(jù)庫的全庫和差異數(shù)據(jù)庫備份: RESTORE DATABASE MyNwind FROM MyNwind_1 WITH NORECOVERY GO RESTORE DATABASE MyNwind FROM MyNwind_1 WITH FILE = 2 RECOVERY8.6.2.3從事務日