ALTER DATABASE (Transact-SQL)

データベース、またはそのデータベースに関連付けられているファイルおよびファイル グループを変更します。 データベースに対するファイルやファイル グループの追加と削除、データベースおよびデータベースのファイルやファイル グループの属性の変更、データベースの照合順序の変更、データベース オプションの設定を行えます。 データベース スナップショットは変更できません。 レプリケーションに関連するデータベース オプションを変更するには、sp_replicationdboption を使用してください。

適用対象:SQL Server (SQL Server 2008 から現在のバージョン)、Azure AQL データベース。

解説が長くなるため、ALTER DATABASE の構文は次の各トピックに分けて説明しています。

• ALTER DATABASE
  このトピックでは、データベースの名前と照合順序を変更するための構文について説明します。

• ALTER DATABASE の File および Filegroup オプション
  データベースのファイルおよびファイル グループを追加したり削除したりするための構文のほか、ファイルおよびファイル グループの属性を変更するための構文について説明します。

• ALTER DATABASE の SET オプション
  ALTER DATABASE の SET オプションを使ってデータベースの属性を変更するための構文について説明します。

• ALTER DATABASE データベース ミラーリング
  ALTER DATABASE のデータベース ミラーリングに関連した SET オプションの構文について説明します。

• ALTER DATABASE SET HADR
  AlwaysOn 可用性グループのセカンダリ レプリカ上のセカンダリ データベースを構成するための、ALTER DATABASE の 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 AQL データベース

  データベースの最大サイズを指定します。 最大サイズは、データベースの EDITION プロパティの有効な値セットに準拠している必要があります。 データベースの最大サイズを変更すると、データベースの EDITION も変更される場合があります。 次の表では、SQL データベース サービス層でサポートされる MAXSIZE 値と既定値 (D) を示します。

  MAXSIZE	Web	ビジネス	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 に設定されます。

  • MAXSIZE が 5 GB より大きい値に設定されていて、EDITION が指定されていない場合、データベースのエディションは自動的に Business に設定されます。

  • EDITION が指定され、MAXSIZE が指定されていない場合は、エディションの既定値が使用されます。 たとえば、EDITION が Standard に設定されていて、MAXSIZE が指定されていない場合、MAXSIZE は自動的に 500 MB に設定されます。

  • MAXSIZE も EDITION も指定されていない場合、EDITION は Web に設定され、MAXSIZE は 1 GB に設定されます。

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

  適用対象:Azure AQL データベース

  データベースのエディションを変更します。 SQL データベース サービス層は、EDITION パラメーターを使用して設定または変更できます。 データベースの MAXSIZE プロパティがそのエディションでサポートされる有効な範囲内の値に設定されていない場合、EDITION の変更は失敗します。

  重要

  ビジネス サービス層および Web サービス層は 2015 年 9 月で廃止されます。詳細については、Web Edition と Business Edition に関する FAQ を参照してください。

• SERVICE_OBJECTIVE

  適用対象:Azure AQL データベース

  パフォーマンス レベルを指定します。 サービス目標に関する説明およびサイズ、エディション、サービス目標の組み合わせの詳細については、「Azure SQL データベースのサービス階層とパフォーマンス レベル」を参照してください。 指定した SERVICE_OBJECTIVE が EDITION によってサポートされていない場合は、エラーが返されます。 SERVICE_OBJECTIVE の値を 1 つの階層から別の階層に変更する場合 (たとえば、S1 から P1) は、EDITION の値も変更する必要があります。

• <db_update_option> ::=

  適用対象:Azure AQL データベース

  データベースで更新を許可するかどうかを制御します。

  { 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 の File および Filegroup オプション (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' キャッシュストア (プラン キャッシュの一部) に対して検出しました。 このメッセージは、5 分以内にキャッシュがフラッシュされる限り、5 分間隔でログに記録されます。

プロシージャ キャッシュは、次のシナリオでもフラッシュされます。

• AUTO_CLOSE データベース オプションが ON に設定されている。 データベースを参照 (または使用) するユーザー接続が 1 つも存在しない場合、バックグラウンド タスクがデータベースを自動的に閉じてシャットダウンすることを試みます。

• 既定のオプションが設定されているデータベースに対して複数のクエリを実行した。 データベースはその後削除されます。

• ソース データベースのデータベース スナップショットが削除された。

• データベースのトランザクション ログを正常に再構築した。

• データベースのバックアップを復元した。

• データベースをデタッチした。

データベースの照合順序の変更

データベースに別の照合順序を適用する前に、次の条件が満たされているかどうかを確認してください。

1. 現在データベースを使用しているのは、1 人だけである。

2. データベースの照合順序に依存するスキーマ バインド オブジェクトがない。

   データベースの照合順序に依存する次のオブジェクトがデータベース内に存在する場合、ALTER DATABASEdatabase_nameCOLLATE ステートメントは失敗します。 SQL Server は、ALTER アクションをブロックしているオブジェクトごとにエラー メッセージを返します。

   • SCHEMABINDING を指定して作成されたユーザー定義関数およびビュー

   • 計算列

   • CHECK 制約

   • 既定のデータベース照合順序から継承した照合順序を持つ文字型列がテーブルにある場合に、そのテーブルを返すテーブル値関数

   非スキーマ バインド エンティティの依存関係情報は、データベースの照合順序が変更されると自動的に更新されます。

データベースの照合順序を変更しても、データベース オブジェクトのシステム名に重複は発生しません。 照合順序の変更によって名前が重複する場合、次の名前空間が原因でデータベースの照合順序の変更が失敗することがあります。

• プロシージャ、テーブル、トリガー、ビューなどのオブジェクト名

• スキーマ名

• グループ、ロール、ユーザーなどのプリンシパル

• システム データ型、ユーザー定義データ型などのスカラー データ型の名前

• フルテキスト カタログ名

• オブジェクト内の列名またはパラメーター名

• テーブル内のインデックス名

新しい照合順序によって名前が重複すると、変更操作が失敗し、SQL Server は重複が見つかった名前空間を示すエラー メッセージを返します。

データベース情報の表示

カタログ ビュー、システム関数、およびシステム ストアド プロシージャを使用して、データベース、ファイルおよびファイル グループについての情報を返すことができます。

権限

SQL Server

データベースに対する ALTER 権限が必要です。

Azure AQL データベース

データベースを変更できるのは、(準備プロセスによって作成される) サーバーレベルのプリンシパルのログイン、または 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)

概念

システム データベース