ALTER DATABASE (Transact-SQL)

修改資料庫或與資料庫相關聯的檔案和檔案群組。 在資料庫中新增或移除檔案和檔案群組、變更資料庫或其檔案和檔案群組的屬性、變更資料庫定序，以及設定資料庫選項。 無法修改資料庫快照集。 若要修改與複寫相關聯的資料庫選項，請使用 sp_replicationdboption。

適用於：SQL Server (SQL Server 2008 到目前的版本)、Azure SQL Database。

由於長度的關係，ALTER DATABASE 語法會分成下列主題：

• ALTER DATABASE
  目前的主題會提供變更資料庫名稱和定序的語法。

• ALTER DATABASE 檔案及檔案群組選項
  提供在資料庫中新增和移除檔案及檔案群組的語法，以及變更檔案及檔案群組之屬性的語法。

• ALTER DATABASE SET 選項
  提供使用 ALTER DATABASE 的 SET 選項來變更資料庫屬性的語法。

• ALTER DATABASE 資料庫鏡像
  提供與資料庫鏡像相關之 ALTER DATABASE SET 選項的語法。

• ALTER DATABASE SET HADR
  為 ALTER DATABASE 的 AlwaysOn 可用性群組 選項，提供在 AlwaysOn 可用性群組的次要複本上設定次要資料庫的語法。

• ALTER DATABASE 相容性層級
  提供與資料庫相容性層級相關之 ALTER DATABASE SET 選項的語法。

Transact-SQL 語法慣例

語法

-- SQL Server Syntax
ALTER DATABASE { database_name  | CURRENT }
{
    MODIFY NAME = new_database_name 
  | COLLATE collation_name
  | <file_and_filegroup_options>
  | <set_database_options>
}
[;]

<file_and_filegroup_options >::=
  <add_or_modify_files>::=
  <filespec>::= 
  <add_or_modify_filegroups>::=
  <filegroup_updatability_option>::= 

<set_database_options>::=
  <optionspec>::= 
  <auto_option> ::= 
  <change_tracking_option> ::=
  <cursor_option> ::= 
  <database_mirroring_option> ::= 
  <date_correlation_optimization_option> ::=
  <db_encryption_option> ::=
  <db_state_option> ::=
  <db_update_option> ::=
  <db_user_access_option> ::= 
  <delayed_durability_option> ::=  <external_access_option> ::=
  <FILESTREAM_options> ::=
  <HADR_options> ::=  
  <parameterization_option> ::=
  <recovery_option> ::= 
  <service_broker_option> ::=
  <snapshot_option> ::=
  <sql_option> ::= 
  <termination> ::=

-- Azure SQL Database Syntax
ALTER DATABASE database_name 
{
    MODIFY NAME =new_database_name
  | MODIFY ( <edition_options> [, ... n] ) 
  | SET { <set_database_options> } 
}

<edition_options> ::= 
{
      MAXSIZE = { 100 MB | 500 MB |1 | 5 | 10 | 20 | 30 … 150 … 500 } GB  
    | EDITION = { 'web' | 'business' | 'basic' | 'standard' | 'Premium' } 
    | SERVICE_OBJECTIVE = { 'shared' | 'basic' | 'S0' | 'S1' | 'S2' | 'P1' | 'P2' | 'P3' } 
}

<set_database_options> ::= 
    <db_update_option>
<db_update_option> ::= 
    { READ_ONLY | READ_WRITE }
 [;]

引數

• database_name
  這是要修改之資料庫的名稱。

  注意

  自主資料庫無法使用這個選項。

• CURRENT

  適用於：SQL Server 2012 到 SQL Server 2014。

  指定應該改變正在使用中的目前資料庫。

• MODIFY NAME **=**new_database_name
  利用指定為 new_database_name 的名稱來重新命名資料庫。

• COLLATE collation_name

  適用於：SQL Server 2008 到 SQL Server 2014。

  指定資料庫的定序。 collation_name 可以是 Windows 定序名稱或 SQL 定序名稱。 若未指定，就會將 SQL Server 執行個體的定序指派給資料庫。

  如需有關 Windows 和 SQL 定序名稱的詳細資訊，請參閱＜COLLATE (Transact-SQL)＞。

• MODIFY (MAXSIZE = [100 MB | 500 MB | 1 | 5 | 10 | 20 | 30 … 150…500] GB)

  適用於：Azure SQL Database

  指定資料庫的大小上限。 大小上限必須符合資料庫的有效 EDITION 屬性值集合。 變更資料庫的大小上限可能也會造成資料庫版本變更。 下表列出 SQL 資料庫 服務層支援的 MAXSIZE 值與預設值 (D)：

  MAXSIZE	Web	Business	Basic	Standard	Premium
  100 MB	√		√	√	√
  500 MB			√	√	√
  1 GB	√ (D)		√	√	√
  2 GB			√ (D)	√	√
  5 GB	√			√	√
  10 GB		√ (D)		√	√
  20 GB		√		√	√
  30 GB		√		√	√
  40 GB		√		√	√
  50 GB		√		√	√
  100 GB		√		√	√
  150 GB		√		√	√
  200 GB				√	√
  250 GB				√ (D)	√
  300 GB					√
  400 GB					√
  500 GB					√ (D)

  以下規則會套用到 MAXSIZE 和 EDITION 引數：

  • 如果指定 MAXSIZE 值的話，它必須是上表所示的有效值。

  • 如果 MAXSIZE 設定為小於 5 GB 的值而且未指定 EDITION，則資料庫版本將會自動設定為 Web Edition。

  • 如果 MAXSIZE 設定為大於 5 GB 的值而且未指定 EDITION，則資料庫版本將會自動設定為 Business Edition。

  • 如果指定了 EDITION 但是未指定 MAXSIZE，就會使用版本的預設值。 例如，如果 EDITION 設定為 Standard 而且未指定 MAXSIZE，則 MAXSIZE 會自動設定為 500 MB。

  • 如果 MAXSIZE 和 EDITION 皆未指定，則 EDITION 會設定為 Web 而 MAXSIZE 則設定為 1 GB。

• MODIFY (EDITION = [ 'web' | 'business' | 'basic' | 'standard' | 'premium' ] )

  適用於：Azure SQL Database

  變更資料庫的版本。 SQL 資料庫 服務層可以使用 EDITION 參數加以設定或修改。 如果資料庫的 MAXSIZE 屬性設定的值超出該版本所支援的有效範圍，則 EDITION 變更將會失敗。

  重要事項
  Business 及 Web 服務層將於 2015 年 9 月淘汰。如需詳細資訊，請參閱＜Web 和 Business 常見問題集＞。

• SERVICE_OBJECTIVE

  適用於：Azure SQL Database

  指定效能等級。 如需服務目標描述和有關大小、版本及服務目標組合的詳細資訊，請參閱 Azure SQL Database 服務層和效能層級。 如果 EDITION 不支援指定的 SERVICE_OBJECTIVE，將會收到錯誤。 若要將 SERVICE_OBJECTIVE 值從某一層變更為另一層 (例如，從 S1 到 P1)，您還必須變更 EDITION 值。

• <db_update_option> ::=

  適用於：Azure SQL Database

  控制是否允許更新資料庫。

  { READ_ONLY | READ_WRITE }

  • READ_ONLY
    使用者可以從資料庫中讀取資料，但無法加以修改。

  • READ_WRITE
    資料庫可以執行讀寫作業。

  注意

  在 SQL 資料庫同盟資料庫上，SET { READ_ONLY | READ_WRITE } 會停用。

<delayed_durability_option> ::=

適用於：SQL Server 2014 到 SQL Server 2014。

如需詳細資訊，請參閱＜ALTER DATABASE SET 選項 (Transact-SQL)＞和＜控制交易持久性＞。

<file_and_filegroup_options >::=

如需詳細資訊，請參閱＜ALTER DATABASE 檔案及檔案群組選項 (Transact-SQL)＞。

<set_database_options >::=

如需詳細資訊，請參閱＜ALTER DATABASE SET 選項 (Transact-SQL)＞、＜ALTER DATABASE 資料庫鏡像 (Transact-SQL)＞、＜ALTER DATABASE SET HADR (Transact-SQL)＞和＜ALTER DATABASE 相容性層級 (Transact-SQL)＞。

備註

若要移除資料庫，請使用 DROP DATABASE。

若要縮小資料庫大小，請使用 DBCC SHRINKDATABASE。

ALTER DATABASE 陳述式必須執行自動認可模式 (預設的交易管理模式)，且不能在明確或隱含的交易中。

資料庫檔案狀態 (如線上或離線) 的維護與資料庫狀態無關。 如需詳細資訊，請參閱＜檔案狀態＞。 檔案群組內的檔案狀態決定了整個檔案群組的可用性。 若要使某個檔案群組為可用的，則在檔案群組中的所有檔案必須都在線上。 如果檔案群組離線，SQL 陳述式存取檔案群組的任何嘗試都會失敗，且會出現錯誤。 當您建置 SELECT 陳述式的查詢計劃時，查詢最佳化工具會避開在離線檔案群組中的非叢集索引和索引檢視表。 這樣會讓這些陳述式能夠執行成功。 不過，如果離線檔案群組包含目標資料表的堆積或叢集索引，SELECT 陳述式將會失敗。 除此之外，在離線檔案群組中，以 INSERT、UPDATE 或 DELETE 陳述式修改含有索引的資料表將會失敗。

當資料庫處於 RESTORING 狀態時，大部分的 ALTER DATABASE 陳述式都會失敗。 設定資料庫鏡像選項例外。 在使用中的還原作業期間，或是由於備份檔損毀導致資料庫或記錄檔的還原作業失敗時，資料庫都有可能處於 RESTORING 狀態。

設定下列其中一個選項，可清除 SQL Server 執行個體的計畫快取。

OFFLINE	READ_WRITE
ONLINE	MODIFY FILEGROUP DEFAULT
MODIFY_NAME	MODIFY FILEGROUP READ_WRITE
COLLATE	MODIFY FILEGROUP READ_ONLY
READ_ONLY	PAGE_VERIFY

清除計畫快取會導致重新編譯所有後續執行計畫，而且可能會導致查詢效能突然暫時下降。 針對計畫快取中每個清除的快取存放區，SQL Server 錯誤記錄檔包含下列資訊訊息："因為某些資料庫維護或重新設定作業，SQL Server 發生 %d 次 '%s' 快取存放區的快取存放區排清 (計畫快取的一部分)。" 只要在該時間間隔內快取發生排清，這個訊息就會每五分鐘記錄一次。

在下列情況下也會排清程序快取：

• 資料庫將 AUTO_CLOSE 資料庫選項設定為 ON。 當沒有任何使用者連接參考或使用資料庫時，背景工作嘗試關閉並自動關閉資料庫。

• 您針對有預設選項的資料庫執行幾個查詢。 然後卸除資料庫。

• 卸除來源資料庫的資料庫快照集。

• 您已成功重建資料庫的交易記錄。

• 您還原資料庫備份。

• 您卸離資料庫。

變更資料庫定序

將不同定序套用至資料庫之前，請確定已符合下列條件：

1. 您是資料庫目前唯一的使用者。

2. 沒有結構描述繫結的物件相依於資料庫的定序。

   如果相依於資料庫定序的下列物件存在於資料庫中，ALTER DATABASEdatabase_nameCOLLATE 陳述式將會失敗。 SQL Server 將會針對每一個封鎖 ALTER 動作的物件傳回錯誤訊息：

   • 利用 SCHEMABINDING 來建立的使用者定義函數和檢視表。

   • 計算資料行。

   • CHECK 條件約束。

   • 傳回包含字元資料行之資料表的資料表值函式，該資料行的定序繼承自預設資料庫定序。

   變更資料庫定序時，就會自動更新非結構描述繫結實體的相依性資訊。

變更資料庫定序並不會在資料庫物件的任何系統名稱之間建立複本。 如果變更的定序產生重複名稱，下列命名空間可能會使資料庫定序的變更失敗：

• 物件名稱，如程序、資料表、觸發程序或檢視表。

• 結構描述名稱。

• 群組、角色或使用者之類的主體。

• 純量類型名稱，如系統和使用者定義類型。

• 全文檢索目錄名稱。

• 物件內的資料行或參數名稱。

• 資料表內的索引名稱。

新定序所造成的重複名稱會使變更動作失敗，SQL Server 會傳回錯誤訊息，指出出現重複名稱的命名空間。

檢視資料庫資訊

您可以利用目錄檢視、系統函數和系統預存程序，以傳回資料庫、檔案和檔案群組的相關資訊。

Permissions

SQL Server

需要資料庫的 ALTER 權限。

Azure SQL Database

只有伺服器層級主體登入 (由佈建程序所建立) 或 dbmanager 資料庫角色成員可以改變資料庫。

安全性注意事項
資料庫的擁有者不能改變資料庫，除非他們是 dbmanager 角色的成員。

範例

A.變更資料庫的名稱

下列範例會將 AdventureWorks2012 資料庫的名稱變更為 Northwind。

USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO

B.變更資料庫的定序

下列範例會使用 SQL_Latin1_General_CP1_CI_AS 定序來建立名為 testdb 的資料庫，然後將 testdb 資料庫的定序變更為 COLLATE French_CI_AI。

適用於：SQL Server 2008 到 SQL Server 2014。

USE master;
GO

CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

請參閱

參考

CREATE DATABASE (SQL Server Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DROP DATABASE (Transact-SQL)

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

EVENTDATA (Transact-SQL)

sp_configure (Transact-SQL)

sp_spaceused (Transact-SQL)

sys.databases (Transact-SQL)

sys.database_files (Transact-SQL)

sys.database_mirroring_witnesses (Transact-SQL)

sys.data_spaces (Transact-SQL)

sys.filegroups (Transact-SQL)

sys.master_files (Transact-SQL)

概念

系統資料庫