3.3 管理數據庫

3.3.1 查看數據庫狀態信息

在實際生產過程中的數據庫總是處于一個特定的狀態下，若要確認數據庫的當前狀態，通過“數據庫屬性”窗口的“常規”選項卡查看數據庫屬性以外，還可以選擇sys.databases目錄視圖中的state_desc列。在查詢設計器窗口中輸入以下代碼并執行，如圖3-14所示。

      Select name,state,state_desc Fromsys.databases

在SQL Server 2016中，數據庫文件的狀態獨立于數據庫的狀態。文件始終處于一個特定狀態，若要查看文件的當前狀態，可使用sys.master_files或sys.database_files目錄視圖。如果數據庫處于離線狀態，則可以從sys.master_files目錄視圖中查看文件的狀態，如圖3-15所示。

可以在查詢設計器窗口中輸入以下代碼并執行，即可查看到相關數據文件的狀態信息。

      Select name,physical_name,type,type_desc,state,state_desc
      Fromsys.master_files

1．數據庫狀態含義

ONLINE：表示可以對數據庫進行訪問。即使可能尚未完成恢復的撤銷階段，主文件組仍處于在線狀態。

OFFLINE：表示數據庫無法使用。數據庫由于顯式的用戶操作而處于離線狀態，并保持離線狀態直至執行了其他的用戶操作。

RESTORING：表示正在還原主文件組的一個或多個文件，或正在離線還原一個或多個輔助文件，此時數據庫不可用。

RECOVERING：表示正在恢復數據庫。恢復進程是一個暫時性狀態，恢復成功后數據庫將自動處于在線狀態。如果恢復失敗，數據庫將處于可疑狀態。此時數據庫不可用。

RECOVERY PENDING：表示SQL Server在恢復過程中遇到了與資源相關的錯誤，數據庫未損壞，但是可能缺少文件，或系統資源限制可能導致無法啟動數據庫，此時數據庫不可用。需要用戶另外執行操作來解決問題，并讓恢復進程完成。

SUSPECT：表示至少主文件組可疑或可能已損壞。在SQL Server啟動過程中無法恢復數據庫，此時數據庫不可用。需要用戶另外執行操作來解決問題。

EMERGENCY：表示用戶更改了數據庫，并將其狀態設置為EMERGENCY。數據庫處于單用戶模式，可以修復或還原。數據庫標記為READ_ONLY，禁用日志行，并且僅限sysadmin固定服務器角色的成員訪問。EMERGENCY主要用于故障排除。

2．數據庫文件狀態含義

ONLINE：表示文件可用于所有操作。如果數據庫本身處于在線狀態，則主文件組中的文件始終處于在線狀態。如果主文件組中的文件處于離線狀態，則數據庫將處于離線狀態，并且輔助文件的狀態未定義。

OFFLINE：表示文件不可訪問，并且可能不顯示在磁盤中。文件通過顯式用戶操作變為離線，并在執行其他用戶操作之前保持離線狀態。注意：當文件已損壞時，該文件僅應設置為離線狀態，但可以進行還原。設置為離線的文件只能通過從備份還原才能設置為在線狀態。

RESTORING：表示正在還原文件。文件處于還原狀態，并且在還原完成及文件恢復之前，一直保持此狀態。

RECOVERY PENDING：表示文件恢復被推遲。由于在段落還原過程中未還原和恢復文件，因此文件將自動進入此狀態。需要用戶執行其他操作來解決該錯誤，并允許完成恢復過程。

SUSPECT：表示在線還原過程中恢復文件失敗。如果文件位于主文件組，則數據庫還將標記為可疑；否則，僅文件處于可疑狀態，而數據庫仍處于在線狀態。

DEFUNCT：表示當文件不處于在線狀態時被刪除。刪除離線文件組后，文件組中的所有文件都將失效。

3.3.2 數據庫的屬性設置

通過SQL Server Management Studio可以查看數據庫文件的物理文件及相關屬性。從3.2.2節的例題可知，利用命令修改了部分數據庫屬性，下面再對其他一些數據庫屬性做進一步設置。

1．數據庫更名

更改數據庫的名稱可以采用兩種方法：一種方法是在SQL Server Management Studio中選中此數據庫，右擊鼠標，在彈出的快捷菜單中選擇“重命名”命令，或者直接利用ALTER DATABASE命令來實現；另一種方法是使用系統存儲過程sp_renamedb更改數據庫的名稱。在重命名數據庫之前，應該確保沒有用戶正在使用該數據庫。

系統存儲過程sp_renamedb語法格式如下：

      sp_renamedb [@dbname=]'old_name',[@newname=]'new_name'

【例3-6】 將名為student的數據庫改名為STUDENTDB。

程序代碼如下：

      ALTER DATABASE student
      MODIFY NAME = STUDENTDB

2．限制用戶對數據庫的訪問

在SQL Server 2016的運行過程中，有時需要限制用戶的訪問，如管理員要維護數據庫、系統需要升級等，這時可設置限定只能由特定用戶訪問數據庫。

在數據庫test01的“數據庫屬性”對話框中選擇“選項”選項卡，如圖3-16所示。選擇“狀態”→“限制訪問”下拉列表框，出現3個選項。

①Multiple：數據庫處于正常生產狀態，允許多個用戶同時訪問數據庫。

②Single：指定一次只能允許一個用戶訪問，其他用戶的連接被中斷。

③Restricted：限制除db_ower（數據庫所有者）、dbcreator（數據庫創建者）和sysadmin（系統管理員）以外的角色成員訪問數據庫，但對數據庫的連接不加限制。一般在維護數據庫時將數據庫設置為該狀態。

3．修改數據庫的排序規則

前面的例3-4是利用命令方式更改數據庫的排序規則，下面介紹如何利用可視化方式修改排序規則。同樣是在圖3-16所示的“選項”選項卡內，利用“排序規則”下拉列表框可以設置數據庫采用的排序規則，如圖3-17所示。

（1）了解排序規則的含義。SQL Server 2016的排序規則指定了字符的物理存儲模式，以及存儲和比較字符的規則。以Chinese_PRC_CS_AI_WS為例，該規則可以分成兩部分來理解。前半部分指排序規則所支持的字符集，如Chinese_PRC_表示對簡體字UNICODE的排序規則；后半部分常見的組合含義如下。

①_BIN：二進制排序。

②_CI（CS）：是否區分大小寫，CI不區分，CS區分。

③_AI（AS）：是否區分重音，AI不區分，AS區分。

④_KI（KS）：是否區分假名類型，KI不區分，KS區分。

⑤_WI（WS）：是否區分寬度，WI不區分，WS區分。

（2）排序規則的層次。SQL Server 2016的排序規則分為3個層次，即服務器排序規則、數據庫排序規則和表的排序規則。

當排序規則在層次之間發生沖突時，以低層次、細粒度為準。假如服務器的排序規則和數據庫的排序規則不一致，在數據庫中自然以數據庫的排序規則為準。

4．更改數據庫所有者

（1）在數據庫屬性窗體中選擇“文件”選項卡，然后單擊“所有者”文本框后面的…□按鈕，則會彈出“選擇數據庫所有者”對話框，如圖3-18所示。

（2）單擊“瀏覽”按鈕，則會彈出“查找對象”對話框，如圖3-19所示。

（3）在“匹配的對象”列表框中選擇數據庫所有者，單擊“確定”按鈕即可實現更改數據庫所有者的操作。如果是附加的數據庫，可以通過此項操作實現數據庫所有者的更改，以此獲得更多的權限。

3.3.3 估算數據庫大小

SQL Server 2016文件可以從它們最初指定的大小開始自動增長。如果文件組中有多個文件，則它們在所有文件被填滿之前不會自動增長，填滿后這些文件會循環增長。

如果SQL Server作為數據庫嵌入某應用程序，而該應用程序的用戶無法迅速與系統管理員聯系，則此功能就特別有用。用戶可以使文件根據需要自動增長，以減輕監視數據庫中的可用空間和手動分配額外空間的管理負擔。

1．影響數據庫大小的因素

創建一個數據庫時，SQL Server會創建一份包括系統表的model數據庫的副本。系統表包含文件、對象、權限和限制的相關信息。在數據庫中新建對象時，這些系統表會有所增長。每創建一個對象，就有一個新行生成并插入到一個或多個系統表中。因此，估計數據庫的大小需要考慮以下因素。

（1）model數據庫和系統表的大小，包括預測到的增長。

（2）表中數據的總量，包括預測到的增長。

（3）索引的數量和大小，特別是鍵值的大小、行的數量和填充因子的設置。

（4）影響事務日志大小的因素，更改活動的總量和頻率，每一個事務的大小以及備份日志的頻率。

（5）系統表的大小，如用戶和對象的數量等，不過這不是影響數據庫大小的主要因素。

對于聯機事務處理（OLTP），一般要為事務日志分配數據庫10%～25%的空間，而主要用于查詢的數據庫可以設置的事務日志空間較小些。

2．估計表中數據的總量

在確定分配給數據庫的空間大小后，應該估計表中數據的總量，包括預測到的增長。可以通過計算行的總數、大小、一個空間頁中合適的行數以及數據庫中表的頁數得到結果。如果知道每行的字符數和表中行的近似數量，就能夠估計表所需的頁數和表占用的磁盤空間。具體可以采用以下方法。

（1）通過統計每列包含的字節數，計算一行的字節數量。對于列中定義為可變長度，可以采用取平均值的方法估算。

（2）確定平均每一個數據頁包含行的數目。用8060除以一行的字節數，取整即可得到結果。

（3）表中行的近似數目除以一個數據頁包含的行數，結果就是需要存儲到表中的頁數。

3.3.4 收縮數據庫

在SQL Server 2016中，當為數據庫分配的磁盤空間過大時，可以收縮數據庫，以節省存儲空間。數據文件和事務日志文件都可以進行收縮。數據庫也可設置為按給定的時間間隔自動收縮。該活動在后臺進行，不影響數據庫內的用戶活動。

1．設置自動收縮數據庫

設置數據庫的自動收縮，可以在數據庫的“屬性”中“選項”選項卡頁面中設置，只要將選項中的“自動收縮”設置為True即可。

2．手動收縮數據庫

手動收縮用戶數據庫的步驟如下。

（1）在SQL Server Management Studio中，右擊相應的數據庫，如test01，從彈出的快捷菜單中依次選擇“任務”→“收縮”→“數據庫”命令。

（2）在彈出的對話框中進行設置，如圖3-20所示。數據庫test01的當前分配空間為20MB，設置收縮后的最大空間為37%，單擊“確定”按鈕，即可完成操作。系統將根據數據庫的具體情況對其進行收縮。

（3）如果單擊“腳本”按鈕，系統還能夠將收縮操作的腳本顯示到“新建查詢”界面中，結果如下：

      USE[test01]
      GO
      DBCC SHRINKDATABASE(N'test01',37)
      GO

3．手動收縮數據庫文件

手動收縮用戶數據庫文件的步驟如下。

（1）在SQL Server Management Studio中，右擊相應的數據庫，如test01，從彈出的快捷菜單中選擇“任務”→“收縮”→“文件”命令。

（2）在彈出的對話框中進行設置，如圖3-21所示。數據庫test01的數據文件當前分配空間為8MB，設置收縮數據庫文件參數，將該文件收縮為6MB，單擊“確定”按鈕，即可完成操作。

從前面的操作中可以看出，使用Transact-SQL語句中的DBCC SHRINKDATABASE命令可以收縮數據庫，同樣，使用DBCC SHRINKFILE命令可以收縮數據庫文件。代碼如下：

      USE test01
      DBCC SHRINKFILE(N'test01',6)
      GO

3.3.5 分離和附加用戶數據庫

在SQL Server 2016中，除了系統數據庫外，其他數據庫都可以從服務器的管理中進行分離，以脫離服務器的管理，同時保持數據文件與日志文件的完整性和一致性。分離出來的數據庫可以附加到其他SQL Server服務器上，構成完整的數據庫。分離和附加是系統開發過程中的重要操作。

1．分離用戶數據庫

（1）在SQL Server Management Studio中，右擊相應的數據庫，如test02，從彈出的快捷菜單中依次選擇“任務”→“分離”命令。

（2）在彈出的對話框中進行設置，如圖3-22所示。設置數據庫test02的分離參數，單擊“確定”按鈕，即可完成操作。

其中的主要參數項含義如下。

①刪除連接：是否斷開與指定服務器的連接。

②更新統計信息：選擇在分離數據庫之前是否更新過時的優化統計信息。

③狀態：顯示數據庫分離前是否“就緒”或“未就緒”。

④消息：是否成功的消息。

2．附加數據庫

附加數據庫可以將已經分離的數據庫重新附加到當前或其他SQL Server 2016的實例。

（1）在SQL Server Management Studio中，右擊“對象資源管理器”中的“數據庫”選項，從彈出的快捷菜單中選擇“附加”命令。

（2）在彈出的“附加數據庫”對話框中單擊“添加”按鈕，目的是將要附加數據庫的主數據文件添加到實例。在彈出的“定位數據庫文件”對話框中選擇要添加的數據庫的主數據文件，如圖3-23所示。數據庫test02的主數據文件為test02.mdf。

（3）單擊“確定”按鈕，返回“附加數據庫”對話框，如圖3-24所示。單擊“確定”按鈕，數據庫test02就附加到當前的實例中了。

3.3.6 聯機和脫機用戶數據庫

脫機操作可以使某個用戶數據庫暫停服務，聯機可以使某個用戶數據庫提供服務。

1．脫機用戶數據庫

（1）在SQL Server Management Studio中，右擊相應的數據庫，如test02，從彈出的快捷菜單中依次選擇“任務”→“脫機”命令，彈出圖3-25所示的對話框。

（2）完成脫機過程后，單擊“關閉”按鈕。系統中將數據庫標注為。

2．聯機用戶數據庫

（1）在SQL Server Management Studio中，右擊已經脫機的數據庫，從彈出的快捷菜單中依次選擇“任務”→“聯機”命令，彈出圖3-26所示的對話框。

（2）完成聯機過程后，單擊“關閉”按鈕，系統中將數據庫恢復原樣。

3.3.7 刪除數據庫

當系統中有不再需要的用戶數據庫時，用戶可以根據自己的權限選擇將其刪除。數據庫刪除之后，數據庫的文件及其數據都從服務器上的磁盤中刪除。數據庫的刪除是永久性的，并且如果不使用以前的備份，則無法檢索該數據庫。

在SQL Server 2016中，可以使用SQL Server Management Studio與Transact-SQL語句來刪除數據庫。

1．使用SQL Server Management Studio刪除數據庫

啟動SQL Server Management Studio界面，連接到本地數據庫默認實例。在“對象資源管理器”中展開樹形目錄，定位到要刪除的數據庫，右擊該數據庫，選擇快捷菜單中的“刪除”命令，如圖3-27所示，刪除數據庫student。確認選擇了正確的數據庫，在彈出的對話框中再單擊“確定”按鈕。若刪除了數據庫，則不能恢復其內容。

2．使用Transact-SQL語句刪除數據庫

Transact-SQL提供了數據庫修改語句DROP DATABASE。具體格式如下：

      DROP DATABASE { database_name } [ ,…n] [;]

其中，database_name為指定要刪除的數據庫名稱。該命令可以一次刪除一個或多個數據庫。

【例3-7】 刪除已創建的數據庫student。

程序代碼如下：

      DROP DATABASE student
      GO

若要執行DROP DATABASE操作，用戶至少須對數據庫具有CONTROL權限。執行刪除數據庫操作會從SQL Server實例中刪除數據庫，并刪除該數據庫使用的物理磁盤文件。