• [アーティクル]

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 データへのアクセスはすべて失敗します。

<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 パラメータは設定できません。

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

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

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

      ファイル グループとファイル (<filespec>) は、同じステートメントで作成する必要があります。ファイル (<filespec>) は、FILESTREAM ファイル グループごとに 1 つしか指定できません。

      SIZE、MAXSIZE、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 }
    ファイルのサイズを拡張する場合の最大サイズを指定します。MAXSIZE は、FILESTREAM ファイル グループには適用されません。

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

    • UNLIMITED
      ディスクがいっぱいになるまでファイルを拡張するように指定します。SQL Server では、無制限に拡張するファイル固有のログの最大サイズは 2 TB で、データ ファイルの最大サイズは 16 TB です。

  • 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)」を参照してください。

<add_or_modify_filegroups>::=

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

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

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

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

  • 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 は、将来のバージョンの MicrosoftSQL Server では削除される予定です。新しい開発作業では READONLY の使用は避け、現在 READONLY を使用しているアプリケーションは修正するようにしてください。代わりに、READ_ONLY を使用してください。

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

    注意注意

    キーワード READWRITE は、将来のバージョンの MicrosoftSQL 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 で埋め込むことにより初期化されます。

  • データベースの作成

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

  • 既存のファイルのサイズの拡張

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

データ ファイルを瞬時に初期化できます。そのため、このようなファイル操作を高速に実行できます。詳細については、「データベース ファイルの初期化」を参照してください。

例

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

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

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

);
GO

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

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

USE master
GO
ALTER DATABASE AdventureWorks
ADD FILEGROUP Test1FG1;
GO

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

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

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

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

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

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

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

E. ファイルを変更する

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

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

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

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

注意注意

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

 
USE master;
GO
ALTER DATABASE AdventureWorks
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 AdventureWorks 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks 
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