1第3章SQLServer2019數據庫本章主要介紹以下內容:3.1系統數據庫3.2數據庫文件及文件組3.3創建數據庫3.4設置數據庫選項3.5管理數據庫3.6數據庫快照概述數據庫是為了滿足多個用戶的多種應用的需要，按照一定的數據模型在計算機系統中組織、存儲和使用的互相聯系的數據集合。如在電子商務網站中，需要存儲和管理的客戶信息、訂單信息、產品信息等數據，在業務處理過程中是一組相互關聯的數據，可以保存在一個數據庫中；而與學校管理相關的學生信息、成績信息、課程信息等數據，是另一組相互關聯的數據，可以保存在另一個數據庫中。在SQLServer中，數據庫所包含的內容不僅僅只是數據，還包括與數據管理和操作相關的各種信息，如：數據庫關系圖、表、視圖、同義詞、可編程性、ServiceBroker、存儲、安全性等對象，而通常所說的數據則保存在其中的一個數據庫對象：數據表中。由此可見，數據庫這一概念在SQLServer中已成為一個存儲數據庫對象的容器。3.1SQLServer2019的系統數據庫在SQLServer2019中數據庫分為兩大類：系統數據庫和用戶數據庫。系統數據庫用于保存系統運行所需的各種數據，包括用戶數據庫信息和其他系統性信息。用戶數據庫是由用戶創建的，用于保存某些特定信息的數據庫。系統數據庫由SQLServer系統預設。在SQLServer2019安裝完成后，就默認創建了五個系統數據庫：master、model、msdb、tempdb和Resource。3.1.1mastermaster數據庫是SQLServer系統中最重要的系統數據庫，記錄了SQLServer系統運行所需的系統信息。這些系統信息包括：所有登錄名和用戶ID及所屬角色所有的系統配置信息（如數據排序規則、安全規則等）服務器中其他系統數據和用戶數據等信息，如：數據庫的名稱、數據庫文件的物理位置等SQLServer的初始化信息各種特殊的系統表，如存儲緩存使用規則、可用字符集、可用語言列表、系統錯誤和警告信息等的數據表。3.1.2modelmodel數據庫是模板數據庫。在SQLServer中創建用戶數據庫時，都會以model數據庫為模板，創建擁有相同對象和結構的數據庫。該模板數據庫的結構如圖所示。如果修改model數據庫，之后創建的所有數據庫都將繼承這些修改。因此，如果希望新創建的數據庫都有具有相同的特性，如希望在所有新創建的數據庫中都建有某個相同的數據表，那么可以預先把這個表建在model數據庫中。3.1.3msdbmsdb數據庫是存儲代理服務信息的數據庫。

SQLServer代理服務運行所需的作業信息，如作業運行的時間、頻率、操作步驟、警報等信息都保存在msdb數據庫中。3.1.4tempdbtempdb是一個臨時數據庫。當每次SQLServer服務重新啟動時，會創建一個空的tempdb數據庫；在SQLServer服務停止或關閉時，tempdb數據庫會丟失。tempdb數據庫用于保存SQLServer運行過程中產生的需要臨時存儲的數據。用戶創建的臨時表，也會存放在tempdb中。3.1.5ResourceResource是自SQLServer2005版起新增的一個系統數據庫。在SQLServer2005版以前，所有可執行的系統對象都存儲在master數據庫中。這些可執行系統對象是指不存儲數據的系統對象，包括：存儲過程、視圖、函數、觸發器等。如在SQLServer2000版的master數據庫中，系統對象就有約1200個。在SQLServer2005中，這類對象只有約70個，原因是上述很多對象已被存放到了Resource數據庫中。3.2數據庫文件及文件組3.2.1SQLServer數據存儲原理1、數據存取過程SQLServer為提高數據存儲的可靠性，采用了優先寫日志的方式。即在SQLServer中存儲數據的文件除了數據文件外，增加了事務日志文件。數據文件用于保存數據，日志文件用于保存各種操作事務，如修改、新增數據的事務。3.2數據庫文件及文件組3.2.1SQLServer數據存儲原理2、存儲空間分配SQLServer在存儲空間分配中使用了較小的數據存儲單元，即頁和盤區。頁是SQLServer數據文件存儲的最小單位，頁的大小為8192字節，即8KB。其中96Byte用于保存頭部信息，用于記錄此頁的相關信息，另外在頁尾存儲用于記錄數據行位置的行偏移和其他一些信息。因此，一頁實際可保存的數據量為8060Byte。根據頁保存數據類型的不同，頁可以劃分成為：數據頁、全局分配圖頁、索引頁、索引分配圖頁、頁面自由空間頁和文本/圖像頁。盤區是連續8個頁的集合,盤區根據實際保存數據的不同，可以劃分成為兩類：單一盤區（也稱統一盤區）和混合盤區（也稱混合區）。單一盤區中所存放的數據為一個數據對象所有，如某盤區8個頁，存放的都是“數據表1”的數據；混合盤區存放的數據來自多個對象，如有“數據表1”和“數據表2”的數據等。當混合區中的表或索引的大小增長到8頁時，系統會將表或索引存放到專門的單一盤區中，以提高訪問的效率。10存儲空間分配日志文件:.ldf數據文件:

.mdfor.ndf區:8個連續的8KB頁頁:8KB2025/6/10103.2.2SQLServer數據庫文件SQLServer采用兩類文件來保存數據：數據文件和事務日志文件。數據文件存儲數據，事務日志文件記錄各種對數據庫的操作；數據文件還可往下分為兩類：主數據文件和輔助數據文件。1、主數據文件主數據文件（PrimaryDataFile，擴展名為MDF）是SQLServer數據庫中最重要的文件，每個SQLServer數據庫有且僅有一個主數據文件。在主數據文件中可以保存SQLServer數據庫中的所有數據，包括用戶對象和系統對象（如系統表）。2、輔助數據文件輔助數據文件（SecondaryDataFile，擴展名為NDF），也稱為次數據文件，在SQLServer中用于保存用戶數據，比如用戶數據表、用戶視圖等；但是不能保存系統數據。與主數據文件在SQLServer數據庫中有且只能有一個不同，輔助數據文件在一個數據庫中可以有多個，一個數據庫最多可以有32767個輔助數據文件。3.2.2SQLServer數據庫文件3、事務日志文件事務日志文件（LogFile，擴展名為：LDF），是SQLServer數據庫中用于記錄操作事務的文件。在SQLServer數據庫中，事務日志文件也是不可缺少的數據庫文件。但與主數據文件在每個數據庫中只能有一個不同，事務日志文件可以有多個，最多可達32767個。為提高系統的可靠性和安全性，可以將事務日志文件與主數據文件分別存放在不同分區，如果有多個硬盤，建議存放在不同硬盤中。3.2.3文件組在數據文件或事務日志文件數量較多的場合，可以通過文件組對數據文件和事務日志文件進行分組管理。文件組就是文件的邏輯分組。在SQLServer2019中文件組可以劃分為兩大類：主文件組（PrimaryFileGroup）、次文件組（SecondaryFileGroup）。還有一種特殊的文件組是默認文件組（DefaultFileGroup）。主文件組是每個數據庫默認提供的文件組，該文件組不能被刪除。主數據文件只能置于主文件組中。次文件組是由用戶創建的文件組，在一個數據庫中用戶可以根據管理需要創建多個次文件組。次文件組也被稱為用戶定義文件組（User-definedFileGroup）默認文件組是在新增數據庫文件時，如果未明確指定該數據文件所屬的文件組，那么該數據文件就會被放置在默認文件組中。系統缺省的默認文件組對應主文件組，但可以修改，如可以將某個用戶文件組設置為默認文件組。14AdventureWorks數據庫文件組默認文件組OrderHistoryGroupE:\C:\D:\AdventureWorks_

Log.IdfAdventureWorks_

Data.mdfOrdHist2.ndfOrdHist1.ndfsys...

sys...

sysusers

sysobjects

...

SalesOrderHeader

Customer

Product

OrdHistYear2

OrdHistYear1

3.3創建數據庫3.3.1使用SSMS創建數據庫DEMO3.3創建數據庫3.3.2使用TSQL語句創建數據庫

創建數據庫的TSQL語句為“CREATEDATABASE”，該語句的基本語法如下：CREATEDATABASEdatabase_name

[ON[PRIMARY][<filespec>[,...n][,<filegroup>[,...n]][LOGON{<filespec>[,...n]}]][COLLATEcollation_name][WITH<external_access_option>]][;]<filespec>::={(NAME=logical_file_name,FILENAME={'os_file_name'|'filestream_path'}[,SIZE=size[KB|MB|GB|TB]][,MAXSIZE={max_size[KB|MB|GB|TB]|UNLIMITED}][,FILEGROWTH=growth_increment[KB|MB|GB|TB|%]])[,...n]}<filegroup>::={FILEGROUPfilegroup_name[CONTAINSFILESTREAM][DEFAULT]<filespec>[,...n]}主要關鍵詞含義如下：database_name，指定新創建的數據庫的名稱，可長達128個字符。PRIMARY，指定主數據文件的名稱及路徑。LOGON，指定事務日志文件的名稱及路徑。NAME，指定數據庫文件的邏輯名稱，是數據庫文件在SQLServer中的標識符，與圖3-4數據庫文件列表中的“邏輯名稱”對應。FILENAME，指定數據庫文件在操作系統中的文件名稱和路徑，該操作系統文件名和NAME的邏輯名稱一一對應。SIZE，指定數據庫文件的初始存儲空間大小。MAXSIZE，指定數據庫文件的最大可用存儲空間大小。FILEGROWTH，指定文件每次增加容量的大小，當指定數據為0時，表示文件不增長。例如：CREATEDATABASECateringNONPRIMARY(NAME=CateringN_Data,filename='c:\data\CateringN_Data.mdf',Size=3MB,maxsize=20MB,fileGrowth=1MB),(NAME=CateringN_Data_1,filename='C:\data\CateringN_Data1.ndf',size=1MB,maxsize=20MB,filegrowth=2MB),(NAME=CateringN_Data_2,filename='C:\data\CateringN_Data2.ndf',size=2MB,maxsize=20MB,filegrowth=2MB)Logon(NAME=CateringN_Log,filename='c:\data\CateringN_Log.ldf',size=1MB,maxsize=20MB,filegrowth=10%)Go3.4設置數據庫選項通過對數據庫配置選項，可以定制數據庫特性。在SQLServer2019提供了50多個數據庫選項，包括：排序規則、恢復模式、兼容級別、頁驗證、默認游標、ANSINULL默認值、ANSI_NULLS已啟用、數據庫狀態等。通過SSMS配置數據庫選項3.4.1數據庫選項及設置3.4設置數據庫選項通過TSQL配置數據庫選項配置數據庫選項的TSQL語句與數據庫修改的語句相同，都是“ALTERDATABASE”。以下代碼修改數據庫“CateringN”的數據恢復模式為“SIMPLE”、并將自動收縮設置為“ON”。USEmasterGOALTERDATABASECateringNSETRECOVERYSIMPLE,AUTO_SHRINKONGO3.4.2查看數據庫信息1、應用系統視圖、函數查看數據庫信息系統視圖sys.databases可以查看數據庫的基本信息，包括各種選項的設置值。sys.database_files視圖，可以查看數據庫的文件信息。sys.filegroups視圖，可以查詢數據庫文件組信息。DATABASEPROPERTYEX函數可以查看數據庫的選項的值。select*fromsys.databasesGOselect*fromsys.database_filesGOselect*fromsys.filegroupsGOselectDATABASEPROPERTYEX('CateringMgn','Status')GO3.4.2查看數據庫信息2、應用系統存儲過程查看數據庫信息系統存儲過程sp_spaceused可以查看數據庫空間的使用情況。系統存儲過程Sp_helpdb可以查看數據庫的信息。USENetSaleGOsp_spaceusedGOsp_helpdbGOsp_helpdb

NetSaleGO

3.5管理數據庫3.5.1擴大數據庫空間1、手工擴大數據庫文件大小USEmasterGOALTERDATABASENetSale

ModifyFile(NAME=NetSale,size=15MB)2、設置數據庫自動增長方式在“數據庫屬性”對話框中，在“數據庫文件列表”中，選擇要調整自動增長方式的數據文件，單擊“自動增長”欄后的按鈕。在“更改自動增長設置”對話框中，選中“啟用自動增長”，設置文件增長方式和最大文件大小。3.5管理數據庫3.5.2收縮數據庫空間

1、收縮整個數據庫在“對象資源管理器”窗口中，展開服務器、數據庫節點。右擊待收縮的數據庫，在右鍵菜單中選擇“任務”→“收縮”→“數據庫”。在“收縮數據庫”對話框，選中“在釋放未使用的空間前重新組織文件，選中此項可能影響性能”，設置其下的“收縮后文件中的最大可用空間”，如為“50%”，即將數據庫的可用空間從原先的“72%”收縮到“50%”。DBCCSHRINKDATABASE(‘NetSale',20%)數據庫控制臺命令DatabaseConsoleCommand3.5管理數據庫3.5.2收縮數據庫空間

2、收縮指定的數據文件DBCCSHRINKFILE({file_name|file_id}{[,EMPTYFILE]|[[,target_size][,{NOTRUNCATE|TRUNCATEONLY}]]})[WITHNO_INFOMSGS]3.5管理數據庫3.5.3管理數據庫文件

1、增加數據庫文件ALTERDATABASENetSaleADDFILE（

NAME=NetSale_LOG_2,FILENAME='C:\data\NetSale_LOG_2.log',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB）3.5管理數據庫3.5.3管理數據庫文件2、刪除數據庫文件ALTERDATABASENetSaleREMOVEFILENetSale_LOG_23.5.4管理文件組SSMS管理文件組TSQL管理文件組ALTERDATABASENetSaleADDFILEGROUPNewUserFileGroup1GOALTERDATABASENetSaleADDFILE(NAME=NetSale_data4,FILENAME='C:\data\NetSale_data4.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)TOFILEGROUPNewUserFileGroup1GOALTERDATABASENetSaleMODIFYFILEGROUPNewUserFileGroupDEFAULTGO3.5.5刪除數據庫在SQLServerManagementStudio刪除數據庫的操作步驟如下1、在“對象資源管理器”窗口中，展開服務器、數據庫節點。右擊待刪除的數據庫，在右鍵菜單中選擇“刪除”。2、在“刪除對象”對話框中，可以選中“刪除數據庫備份和還原歷史記錄信息”，表示同時從系統數據庫msdb中刪除該數據庫的備份和還原的歷史記錄。選中“關閉現有連接”，表示斷開刪除前已建立的用戶與該數據庫的連接。3、單擊“確定”按鈕，該數據庫相關的數據文件和事務日志文件都會從系統中被刪除。刪除數據庫的TSQL語句為“DROPDATABASE”，語法如下：DROPDATABASE{database_name|atabase_snapshot_name}[,...n][;]例如DROPDATABASECateringN,CateringN1,CateringN23.5.6分離數據庫在SQLServerManagementStudio中分離數據庫的操作如下：1、在“對象資源管理器”窗口中，展開服務器、數據庫節點。右擊要分離的數據庫，在右鍵菜單中選擇“任務”→“分離”。2、在“分離數據庫”對話框中，可以看到“消息欄”當前有一個“活動連接”，即說明已有用戶連接到該數據。如果分離數據庫，需要選中“刪除連接”，此選項表示在分離時需要先把當前的“連接”刪除。選中“更新統計”表示當SQLServer在分離數據庫前更新狀態信息，如索引等，這樣不會丟失以前的全文索引等信息。3、單擊“確定”按鈕，分離數據庫。3.5.6分離數據庫分離數據庫的TSQL語句，需要調用系統存儲過程“sp_detach_db”sp_detach_db[@dbname=]'database_name'[,[@skipchecks=]'skipchecks'][,[@keepfulltextindexfile=]'KeepFulltextIndexFile']例如usemasterGosp_detach_db'CateringN'3.5.7附加數據庫在SQLServerManagementStudio中附加數據庫的操作步驟如下：1、在“對象資源管理器”窗口中，右擊“數據庫”節點，在右鍵菜單中選擇“附加”。2、在“附加數據庫”對話框中，可以看到“要附加的數據庫”和“數據庫詳細信息”列表都為空，需要添加數據庫文件。單擊“添加”按鈕。3、在“定位數據庫文件”對話框，找到并添加所要附加數據庫的主數據文件（MDF文件），然后單擊“確定”，返回到前一對話框中。4、此時，在“附加數據庫”對話框中會根據添加的主數據文件，更新“要附加的數據庫”列表和“數據庫詳細信息”列表，如圖3-21所示。5、確認信息無誤，單擊“確定”，系統執行數據庫附加操作。附加完成后，該數據庫會出現在“對象資源管理器”的數據庫節點下，如果未看到該數據庫，可單擊工具欄“刷新”按鈕，刷新數據庫節點。3.5.7附加數據庫數據庫附加操作還可以通過系統存儲過程“sp_attach_db”和“CREATEDATABASE…FORATTACH”

來實現，TSQL的語法如下：sp_attach_db[@dbname=]'dbname',[@filename1=]'filename_n'[,...16]例如usemasterGosp_attach_db@dbname=N'CateringN',@filename1=N'C:\data\CateringN_Data.mdf',@filename2=N'C:\data\CateringN_Data1.ndf',@filename3=N'C:\data\CateringN_Data2.ndf',@filename4=N'C:\data\CateringN_Log.ldf';usemasterGOCREATEDATABASESchoolON(FILENAME=‘d:\school.mdf'),(FILENAME=‘E:\school_Log.ldf')FORATTACH;GO3.5.8部分包含數據庫數據庫所包含的往往只是用戶的數據，而很多與數據庫相關的登錄名、元數據等內容一般保存在系統數據庫中，這些數據無法通過用戶數據庫的分離附加或備份還原直接遷移過去，還需要其他額外工作來完成。部分包含數據庫（ContainedDatabase）是SQLServer用于解決這一問題的特性，其主要作用是提高數據庫在多個數據庫引擎服務實例中遷移時的便捷性。通過將用戶數據庫啟用部分包含特性，可以將數據庫用戶等數據保存在用戶數據庫中，從而可以最大限度遷移數據庫的完整信息。同時，部分包含數據庫還可以與AlwaysOn結合，提高系統管理和維護的可靠性與高可用性。3.5.8部分包含數據庫1.啟用部分包含數據庫部分包含數據庫是服務器級的選項，要啟用此特性，需要在服務器上啟用此選項。啟用部分包含數據庫，可以通過SQLServerManagementStudio和T-SQL來實現。sp_configure'showadvancedoptions'1,GOsp_configure'containeddatabaseauthentication',1;GORECONFIGURE;GO3.5.8部分包含數據庫2.數據庫設置數據庫要開啟部分包含的特性，需要在數據庫選項中做對應設置。設置數據庫的此項選項也可以通過SQLServerManagementStudio完成。操作步驟如下：（1）在“對象資源管理器”窗口中，展開服務器，選