ALTER DATABASE (Transact-SQL)

  • Статья

Изменяет базу данных или файлы и файловые группы, связанные с базой данных. Добавляет или удаляет файлы и файловые группы из базы данных, изменяет атрибуты базы данных или ее файлов и файловых групп, изменяет параметры сортировки базы данных и устанавливает параметры базы данных. Моментальные снимки базы данных изменить нельзя. Чтобы изменить параметры базы данных, связанные с репликацией, используйте процедуру sp_replicationdboption.

Применимо для следующих объектов: SQL Server (начиная с SQL Server 2008 до текущей версии), База данных SQL Azure.

Так как синтаксис ALTER DATABASE имеет значительную длину, его разделяют на несколько частей:

  • ALTER DATABASE
    Данный раздел содержит синтаксис, изменяющий имя и параметры сортировки базы данных.

  • ALTER DATABASE «параметры файлов и файловых групп»
    Содержит синтаксис, добавляющий или удаляющий файлы и их группы в базе данных, а также предназначенный для изменения атрибутов указанных файлов.

  • ALTER DATABASE SET «параметры»
    Содержит синтаксис, изменяющий атрибуты базы данных с помощью параметров SET команды ALTER DATABASE.

  • ALTER DATABASE «зеркальное отображение базы данных»
    Содержит синтаксис параметров SET команды ALTER DATABASE, используемых в процессе зеркального отображения базы данных.

  • ALTER DATABASE SET HADR
    Предоставляет синтаксис для параметров Группы доступности AlwaysOn инструкции ALTER DATABASE для настройки базы данных-получателя вторичной реплики группы доступности AlwaysOn.

  • ALTER DATABASE «уровень совместимости»
    Содержит синтаксис параметров SET команды ALTER DATABASE, относящихся к уровню совместимости базы данных.

Значок ссылки на раздел Cинтаксические обозначения в 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)

    Применимо для следующих объектов: База данных SQL Azure

    Указывает максимальный размер базы данных. Максимальный размер должен соответствовать допустимому набору значений для свойства EDITION базы данных. Смена максимального размера базы данных может потребовать также смены значения EDITION базы данных. В следующей таблице приведены поддерживаемые значения MAXSIZE и значения, заданные по умолчанию (D) для уровней служб База данных SQL.

    MAXSIZE

    Web Edition

    Business Edition

    Basic

    Standard Edition

    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 ГБ и значение EDITION не указано, то в качестве выпуска базы данных будет автоматически задан Web.

    • Если параметру MAXSIZE задано значение больше 5 ГБ и значение EDITION не указано, то в качестве выпуска базы данных будет автоматически задан Business.

    • Если параметр EDITION указан, а параметр MAXSIZE — нет, то в качестве выпуска будет использоваться значение по умолчанию. Например, если параметру EDITION задано значение Standard, а параметр MAXSIZE не задан, то параметру MAXSIZE будет автоматически присвоено значение 500 МБ.

    • Если не указаны ни MAXSIZE, ни EDITION, то параметру EDITION задается значение Web, а параметру MAXSIZE — значение 1 ГБ.

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

    Применимо для следующих объектов: База данных SQL Azure

    Изменяет выпуск базы данных. Уровень служб База данных SQL можно задавать или изменять с помощью параметра EDITION. Смена значения параметра EDITION завершается неудачей, если для свойства MAXSIZE базы данных задано значение вне допустимого диапазона, поддерживаемого этим выпуском.

    Важное примечаниеВажно!

    Уровни службы Business и Web будут выведены из эксплуатации в сентябре 2015 г.Дополнительные сведения см. в разделе Часто задаваемые вопросы по выпускам Web и Business.

  • SERVICE_OBJECTIVE

    Применимо для следующих объектов: База данных SQL Azure

    Определяет уровень производительности. Дополнительные сведения об описании служб и о размере, выпусках и комбинациях служб см. в разделе Уровни обслуживания и уровни производительности служб баз данных Azure SQL. Если указанное значение SERVICE_OBJECTIVE не поддерживается для значения EDITION, вы получите сообщение об ошибке. Чтобы изменить значение SERVICE_OBJECTIVE с одного уровня на другой (например, с S1 на P1), необходимо также изменить значение EDITION.

  • <db_update_option> ::=

    Применимо для следующих объектов: База данных SQL Azure

    Управляет разрешениями на обновления базы данных.

    { 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 для файлов и файловых групп (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" (часть кэша планов) в связи с обслуживанием базы данных или операциями по перенастройке." Это сообщение добавляется в журнал каждые пять минут при сбросе кэша в течение этого интервала времени.

Кроме того, кэш процедур сбрасывается в следующих случаях.

  • В базе данных включен параметр базы данных AUTO_CLOSE. Если отсутствуют ссылки соединений пользователя или базы данных, фоновая задача предпримет попытку закрыть и отключить базу данных автоматически.

  • Выполняется нескольких запросов в базе данных с параметрами по умолчанию. Затем база данных уничтожается.

  • Моментальный снимок базы данных для базы данных-источника удален.

  • Успешное перестроение журнала транзакций базы данных.

  • Восстановление резервной копии базы данных.

  • Отсоединение базы данных.

Изменение параметров сортировки в базе данных

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

  1. Вы являетесь единственным пользователем базы данных в настоящее время.

  2. Ни один объект, привязанный к схеме, не зависит от параметров сортировки базы данных.

    Если следующие объекты, зависящие от параметров сортировки базы данных, существуют в базе данных, выполнение инструкции ALTER DATABASEdatabase_nameCOLLATE завершится ошибкой. SQL Server возвращает сообщение об ошибке для каждого объекта, блокирующего действие ALTER:

    • определяемые пользователем функции и представления, созданные с помощью предложения SCHEMABINDING;

    • вычисляемые столбцы;

    • ограничения CHECK;

    • возвращающие табличное значение функции, которые возвращают таблицы с символьными столбцами, имеющими параметры сортировки, унаследованные из параметров сортировки базы данных по умолчанию.

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

При изменении параметров сортировки базы данных дубликаты любых системных имен для объектов базы данных не создаются. Если новые параметры сортировки вызывают повторяющиеся имена, следующие пространства имен могут вызвать сбой при изменении параметров сортировки базы данных:

  • имена объектов, такие как процедуры, таблицы, триггеры или представления;

  • имена схем;

  • участники, такие как группы, роли или пользователи;

  • имена скалярных типов, таких как системные и определяемые пользователем типы;

  • имена полнотекстовых каталогов;

  • имена столбцов или параметров в пределах объекта;

  • имена индексов в пределах таблицы.

Повторяющиеся имена, полученные с помощью новых параметров сортировки, могут быть причиной сбоя операции изменения, а SQL Server возвратит сообщение об ошибке, указывающее пространство имен, в котором был найден дубликат.

Просмотр сведений о базе данных

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

Разрешения

SQL Server

Необходимо разрешение ALTER на базу данных.

База данных SQL Azure

Изменять базу данных могут только имя входа субъект серверного уровня (созданное в процессе провизионирования) или члены роли базы данных dbmanager.

Примечание по безопасностиПримечание по безопасности

Владелец базы данных не может изменять базу данных, если он не является членом роли dbmanager.

Примеры

А.Изменение имени базы данных

В следующем примере имя базы данных AdventureWorks2012 изменяется на Northwind.

USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO

Б.Изменение параметров сортировки базы данных

В следующем примере создается база данных testdb, параметры сортировки которой имеют значение SQL_Latin1_General_CP1_CI_A. Затем имя базы данных 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)

Основные понятия

Системные базы данных