• 아티클

ALTER DATABASE 파일 및 파일 그룹 옵션(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
    수정할 파일을 지정합니다. <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
      기존 논리적 파일 이름을 바꿀 이름입니다. 이 이름은 데이터베이스에서 고유해야 하며 식별자에 대한 규칙을 따라야 합니다. 이름에 문자나 유니코드 상수, 일반 식별자, 구분 식별자를 지정할 수 있습니다. 자세한 내용은 식별자를 개체 이름으로 사용을 참조하십시오.

  • FILENAME { 'os_file_name' | 'filestream_path' }
    운영 체제(물리적) 파일 이름을 지정합니다.

    • ' os_file_name '
      표준(행) 파일 그룹의 경우 이것은 파일을 만들 때 운영 체제에서 사용한 경로와 파일 이름입니다. 이 파일은 SQL Server가 설치된 서버에 있어야 합니다. ALTER DATABASE 문을 실행하기 전에 지정된 경로가 존재해야 합니다.

      파일에 대해 UNC 경로가 지정된 경우 SIZE, MAXSIZE 및 FILEGROWTH 매개 변수를 설정할 수 없습니다.

      파일이 읽기 전용 보조 파일이 아니거나 데이터베이스가 읽기 전용이 아닌 경우 데이터 파일을 압축 파일 시스템에 저장하지 마십시오. 로그 파일은 절대로 압축 파일 시스템에 저장하지 마십시오. 자세한 내용은 읽기 전용 파일 그룹 및 압축을 참조하십시오.

      파일이 원시 파티션에 있을 경우, os_file_name에는 기존 원시 파티션의 드라이브 문자만 지정해야 합니다. 각 원시 파티션에는 한 개의 파일만 저장할 수 있습니다.

    • 'filestream_path'
      FILENAME 파일 그룹의 경우 FILENAME은 FILESTREAM 데이터가 저장될 경로를 참조합니다. 따라서 마지막 폴더의 경로가 있어야 하고 마지막 폴더 자체가 있으면 안 됩니다. 예를 들어 C:\MyFiles\MyFilestreamData 경로를 지정하는 경우 ALTER DATABASE를 실행하기 전에 C:\MyFiles 경로가 있어야 하지만 MyFilestreamData 폴더는 있으면 안 됩니다.

      파일 그룹과 파일(<filespec>)은 동일한 문으로 생성되어야 합니다. FILESTREAM 파일 그룹에 <filespec> 파일 하나만 있을 수도 있습니다.

      SIZE, MAXSIZE 및 FILEGROWTH 속성은 FILESTREAM 그룹에 적용되지 않습니다.

  • SIZE size
    파일 크기를 지정합니다. SIZE는 FILESTREAM 파일 그룹에 적용되지 않습니다.

    • size
      파일의 크기입니다.

      ADD FILE과 함께 지정할 경우 size는 파일의 처음 크기를 나타냅니다. MODIFY FILE과 함께 지정할 경우 size는 새로운 파일 크기를 나타내며 현재 파일 크기보다 커야 합니다.

      주 파일의 size를 지정하지 않으면 SQL Server에서 model 데이터베이스에 있는 주 파일의 크기를 사용합니다. 보조 데이터 파일 또는 로그 파일을 지정하고 해당 파일의 size 를 지정하지 않으면 데이터베이스 엔진에서 파일 크기를 1MB로 지정합니다.

      KB, MB, GB 및 TB 접미사를 사용하여 각각 킬로바이트, 메가바이트, 기가바이트, 테라바이트를 지정할 수 있습니다. 기본값은 MB입니다. 소수점을 포함하지 않은 정수를 지정합니다. 소수로 된 MB 값을 지정하려면 1024를 곱하여 값을 KB로 변환합니다. 예를 들어 1.5MB인 경우 1536KB(1.5 x 1024 = 1536)를 지정합니다.

  • MAXSIZE { max_size| UNLIMITED }
    파일을 확장할 수 있는 최대 크기를 지정합니다. MAXSIZE는 FILESTREAM 파일 그룹에 적용되지 않습니다.

    • max_size
      파일의 최대 크기입니다. KB, MB, GB 및 TB 접미사를 사용하여 각각 킬로바이트, 메가바이트, 기가바이트, 테라바이트를 지정할 수 있습니다. 기본값은 MB입니다. 소수점을 포함하지 않은 정수를 지정합니다. max_size를 지정하지 않으면 디스크가 꽉 찰 때까지 파일 크기가 늘어납니다.

    • UNLIMITED
      디스크가 꽉 찰 때까지 파일 크기가 늘어나도록 지정합니다. SQL Server에서 무제한 증가가 허용된 로그 파일의 최대 크기는 2TB이고 데이터 파일의 최대 크기는 16TB입니다.

  • FILEGROWTH growth_increment
    파일의 자동 증가분을 지정합니다. 파일에 대한 FILEGROWTH 설정은 MAXSIZE 설정을 초과할 수 없습니다. FILEGROWTH는 FILESTREAM 파일 그룹에 적용되지 않습니다.

    • growth_increment
      공간이 새로 필요할 때마다 파일에 추가되는 공간의 크기입니다.

      값은 MB, KB, GB, TB, 또는 퍼센트(%) 단위로 지정할 수 있습니다. MB, KB 또는 % 접미사를 붙이지 않고 숫자를 지정하면 MB가 기본값이 됩니다. %를 지정할 때의 증가분 크기는 파일이 늘어나는 시점의 파일 크기에 대한 지정한 비율입니다. 지정한 크기는 64KB 단위로 반올림됩니다.

      값 0은 자동 증가를 사용하지 않고 추가 공간을 허용하지 않음을 나타냅니다.

      FILEGROWTH를 지정하지 않으면 데이터 파일에는 1MB, 로그 파일에는 10%의 기본값이 적용되며 최소값은 64KB입니다.

      [!참고]

      SQL Server 2005부터 데이터 파일의 기본 증가분은 10%에서 1MB로 변경되었습니다. 로그 파일의 기본값 10%는 그대로 유지됩니다.

  • OFFLINE
    파일을 오프라인으로 설정하고 파일 그룹 내의 모든 개체를 액세스할 수 없도록 합니다.

    주의 사항주의

    파일이 손상되고 복원이 가능한 경우에만 이 옵션을 사용하십시오. OFFLINE으로 설정된 파일은 해당 파일을 백업에서 복원해야만 온라인 상태로 설정할 수 있습니다. 단일 파일 복원 방법은 RESTORE(Transact-SQL)를 참조하십시오.

<add_or_modify_filegroups>::=

데이터베이스의 파일 그룹을 추가, 수정 또는 제거합니다.

  • ADD FILEGROUP filegroup_name
    데이터베이스에 파일 그룹을 추가합니다.

  • CONTAINS FILESTREAM
    파일 그룹이 FILESTREAM BLOB(Binary Large Object)를 파일 시스템에 저장하도록 지정합니다.

  • 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으로 변경합니다. 데이터베이스에서 한 개의 파일 그룹만 기본 파일 그룹이 될 수 있습니다. 자세한 내용은 파일 및 파일 그룹 이해를 참조하십시오.

    • 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으로 채워져 초기화됩니다.

  • 데이터베이스 만들기

  • 기존 데이터베이스에 파일 추가

  • 기존 파일의 크기 증가

  • 데이터베이스 또는 파일 그룹 복원

데이터 파일을 즉시 초기화할 수 있습니다. 이를 통해 이와 같은 파일 작업을 신속히 실행할 수 있습니다. 자세한 내용은 데이터베이스 파일 초기화를 참조하십시오.

예

1. 데이터베이스에 파일 추가

다음 예에서는 AdventureWorks 데이터베이스에 5MB 데이터 파일을 추가합니다.

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

2. 데이터베이스에 두 개의 파일이 포함된 파일 그룹 추가

다음 예에서는 AdventureWorks 데이터베이스에 Test1FG1 파일 그룹을 만들고 이 파일 그룹에 두 개의 5MB 파일을 추가합니다.

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

3. 데이터베이스에 두 개의 로그 파일 추가

다음 예에서는 AdventureWorks 데이터베이스에 두 개의 5MB 로그 파일을 추가합니다.

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

4. 데이터베이스에서 파일 제거

다음 예에서는 2번 예에서 추가한 파일 중 하나를 제거합니다.

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

5. 파일 수정

다음 예에서는 2번 예에서 추가한 파일 중 하나의 크기를 늘립니다.

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

6. 파일을 새 위치로 이동

다음 예에서는 1번 예에서 만든 Test1dat2 파일을 새 디렉터리로 이동합니다.

[!참고]

이 예를 실행하기 전에 해당 파일을 새 디렉터리로 물리적으로 이동해야 합니다. 그 다음 SQL Server 인스턴스를 중지한 후 다시 시작하거나 AdventureWorks 데이터베이스를 OFFLINE으로 설정한 후 다시 ONLINE으로 되돌려 변경 사항을 적용합니다.

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

7. tempdb를 새 위치로 이동

다음 예에서는 tempdb를 디스크의 현재 위치에서 다른 디스크 위치로 이동합니다. MSSQLSERVER 서비스를 시작할 때마다 tempdb가 다시 생성되므로 데이터와 로그 파일을 물리적으로 이동할 필요는 없습니다. 3단계에서 서비스를 다시 시작할 때 파일이 생성됩니다. 서비스를 다시 시작하기 전까지는 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 파일을 원래 위치에서 삭제합니다.

8. 기본 파일 그룹 설정

다음 예에서는 2번 예에서 만든 Test1FG1 파일 그룹을 기본 파일 그룹으로 설정합니다. 그 다음 PRIMARY 파일 그룹을 기본 파일 그룹으로 다시 설정합니다. PRIMARY는 대괄호 또는 따옴표로 구분해야 합니다.

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

9. 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