Статья
04/01/2012

Параметры инструкции 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>. Предложение NAME всегда должно быть указано в <filespec>, чтобы определить, какой файл будет изменен. Если указано предложение 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 '
  Для стандартной файловой группы (ROWS) этот параметр представляет собой путь и имя файла, которые использовались операционной системой при создании файла. Файл должен постоянно храниться на сервере, на котором установлен SQL Server. Указанный путь должен существовать до выполнения инструкции ALTER DATABASE.
  
  Параметры SIZE, MAXSIZE и FILEGROWTH недоступны, если путь к файлу указан в формате UNC.
  
  Примечание
  
  Системные базы данных не могут размещаться в общих каталогах UNC.
  
  Файлы данных не должны располагаться в сжатой файловой системе, кроме случаев, когда файлы являются вторичными файлами только для чтения или база данных находится в режиме только для чтения. Файлы журналов ни в коем случае не должны размещаться в сжатых файловых системах. Дополнительные сведения см. в разделе Сжатие и файловые группы только для чтения.
  
  Если файл находится в необработанной секции, аргумент os_file_name должен указывать только букву диска существующей необработанной секции. В каждую необработанную секцию может быть помещен только один файл.
- 'filestream_path'
  Для файловой группы FILESTREAM параметр FILENAME указывает путь, где будут храниться данные FILESTREAM. Должен существовать путь вплоть до последнего каталога, но последний каталог существовать не должен. Например, если указать путь «C:\MyFiles\MyFilestreamData», папка «C:\MyFiles» должна существовать до запуска инструкции ALTER DATABASE, а папка «MyFilestreamData» — не должна.
  
  Файловую группу и файл (<filespec>) необходимо создавать в одной инструкции. Для файловой группы FILESTREAM может быть только один файл (<filespec>,).
  
  Свойства SIZE, MAXSIZE и FILEGROWTH к файловой группе FILESTREAM не относятся.
SIZE size
Указывает размер файла. Параметр SIZE не применяется к файловым группам FILESTREAM.
- size
  Размер файла.
  
  При использовании в инструкции ADD FILE аргумент size является начальным размером файла. При использовании в инструкции MODIFY FILE аргумент size является новым размером файла и должен превышать текущий размер файла.
  
  Если аргумент size не задан для первичного файла, то компонент SQL Server использует размер первичного файла, указанный в базе данных model. Когда указан вторичный файл данных или журнала, но параметр size для файла не указан, компонент Database Engine задает размер файла равным 1 МБ.
  
  Суффиксы KB, MB, GB и TB могут использоваться для указания килобайтов, мегабайтов, гигабайтов или терабайтов. По умолчанию — MБ. Укажите целое число без десятичного разделителя. Для указания долей мегабайта преобразуйте значение в килобайты, умножив число на 1024. Например, укажите «1536 KB» вместо «1,5 MB» (1,5 x 1024 = 1536).
MAXSIZE { max_size| UNLIMITED }
Указывает максимальный размер, до которого может расти файл. Параметр MAXSIZE не применяется к файловым группам FILESTREAM.
- max_size
  Максимальный размер файла. Суффиксы KB, MB, GB и TB могут использоваться для указания килобайтов, мегабайтов, гигабайтов или терабайтов. По умолчанию — MБ. Укажите целое число без десятичного разделителя. Если аргумент max_size не указан, то размер файла может увеличиваться до тех пор, пока диск не будет заполнен.
- UNLIMITED
  Указывает, что файл может расти вплоть до заполнения диска. В SQL Server файл журнала, для которого задано неограниченное увеличение размера, имеет максимальный размер 2 ТБ, а файл данных — 16 ТБ.

Примечание
Системные базы данных не могут размещаться в общих каталогах UNC.

FILEGROWTH growth_increment
Задает автоматическое приращение размера файла. Значение параметра FILEGROWTH для файла не может превосходить значение параметра MAXSIZE. Параметр FILEGROWTH не применяется к файловым группам FILESTREAM.

growth_increment
Объем пространства, добавляемого к файлу каждый раз, когда требуется увеличение пространства.

Значение может быть указано в килобайтах, мегабайтах, гигабайтах, терабайтах или процентах (%). Если указано число без суффикса MB, KB или %, то по умолчанию используется MB. Если размер указан в процентах (%), то шаг роста это заданная часть в процентах от размера файла во время этого файла. Указанный размер округляется до ближайших 64 КБ.

Значение 0 указывает, что автоматическое приращение выключено и дополнительное пространство для файла не разрешено.

Если параметр FILEGROWTH не задан, значением по умолчанию является 1 МБ для файлов данных и 10% для файлов журналов, минимальное значение — 64 КБ.

Примечание
Начиная с SQL Server 2005 шаг роста по умолчанию для файлов данных изменился и вместо 10% применяется значение 1 МБ. Значение по умолчанию для файлов журналов (10%) осталось неизменным.

OFFLINE
Переводит файл в режим вне сети и делает все его объекты в файловой группе недоступными.

Внимание!
Используйте этот параметр только в том случае, когда файл поврежден и может быть восстановлен. Файл, переведенный в режим OFFLINE, может быть заново включен в режиме в сети только при восстановлении из резервной копии. Дополнительные сведения о восстановлении из копии одного файла см. в разделе RESTORE (Transact-SQL).

Используйте этот параметр только в том случае, когда файл поврежден и может быть восстановлен. Файл, переведенный в режим OFFLINE, может быть заново включен в режиме в сети только при восстановлении из резервной копии. Дополнительные сведения о восстановлении из копии одного файла см. в разделе RESTORE (Transact-SQL).

<add_or_modify_filegroups>::=

Добавить, изменить или удалить файловую группу из базы данных.

ADD FILEGROUP filegroup_name
Добавляет файловую группу в базу данных.
CONTAINS FILESTREAM
Указывает, что файловая группа хранит большие двоичные объекты (BLOB) FILESTREAM в файловой системе.
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 будет удалено в будущей версии Microsoft SQL Server. Избегайте использования ключевого слова READONLY в новых разработках и запланируйте изменение приложений, которые сейчас его используют. Вместо него используйте READ_ONLY.

Ключевое слово READONLY будет удалено в будущей версии Microsoft SQL Server. Избегайте использования ключевого слова READONLY в новых разработках и запланируйте изменение приложений, которые сейчас его используют. Вместо него используйте READ_ONLY.

READ_WRITE | READWRITE
Определяет, что файловая группа находится в состоянии READ_WRITE. Разрешено изменять объекты в файловой группе. Чтобы изменить это состояние, необходимо обладать монопольным доступом к базе данных. Дополнительные сведения см. в описании предложения SINGLE_USER.

Примечание
Ключевое слово READWRITE будет удалено в будущей версии Microsoft SQL Server. Избегайте использования ключевого слова READWRITE в новых разработках и запланируйте изменение приложений, которые сейчас его используют. Вместо него используйте READ_WRITE.

Ключевое слово READWRITE будет удалено в будущей версии Microsoft SQL Server. Избегайте использования ключевого слова READWRITE в новых разработках и запланируйте изменение приложений, которые сейчас его используют. Вместо него используйте READ_WRITE.

Состояние этих параметров может быть определено с помощью проверки значения столбца is_read_only в представлении каталога sys.databases или свойства Updateability функции DATABASEPROPERTYEX.

Замечания

Чтобы уменьшить размер базы данных, используйте предложение DBCC SHRINKDATABASE.

Добавить или удалить файл во время выполнения инструкции BACKUP невозможно.

Для каждой базы данных может указываться не более 32 767 файлов и 32 767 файловых групп.

В SQL Server 2005 и более поздних версиях состояние файла базы данных (например, в сети или вне сети) поддерживается независимо от состояния базы данных. Дополнительные сведения см. в разделе Состояния файла. Состояние файлов в пределах файловой группы определяет доступность файловой группы в целом. Чтобы файловая группа была доступна, необходимо, чтобы все файлы в файловой группе находились в режиме «в сети». Если файловая группа вне сети, то любая попытка обращения к файловой группе с помощью инструкции SQL закончится ошибкой. При создании планов запросов для инструкций SELECT оптимизатор запросов избегает некластеризованных индексов и индексированных представлений, которые находятся в файловых группах вне сети. Это позволяет успешно выполнить эти инструкции. Однако, если файловая группа, находящаяся в режиме «вне сети», содержит область памяти или кластеризованный индекс целевой таблицы, инструкция SELECT не сможет быть выполнена. Кроме того, любая инструкция INSERT, UPDATE или DELETE, изменяющая таблицу с индексом в файловой группе, находящейся в автономном режиме, также не будет выполнена.

Перемещение файлов

В SQL Server 2005 или более поздней версии можно перемещать системные или определенные пользователем данные и файлы журналов путем указания нового местоположения в параметре FILENAME. Это может быть полезным в следующих случаях:

восстановление после сбоя. Например, база данных находится в подозрительном режиме или завершила работу по причине сбоя оборудования;
плановое перемещение;
перемещение для запланированного обслуживания дисков.

Дополнительные сведения см. в разделе Перемещение файлов баз данных.

Инициализация файлов

По умолчанию файлы данных и журналов инициализируются, заполняясь нулями, при выполнении одной из следующих операций:

создание базы данных;
добавление файлов к существующей базе данных;
увеличение размера существующего файла;
восстановление базы данных или файловой группы.

Файлы данных могут быть инициализированы мгновенно. Это разрешено для быстрого выполнения этих файловых операций. Дополнительные сведения см. в разделе Инициализация файлов базы данных.

Примеры

A. Добавление файла к базе данных

В следующем примере к базе данных AdventureWorks2008R2 добавляется файл данных размером 5 МБ.

USE master;
GO
ALTER DATABASE AdventureWorks2008R2 
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

Б. Добавление файловой группы с двумя файлами к базе данных

В следующем примере в базе данных AdventureWorks2008R2 создается файловая группа Test1FG1 и добавляется два файла по 5 МБ в эту файловую группу.

USE master
GO
ALTER DATABASE AdventureWorks2008R2
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2008R2 
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

В. Добавление двух файлов журнала к базе данных

В следующем примере к базе данных AdventureWorks2008R2 добавляется два файла журнала размером 5 МБ каждый.

USE master;
GO
ALTER DATABASE AdventureWorks2008R2 
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

Г. Удаление файла из базы данных

В следующем примере удаляется один из файлов, добавленных в примере Б.

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

Д. Изменение файла

В следующем примере увеличивается размер одного из файлов, добавленных в примере Б.

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

Е. Перемещение файла в новое расположение

В следующем примере файл Test1dat2, созданный в примере A, перемещается в новый каталог.

Примечание
Перед выполнением этого примера необходимо физически переместить файл в новый каталог. После выполнения остановите и запустите экземпляр SQL Server или переведите базу данных База данных AdventureWorks2008R2 в состояние OFFLINE, а затем назад в ONLINE, чтобы осуществить изменения.

Перед выполнением этого примера необходимо физически переместить файл в новый каталог. После выполнения остановите и запустите экземпляр SQL Server или переведите базу данных База данных AdventureWorks2008R2 в состояние OFFLINE, а затем назад в ONLINE, чтобы осуществить изменения.

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

Ж. Перемещение базы данных tempdb в новое расположение

В следующем примере база данных tempdb перемещается из ее текущего расположения на диске в другое расположение. Так как база данных tempdb повторно создается при каждом запуске службы MSSQLSERVER, нет необходимости физически переносить файлы данных и журнала. Эти файлы создаются при запуске службы на шаге 3. Пока служба не будет запущена повторно, база данных tempdb продолжает функционировать на прежнем месте.

Определите логические имена файлов базы данных tempdb и их текущее расположение на диске.
```
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
```

Измените местоположение каждого файла с помощью инструкции 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

Остановите и перезапустите экземпляр SQL Server.

Проверьте изменение файла.

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

Удалите файлы tempdb.mdf и templog.ldf из их исходного расположения.

З. Назначение файловой группы по умолчанию

В следующем примере файловая группа Test1FG1, созданная в примере Б, назначается файловой группой по умолчанию. Затем файловая группа по умолчанию будет переназначена на файловую группу PRIMARY. Обратите внимание, что слово PRIMARY должно быть заключено в скобки или в кавычки.

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

К. Добавление файловой группы с помощью инструкции ALTER DATABASE

В следующем примере в базу данных FileStreamPhotoDB добавляется файловая группа FILEGROUP, содержащая предложение FILESTREAM.

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