ALTER DATABASE の File および Filegroup オプション (Transact-SQL)

  • [アーティクル]

データベースに関連付けられているファイルおよびファイル グループを変更します。 データベースに対するファイルやファイル グループの追加と削除、およびデータベースおよびデータベースのファイルやファイル グループの属性の変更を行います。 その他の ALTER DATABASE オプションについては、「ALTER DATABASE (Transact-SQL)」を参照してください。

トピック リンク アイコン Transact-SQL 構文表記規則

構文

ALTER DATABASE database_name 
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ] 
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ] 
  | REMOVE FILE logical_file_name 
  | MODIFY FILE <filespec>
}

<filespec>::=  
(
    NAME = logical_file_name  
    [ , NEWNAME = new_logical_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| % ] ] 
    [ , OFFLINE ]
)  

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name 
        [ CONTAINS FILESTREAM ]
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option> 
        | DEFAULT
        | NAME = new_filegroup_name 
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE } 
    | { READ_ONLY | READ_WRITE }
}

引数

<add_or_modify_files>::=

追加、削除、または変更するファイルを指定します。

  • database_name
    変更するデータベースの名前を指定します。

  • ADD FILE
    データベースにファイルを追加します。

    • TO FILEGROUP { filegroup_name }
      指定されたファイルを追加するファイル グループを指定します。 現在のファイル グループ、および現在の既定のファイル グループを表示するには、sys.filegroups カタログ ビューを使用してください。

  • ADD LOG FILE
    指定されたデータベースにログ ファイルを追加します。

  • REMOVE FILE logical_file_name
    SQL Server インスタンスから論理ファイルの説明を削除し、物理ファイルを削除します。 ファイルが空でない場合は削除できません。

    • logical_file_name
      ファイルを参照するときに SQL Server で使用される論理名を指定します。

  • MODIFY FILE
    変更するファイルを指定します。 一度に 1 つの <filespec> プロパティだけを変更できます。 変更するファイルを識別するには、<filespec> に NAME を指定する必要があります。 SIZE を指定する場合、ファイルの現在のサイズより新しいサイズの方が大きくなければなりません。

    データ ファイルまたはログ ファイルの論理名を変更するには、変更するファイルの論理名を NAME 句で指定し、NEWNAME 句にそのファイルの新しい論理名を指定します。 次に例を示します。

    MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

    データ ファイルまたはログ ファイルを別の場所に移動するには、NAME 句にファイルの現在の論理名を指定し、FILENAME 句に新しいパスとオペレーティング システム ファイル名を指定します。 次に例を示します。

    MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )

    フルテキスト カタログを移動する場合は、FILENAME 句に新しいパスだけを指定します。 オペレーティング システム ファイル名は指定しないでください。

    詳細については、「データベース ファイルの移動」を参照してください。

    FILESTREAM ファイル グループの場合、NAME をオンラインで変更できます。 FILENAME はオンラインで変更できますが、コンテナーを物理的に再配置し、サーバーをシャットダウンして再起動するまで、変更は有効になりません。

    FILESTREAM ファイルを OFFLINE に設定できます。 FILESTREAM ファイルをオフラインにすると、その親ファイル グループがオフラインとして内部的にマークされるため、そのファイル グループ内の FILESTREAM データへのアクセスはすべて失敗します。

注意注

<add_or_modify_files> オプションは、包含データベースでは使用できません。

<filespec>::=

ファイル プロパティを制御します。

  • NAME logical_file_name
    ファイルの論理名を指定します。

    • logical_file_name
      ファイルを参照するときに SQL Server インスタンスで使用される論理名を指定します。

  • NEWNAME new_logical_file_name
    ファイルの新しい論理名を指定します。

    • new_logical_file_name
      既存の論理ファイル名と置換する新しい名前を指定します。 論理ファイル名は、データベース内で一意であり、識別子の規則に従っている必要があります。 この名前は、文字定数、Unicode 定数、標準の識別子、区切られた識別子のいずれでもかまいません。

  • FILENAME { 'os_file_name' | 'filestream_path' }
    オペレーティング システムの (物理) ファイル名を指定します。

    • ' os_file_name '
      標準の (ROWS) ファイル グループの場合、ファイルの作成時にオペレーティング システムで使用されるパスとファイル名を指定します。 ファイルは、SQL Server がインストールされているサーバー上に存在する必要があります。 ALTER DATABASE ステートメントを実行するには、指定したパスが実際に存在するパスであることが必要です。

      ファイルに対して UNC パスが指定されている場合、SIZE、MAXSIZE、および FILEGROWTH パラメーターは設定できません。

      注意注

      システム データベースを UNC 共有ディレクトリに置くことはできません。

      データ ファイルが読み取り専用のセカンダリ ファイルであるか、データベースが読み取り専用である場合を除き、データ ファイルを圧縮ファイル システム上には置かないでください。 ログ ファイルは、圧縮ファイル システム上に置くことはできません。

      ファイルが未処理のパーティション上にある場合、os_file_name には、未処理になっている既存のパーティションのドライブ文字のみを指定する必要があります。 未処理の各パーティションに配置できるファイルは、それぞれ 1 ファイルだけです。

    • ' filestream_path '
      FILESTREAM ファイル グループの場合、FILENAME は FILESTREAM データが格納されるパスを参照します。 最後のフォルダーまでのパスが存在する必要があり、最後のフォルダーは存在すべきではありません。 たとえば、パス C:\MyFiles\MyFilestreamData を指定する場合は、ALTER DATABASE を実行するとき、C:\MyFiles は既に存在している必要がありますが、MyFilestreamData フォルダーは存在してはなりません。

      SIZE プロパティおよび FILEGROWTH プロパティは、FILESTREAM ファイル グループには適用されません。

  • SIZE size
    ファイルのサイズを指定します。 SIZE は、FILESTREAM ファイル グループには適用されません。

    • size
      ファイルのサイズです。

      ADD FILE と共に指定する場合、size はファイルの初期サイズになります。 MODIFY FILE と共に指定する場合、size はファイルの新しいサイズになります。この値には、ファイルの現在のサイズより大きい値を指定する必要があります。

      プライマリ ファイルに size が指定されていない場合、SQL Server では、model データベースのプライマリ ファイルのサイズを使用します。 セカンダリ データ ファイルまたはログ ファイルが指定されているにもかかわらず、そのファイルに対して size が指定されていない場合、データベース エンジンでは、そのファイルのサイズが 1 MB になります。

      KB、MB、GB、および TB の各サフィックスを使用して、キロバイト、メガバイト、ギガバイト、またはテラバイトを指定できます。 既定値は MB です。 整数を指定します。小数は含めないでください。 小数部を持つメガバイトの値を指定するには、その値に 1024 を乗算することによって、キロバイトの単位に変換します。 たとえば、1.5 MB ではなく 1536 KB と指定します (1.5 × 1024 = 1536)。

  • MAXSIZE { max_size| UNLIMITED }
    ファイルのサイズを拡張する場合の最大サイズを指定します。

    • max_size
      ファイルの最大サイズです。 KB、MB、GB、および TB の各サフィックスを使用して、キロバイト、メガバイト、ギガバイト、またはテラバイトを指定できます。 既定値は MB です。 整数を指定します。小数は含めないでください。 max_size を指定しない場合、ファイル サイズはディスクがいっぱいになるまで拡張されます。

    • UNLIMITED
      ディスクがいっぱいになるまでファイルを拡張するように指定します。 SQL Server では、無制限に拡張するファイル固有のログの最大サイズは 2 TB で、データ ファイルの最大サイズは 16 TB です。FILESTREAM コンテナーに対してこのオプションを指定した場合、最大サイズはありません。 ディスクがいっぱいになるまでファイル サイズが拡張します。

  • FILEGROWTH growth_increment
    ファイルを自動拡張するときの増加量を指定します。 ファイルの FILEGROWTH 設定を MAXSIZE 設定より大きくすることはできません。 FILEGROWTH は、FILESTREAM ファイル グループには適用されません。

    • growth_increment
      新しい領域が必要とされるたびにファイルに追加される領域の容量です。

      値は MB、KB、GB、TB または % の単位で指定できます。 サフィックス MB、KB、または % を付けないで数値を指定した場合の既定値は MB です。 % を指定すると、1 回の増加量は、増加時のファイル サイズに指定されたパーセンテージを掛けた値になります。 指定されたサイズは、最も近い 64 KB の倍数値に丸められます。

      0 は、自動拡張がオフで、領域を追加できないことを示します。

      FILEGROWTH が指定されていない場合、既定値は、データ ファイルが 1 MB、ログ ファイルが 10% で、最小値は 64 KB になります。

      注意注

      SQL Server 2005 以降は、データ ファイルの既定の拡張増加量が、10% から 1 MB に変更されました。 ログ ファイルの既定値は 10% のままで、変更ありません。

  • OFFLINE
    ファイルをオフラインに設定し、ファイル グループ内のすべてのオブジェクトをアクセス不可にします。

    注記注意

    このオプションは、ファイルは破損しているが、復元可能な場合にのみ使用してください。 OFFLINE に設定したファイルは、そのファイルをバックアップから復元することによってのみ、オンラインに設定されます。 単一ファイルの復元方法の詳細については、「RESTORE (Transact-SQL)」を参照してください。
注意注

<filespec> オプションは、包含データベースでは使用できません。

<add_or_modify_filegroups>::=

データベースに対してファイル グループの追加、変更、または削除を行います。

  • ADD FILEGROUP filegroup_name
    データベースにファイル グループを追加します。

  • CONTAINS FILESTREAM
    ファイル グループで FILESTREAM バイナリ ラージ オブジェクト (BLOB) をファイル システムに格納することを指定します。

  • REMOVE FILEGROUP filegroup_name
    データベースからファイル グループを削除します。 ファイル グループが空でない場合は削除できません。 最初に、ファイル グループからすべてのファイルを削除してください。 詳細については、前の「REMOVE FILE logical_file_name」を参照してください。

    注意注

    FILESTREAM ガベージ コレクターによって FILESTREAM コンテナーからすべてのファイルが削除されない限り、ALTER DATABASE REMOVE FILE 操作で FILESTREAM コンテナーを削除する試みは失敗し、エラーが返されます。 このトピックの「解説」の「FILESTREAM コンテナーの削除」を参照してください。

  • MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME **=**new_filegroup_name }
    ファイル グループに変更を加えます。ここでは、状態を READ_ONLY または READ_WRITE に設定したり、ファイル グループをデータベースの既定のファイル グループに指定したり、ファイル グループ名を変更することができます。

    • <filegroup_updatability_option>
      ファイル グループに読み取り専用、または読み取り/書き込みのプロパティを設定します。

    • DEFAULT
      既定のデータベース ファイル グループを filegroup_name に変更します。 データベース内の 1 つのファイル グループだけを、既定のファイル グループにすることができます。 詳細については、「データベース ファイルとファイル グループ」を参照してください。

    • NAME = new_filegroup_name
      ファイル グループ名を new_filegroup_name に変更します。

<filegroup_updatability_option>::=

ファイル グループに読み取り専用、または読み取り/書き込みのプロパティを設定します。

  • READ_ONLY | READONLY
    ファイル グループが読み取り専用であることを指定します。 この中のオブジェクトを更新することはできません。 プライマリ ファイル グループを読み取り専用にすることはできません。 この状態を変更するには、データベースに対する排他的アクセスが必要になります。 詳細については、SINGLE_USER 句を参照してください。

    読み取り専用データベースのデータを変更することはできないため、次のようになります。

    • システム起動時に自動復旧がスキップされます。

    • データベースの圧縮が不可能になります。

    • 読み取り専用データベースでは、ロックは発生しません。 これにより、クエリのパフォーマンスが向上することがあります。

    注意注

    キーワード READONLY は、将来のバージョンの Microsoft SQL Server では削除される予定です。 新しい開発作業では READONLY の使用は避け、現在 READONLY を使用しているアプリケーションは修正するようにしてください。 代わりに、READ_ONLY を使用してください。

  • READ_WRITE | READWRITE
    ファイル グループを READ_WRITE に指定します。 ファイル グループ内のオブジェクトを更新できます。 この状態を変更するには、データベースに対する排他的アクセスが必要になります。 詳細については、SINGLE_USER 句を参照してください。

    注意注

    キーワード READWRITE は、将来のバージョンの Microsoft SQL Server では削除される予定です。 新しい開発作業では READWRITE の使用は避け、現在 READWRITE を使用しているアプリケーションは修正するようにしてください。 代わりに、READ_WRITE を使用してください。

これらのオプションの状態を確認するには、sys.databases カタログ ビューの is_read_only 列、または DATABASEPROPERTYEX 関数の Updateability プロパティを調べてください。

説明

データベースのサイズを縮小するには、DBCC SHRINKDATABASE を使用します。

BACKUP ステートメントの実行中にファイルを追加したり削除したりすることはできません。

各データベースに、最大 32,767 のファイルと 32,767 のファイル グループを指定できます。

SQL Server 2005 以降では、データベース ファイルの状態 (オンラインかオフラインかなど) は、データベースの状態とは別に保持されます。 詳細については、「ファイルの状態」を参照してください。 ファイル グループ内のファイルの状態は、ファイル グループ全体の可用性を決定します。 ファイル グループを使用可能にするには、ファイル グループ内のすべてのファイルがオンラインである必要があります。 ファイル グループがオフラインの場合、SQL ステートメントでそのファイル グループにアクセスを試行するとエラーが発生します。 SELECT ステートメントのクエリ プランを作成する場合、クエリ オプティマイザーは、オフラインのファイル グループにある非クラスター化インデックスやインデックス付きビューを回避します。 これにより、これらのステートメントは正常に実行できます。 ただし、オフラインのファイル グループに、対象テーブルのヒープやクラスター化インデックスが含まれている場合には、SELECT ステートメントは失敗します。 また、オフラインのファイル グループ内にあるインデックス付きのテーブルを変更する INSERT、UPDATE、または DELETE ステートメントは失敗します。

ファイルの移動

SQL Server 2005 以降では、FILENAME に新しい場所を指定することにより、システムまたはユーザー定義のデータ、およびログ ファイルを移動することができます。 これは、次のようなシナリオで役立ちます。

  • 障害復旧。 たとえば、ハードウェア障害により、データベースが問題のあるモードになっている場合や、シャットダウンされた場合など。

  • 計画に従った再配置。

  • スケジュールされたディスク メンテナンスでの再配置。

詳細については、「データベース ファイルの移動」を参照してください。

ファイルの初期化

既定では、データ ファイルおよびログ ファイルは、次のいずれかの操作を実行したときに、ファイルを 0 で埋め込むことにより初期化されます。

  • データベースの作成

  • 既存のデータベースへのファイルの追加

  • 既存のファイルのサイズを大きくする

  • データベースまたはファイル グループの復元

データ ファイルを瞬時に初期化できます。 そのため、このようなファイル操作を高速に実行できます。

FILESTREAM コンテナーの削除

DBCC SHRINKFILE 操作によって FILESTREAM コンテナーが空にされた場合でも、データベースは、さまざまなシステム保守上の理由から、削除されたファイルへの参照を維持する必要があります。 sp_filestream_force_garbage_collection (Transact-SQL) は、FILESTREAM ガベージ コレクターを実行して、安全な場合にこれらのファイルを削除します。 FILESTREAM ガベージ コレクターによって FILESTREAM コンテナーからすべてのファイルが削除されない限り、ALTER DATABASE REMOVE FILE 操作で FILESTREAM コンテナーを削除する試みは失敗し、エラーが返されます。 FILESTREAM コンテナーを削除する場合は、次の手順をお勧めします。

  1. EMPTYFILE オプションを指定して DBCC SHRINKFILE (Transact-SQL) を実行して、このコンテナーのアクティブなコンテンツを別のコンテナーに移動します。

  2. ログ バックアップが完全または一括ログ復旧モデルで作成されたことを確認します。

  3. レプリケーション ログ リーダー ジョブが実行されていることを確認します (該当する場合)。

  4. sp_filestream_force_garbage_collection (Transact-SQL) を実行して、このコンテナーで不要になったすべてのファイルをガベージ コレクターを使って強制的に削除します。

  5. REMOVE FILE オプションを指定して ALTER DATABASE を実行し、このコンテナーを削除します。

  6. もう一度手順 2. ～ 4. を繰り返して、ガベージ コレクションを完了します。

  7. ALTER Database...REMOVE FILE を使用して、このコンテナーを削除します。

使用例

A. データベースにファイルを追加する

次の例では、5 MB のデータ ファイルを AdventureWorks2012 データベースに追加します。

USE master;
GO
ALTER DATABASE AdventureWorks2012 
ADD FILE 
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

B. 2 つのファイルから成るファイル グループをデータベースに追加する

次の例では、AdventureWorks2012 データベースに Test1FG1 ファイル グループを作成し、そのファイル グループに 5 MB のファイルを 2 つ追加します。

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

C. 2 つのログ ファイルをデータベースに追加する

次の例では、5 MB のログ ファイルを 2 つ、 AdventureWorks2012 データベースに追加します。

USE master;
GO
ALTER DATABASE AdventureWorks2012 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test3log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

D. データベースからファイルを削除する

次の例では、例 B で追加したファイルの一方を削除します。

USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4;
GO

E. ファイルを変更する

次の例では、例 B で追加したファイルのうち、1 つのサイズを拡張します。

USE master;
GO
ALTER DATABASE AdventureWorks2012 
MODIFY FILE
    (NAME = test1dat3,
    SIZE = 20MB);
GO

F. ファイルを新しい場所に移動する

次の例では、例 A で作成した Test1dat2 ファイルを、新しいディレクトリに移動します。

注意注

この例を実行する前に、ファイルを新しいディレクトリに物理的に移動しておく必要があります。 その後、SQL Server のインスタンスをいったん停止してから起動するか、または AdventureWorks2012 データベースをいったん OFFLINE にしてから ONLINE にして、変更を実装します。

 
USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G. tempdb を新しい場所に移動する

次の例では、tempdb をディスク上の現在の場所から別の場所に移動します。 tempdb は MSSQLSERVER サービスが開始されるたびに再作成されるので、データ ファイルとログ ファイルを物理的に移動する必要はありません。 これらのファイルは、MSSQLSERVER サービスが手順 3. で再開したときに作成されます。 MSSQLSERVER サービスを再開しない限り、tempdb は引き続き元の場所で機能します。

  1. tempdb データベースの論理ファイル名と、ディスク上での現在の場所を確認します。

    SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO

  2. ALTER DATABASE を使用して、各ファイルの場所を変更します。

    USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE  tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO

  3. SQL Server のインスタンスをいったん停止してから再起動します。

  4. ファイルの変更を確認します。

    SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

  5. tempdb.mdf ファイルと templog.ldf ファイルを元の場所から削除します。

H. ファイル グループを既定にする

次の例では、例 B で作成した Test1FG1 ファイル グループを、既定のファイル グループにします。 次に、既定のファイル グループを、PRIMARY ファイル グループに再設定します。 PRIMARY は、角かっこまたは引用符で区切る必要があります。

USE master;
GO
ALTER DATABASE AdventureWorks2012 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2012 
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

I. ALTER DATABASE を使用してファイル グループを追加する

次の例では、FILESTREAM 句が含まれる FILEGROUP を、FileStreamPhotoDB データベースに追加します。

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause to
--the FileStreamPhotoDB database.
ALTER DATABASE FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM
GO

--Add a file for storing database photos to FILEGROUP 
ALTER DATABASE FileStreamPhotoDB
ADD FILE
(
    NAME= 'PhotoShoot1',
    FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot
GO

関連項目

参照

CREATE DATABASE (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DROP DATABASE (Transact-SQL)

sp_spaceused (Transact-SQL)

sys.databases (Transact-SQL)

sys.database_files (Transact-SQL)

sys.data_spaces (Transact-SQL)

sys.filegroups (Transact-SQL)

sys.master_files (Transact-SQL)

DBCC SHRINKFILE (Transact-SQL)

sp_filestream_force_garbage_collection (Transact-SQL)

概念

バイナリ ラージ オブジェクト (Blob) データ (SQLServer)