ALTER DATABASE (Transact-SQL)

Modifica um banco de dados ou os arquivos e grupos de arquivos associados ao banco de dados. Adiciona ou remove arquivos e grupos de arquivos de um banco de dados, altera os atributos de um banco de dados ou seus arquivos e grupos de arquivos, altera o agrupamento de banco de dados e define opções de banco de dados. Instantâneos de banco de dados não podem ser modificados. Para modificar opções de banco de dados associadas à replicação, use sp_replicationdboption.

Aplica-se a: SQL Server (SQL Server 2008 até a versão atual), Banco de dados SQL do Azure.

Devido a sua extensão, a sintaxe ALTER DATABASE está dividida nos seguintes tópicos:

• ALTER DATABASE
  O tópico atual fornece a sintaxe para alterar o nome e o agrupamento de um banco de dados.

• Opções de arquivo e grupo de arquivos de ALTER DATABASE
  Fornece a sintaxe para adicionar e remover arquivos e grupos de arquivos de um banco de dados e para alterar os atributos dos arquivos e grupos de arquivos.

• Opções de ALTER DATABASE SET
  Fornece a sintaxe para alterar os atributos de um banco de dados usando as opções SET de ALTER DATABASE.

• Espelhamento de banco de dados de ALTER DATABASE
  Fornece a sintaxe para as opções SET de ALTER DATABASE relacionadas a espelhamento de banco de dados.

• ALTER DATABASE SET HADR
  Fornece a sintaxe para as opções Grupos de Disponibilidade AlwaysOn de ALTER DATABASE para configurar um banco de dados secundário em uma réplica secundária de um grupo de disponibilidade AlwaysOn.

• Nível de compatibilidade de ALTER DATABASE
  Fornece a sintaxe para as opções SET de ALTER DATABASE relacionadas aos níveis de compatibilidade do banco de dados.

Convenções da sintaxe Transact-SQL

Sintaxe

-- 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 }
 [;]

Argumentos

• database_name
  É o nome do banco de dados a ser modificado.

  Dica

  Essa opção não está disponível em um banco de dados independente.

• CURRENT

  Aplica-se a: SQL Server 2012 ao SQL Server 2014.

  Designa que o banco de dados em uso deve ser alterado.

• MODIFY NAME **=**new_database_name
  Renomeia o banco de dados com o nome especificado como new_database_name.

• COLLATE collation_name

  Aplica-se a: SQL Server 2008 ao SQL Server 2014.

  Especifica o agrupamento do banco de dados. collation_name pode ser um nome de agrupamento do Windows ou um nome de agrupamento SQL. Se não especificado, o banco de dados será atribuído ao agrupamento da instância do SQL Server.

  Para obter mais informações sobre nomes de agrupamentos Windows e SQL, consulte COLLATE (Transact-SQL).

• MODIFY (MAXSIZE = [100 MB | 500 MB | 1 | 5 | 10 | 20 | 30 … 150…500] GB)

  Aplica-se a: Banco de dados SQL do Azure

  Especifica o tamanho máximo do banco de dados. O tamanho máximo deve estar em conformidade com o conjunto válido de valores da propriedade EDITION do banco de dados. A alteração do tamanho máximo do banco de dados pode fazer com que a EDIÇÃO do banco de dados seja alterada. A tabela a seguir lista os valores MAXSIZE com suporte e os padrões (D) para as camadas de serviço do Banco de dados SQL.

  MAXSIZE	Web	Business	Básica	Standard	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)

  As regras a seguir se aplicam aos argumentos MAXSIZE e EDITION:

  • O valor MAXSIZE, se especificado, precisa ser um valor válido exibido na tabela acima.

  • Se MAXSIZE for definido como um valor inferior a 5 GB e EDITION não for especificado, a edição do banco de dados será definida automaticamente como Web.

  • Se MAXSIZE for definido como um valor superior a 5 GB e EDITION não for especificado, a edição do banco de dados será definida automaticamente como Business.

  • Se EDITION for especificado, mas MAXSIZE não for especificado, o valor padrão da edição será usado. Por exemplo, se EDITION for definido como Standard e MAXSIZE não for especificado, MAXSIZE será automaticamente definido como 500 MB.

  • Se MAXSIZE nem EDITION forem especificados, EDITION será definido como Web e MAXZISE será definido como 1 GB.

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

  Aplica-se a: Banco de dados SQL do Azure

  Altera a edição do banco de dados. As camadas de serviço do Banco de dados SQL podem ser definidas ou modificadas por meio do parâmetro EDITION. A alteração de EDITION falhará se a propriedade MAXSIZE do banco de dados for definida para um valor fora do intervalo válido com suporte nessa edição.

  Importante

  As camadas de serviço de Negócios e Web serão desativadas em setembro de 2015.Para obter mais informações, consulte Perguntas frequentes sobre Web e Business.

• SERVICE_OBJECTIVE

  Aplica-se a: Banco de dados SQL do Azure

  Especifica o nível de desempenho. Para obter descrições do objetivo do serviço e mais informações sobre o tamanho, as edições e as combinações de objetivos do serviço, consulte Camadas de Serviço e Níveis de Desempenho do Banco de Dados SQL do Azure. Se o SERVICE_OBJECTIVE especificado não tiver suporte pela EDITION, você receberá um erro. Para alterar o valor SERVICE_OBJECTIVE de uma camada para outra (por exemplo, de S1 para P1), você deve alterar também o valor EDITION.

• <db_update_option> ::=

  Aplica-se a: Banco de dados SQL do Azure

  Controla se atualizações são permitidas no banco de dados.

  { READ_ONLY | READ_WRITE }

  • READ_ONLY
    Os usuários podem ler dados do banco de dados, mas não modificá-los.

  • READ_WRITE
    O banco de dados está disponível para operações de leitura e gravação.

  Dica

  Nos bancos de dados federados do Banco de dados SQL, SET { READ_ONLY | READ_WRITE } é desabilitado.

<delayed_durability_option> ::=

Aplica-se a: SQL Server 2014 ao SQL Server 2014.

Para obter mais informações, consulte Opções ALTER DATABASE SET (Transact-SQL) e Controlar a durabilidade da transação.

<file_and_filegroup_options >:: =

Para obter mais informações, consulte Opções de arquivo e grupos de arquivos ALTER DATABASE (Transact-SQL).

<set_database_options >:: =

Para obter mais informações, consulte Opções ALTER DATABASE SET (Transact-SQL), espelhamento de banco de dados ALTER DATABASE (Transact-SQL), ALTER DATABASE SET HADR (Transact-SQL) e Nível de compatibilidade de ALTER DATABASE (Transact-SQL).

Comentários

Para remover um banco de dados, use DROP DATABASE.

Para diminuir o tamanho de um banco de dados, use DBCC SHRINKDATABASE.

A instrução ALTER DATABASE deve ser executada em modo de confirmação automática (o modo padrão de administração de transações) e não deve ser permitida em uma transação explícita ou implícita.

O estado de um arquivo de banco de dados (por exemplo, online ou offline) é mantido independentemente do estado do banco de dados. Para obter mais informações, consulte Estados de arquivo. O estado dos arquivos dentro de um grupo de arquivos determina a disponibilidade de todo o grupo. Para que um grupo de arquivos fique disponível, todos os seus arquivos devem estar online. Se um grupo de arquivos estiver offline, qualquer tentativa de acessá-lo por meio de uma instrução SQL falhará com erro. Quando você cria planos de consulta para instruções SELECT, o otimizador de consultas evita índices não clusterizados e exibições indexadas que residam em grupos de arquivos offline. Isso permite que essas instruções tenham êxito. Porém, se o grupo de arquivos offline contiver o heap ou índice clusterizado da tabela de destino, as instruções SELECT falharão. Além disso, qualquer instrução INSERT, UPDATE ou DELETE que modifique uma tabela contendo um índice em um grupo de arquivos offline falhará.

Quando um banco de dados estiver em estado RESTORING, a maioria das instruções ALTER DATABASE falhará. A exceção está definindo opções de espelhamento de banco de dados. Um banco de dados pode estar no estado RESTORING durante uma operação de restauração ativa ou quando uma operação de restauração de um banco de dados ou arquivo de log falhar devido a um arquivo de backup corrompido.

O cache do plano para a instância do SQL Server é limpo pela configuração de uma das seguintes opções.

OFFLINE	READ_WRITE
ONLINE	MODIFY FILEGROUP DEFAULT
MODIFY_NAME	MODIFY FILEGROUP READ_WRITE
COLLATE	MODIFY FILEGROUP READ_ONLY
READ_ONLY	PAGE_VERIFY

A limpeza do cache de planos gera uma recompilação de todos os planos de execução subsequentes e pode causar uma queda repentina e temporária no desempenho de consultas. Para cada armazenamento em cache limpo no cache de planos, o log de erros do SQL Server contém a seguinte mensagem informativa: "O SQL Server encontrou %d ocorrência(s) de liberação de armazenamento em cache para o armazenamento em cache '%s' (parte do cache de planos) devido a algumas operações de manutenção do banco de dados e à reconfiguração de operações". Essa mensagem é registrada a cada cinco minutos, contanto que o cache seja liberado dentro desse intervalo de tempo.

O cache de procedimento também é liberado nos seguintes cenários:

• Um banco de dados tem a opção de banco de dados AUTO_CLOSE definida como ON. Quando nenhuma conexão de usuário fizer referência ou usar o banco de dados, a tarefa de banco de dados tentará fechar e encerrar o banco de dados automaticamente.

• Execute diversas consultas em um banco de dados que tem opções padrão. O banco de dados é removido.

• Um instantâneo de banco de dados para um banco de dados de origem é removido.

• Você recria com sucesso o log de transação para um banco de dados.

• Você restaura um backup de banco de dados.

• Você desanexa um banco de dados.

Alterando o agrupamento de banco de dados

Antes de aplicar um agrupamento diferente a um banco de dados, certifique-se de que existam as seguintes condições:

1. Você é o único usuário que está utilizando o banco de dados no momento.

2. Nenhum objeto associado ao esquema depende do agrupamento do banco de dados.

   Se os objetos a seguir, que dependem do agrupamento de banco de dados, existirem no banco de dados, a instrução ALTER DATABASEdatabase_nameCOLLATE falhará. O SQL Server retornará uma mensagem de erro para cada objeto que bloqueia a ação de ALTER:

   • Funções definidas pelo usuário e exibições criadas com SCHEMABINDING.

   • Colunas computadas.

   • Restrições CHECK.

   • Funções com valor de tabela que retornam tabelas com colunas de caracteres com agrupamentos herdados do agrupamento de banco de dados padrão.

   Informações de dependência de entidades não associadas a esquema são automaticamente atualizadas quando o agrupamento de banco de dados é alterado.

Alterar o agrupamento de banco de dados não cria duplicatas entre nenhum nome de sistema para os objetos de banco de dados. Se nomes duplicados resultarem do agrupamento alterados, os namespaces a seguir poderão provocar falha de alteração de agrupamento de banco de dados:

• Nomes de objeto, como procedimentos, tabelas, gatilhos ou exibições.

• Nomes de esquema.

• Entidades, como grupos, funções ou usuários.

• Nomes escalares, como tipos de sistema e tipos definidos pelo usuário.

• Nomes de catálogo de texto completo.

• Nomes de coluna ou parâmetro dentro de um objeto.

• Nomes de índice dentro de uma tabela.

Nomes duplicados resultantes do novo agrupamento provocarão falha na ação de alteração e o SQL Server retornará uma mensagem de erro especificando o namespace onde a duplicata foi encontrada.

Exibindo informações do banco de dados

É possível usar exibições do catálogo, funções do sistema e procedimentos armazenados do sistema para retornar informações sobre bancos de dados, arquivos e grupos de arquivos.

Permissões

SQL Server

Requer a permissão ALTER no banco de dados.

Banco de dados SQL do Azure

Somente o logon de entidade de segurança no nível do servidor (criado pelo processo de provisionamento) ou os membros da função de banco de dados dbmanager podem alterar um banco de dados.

Observação sobre segurança
O proprietário do banco de dados não pode alterar o banco de dados, a menos que seja um membro da função dbmanager.

Exemplos

A.Alterando o nome de um banco de dados

O exemplo a seguir altera o nome do banco de dados AdventureWorks2012 para Northwind.

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

B.Alterando o agrupamento de um banco de dados

O exemplo a seguir cria um banco de dados denominado testdb com o agrupamento SQL_Latin1_General_CP1_CI_AS e, em seguida, altera o agrupamento do banco de dados testdb para COLLATE French_CI_AI.

Aplica-se a: SQL Server 2008 ao 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

Consulte também

Referência

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)

Conceitos

Bancos de dados do sistema