Share via


ALTER DATABASE (Transact-SQL)

Modifica una base de datos o los archivos y grupos de archivos asociados a la base de datos. Agrega o quita archivos y grupos de archivos en una base de datos, cambia los atributos de una base de datos o de sus archivos y grupos de archivos, cambia la intercalación de base de datos y establece las opciones de base de datos. Las instantáneas de base de datos no se pueden modificar. Para modificar las opciones de base de datos asociadas a la replicación, utilice sp_replicationdboption.

Se aplica a: de SQL Server (SQL Server 2008 a la versión actual), Base de datos SQL de Azure.

Debido a su longitud, la sintaxis de ALTER DATABASE se divide en los temas siguientes:

  • ALTER DATABASE
    El tema actual proporciona la sintaxis para cambiar el nombre y la intercalación de una base de datos.

  • Opciones File y Filegroup de ALTER DATABASE
    Proporciona la sintaxis para agregar y eliminar archivos y grupos de archivos de una base de datos y para cambiar los atributos de archivos y grupos de archivos.

  • Opciones SET de ALTER DATABASE
    Proporcionan la sintaxis para cambiar los atributos de una base de datos usando las opciones SET de ALTER DATABASE.

  • Creación de reflejo de la base de datos de ALTER DATABASE
    Proporciona la sintaxis de las opciones SET de ALTER DATABASE relacionadas con la creación de reflejo de la base de datos.

  • ALTER DATABASE SET HADR
    Proporciona la sintaxis de las opciones Grupos de disponibilidad AlwaysOn de ALTER DATABASE para configurar una base de datos secundaria en una réplica secundaria de un grupo de disponibilidad AlwaysOn.

  • Nivel de compatibilidad de ALTER DATABASE
    Proporciona la sintaxis de las opciones SET de ALTER DATABASE relacionadas con los niveles de compatibilidad de la base de datos.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

-- 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
    Es el nombre de la base de datos que se va a modificar.

    Nota

    Esta opción no está disponible en las bases de datos independientes.

  • CURRENT

    Se aplica a: SQL Server 2012 a SQL Server 2014.

    Designa que la base de datos actual en uso se debe modificar.

  • MODIFY NAME **=**new_database_name
    Cambia el nombre de la base de datos por el nombre especificado como new_database_name.

  • COLLATE collation_name

    Se aplica a: SQL Server 2008 a SQL Server 2014.

    Especifica la intercalación de la base de datos. collation_name puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. Si no se especifica, se asigna a la base de datos la intercalación de la instancia de SQL Server.

    Para obtener más información acerca de los nombres de intercalación de Windows y de SQL, vea COLLATE (Transact-SQL).

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

    Se aplica a: Base de datos SQL de Azure

    Especifica el tamaño máximo de la base de datos. El tamaño máximo debe cumplir con el conjunto válido de valores de la propiedad EDITION de la base de datos. Cambiar el tamaño máximo de la base de datos puede causar que cambie también el valor de EDITION de la base de datos. En la tabla siguiente se muestran los valores admitidos de MAXSIZE y los valores predeterminados (D) para los niveles de servicio de Base de datos SQL.

    MAXSIZE

    Web

    Negocio

    Basic

    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)

    Las reglas siguientes se aplican a los argumentos MAXSIZE y EDITION:

    • El valor de MAXSIZE, si se especifica, tiene que ser un valor válido mostrado en la tabla anterior.

    • Si MAXSIZE está establecido en un valor menor que 5 GB y EDITION no se especifica, la edición de la base de datos se establecerá automáticamente en Web.

    • Si MAXSIZE está establecido en un valor mayor que 5 GB y EDITION no se especifica, la edición de la base de datos se establecerá automáticamente en Business.

    • Si se especifica EDITION pero no se especifica MAXSIZE, se usa el valor predeterminado de la edición. Por ejemplo, si EDITION está establecido en Standard y MAXSIZE no se especifica, el valor de MAXSIZE se establece automáticamente en 500 MB.

    • Si no se especifica MAXSIZE ni EDITION, EDITION se establece en Web y MAXSIZE en 1 GB.

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

    Se aplica a: Base de datos SQL de Azure

    Cambia la edición de la base de datos. Los niveles de servicio de Base de datos SQL se pueden establecer o modificar mediante el parámetro EDITION. El cambio de EDITION no se realizará si la propiedad MAXSIZE de la base de datos está establecida en un valor fuera del intervalo válido admitido por esa edición.

    Importante

    Los niveles de servicio Web y Business se retirarán en septiembre de 2015.Para obtener más información, vea Preguntas más frecuentes Web y Business.

  • SERVICE_OBJECTIVE

    Se aplica a: Base de datos SQL de Azure

    Especifica el nivel de rendimiento. Para conocer las descripciones de los objetivos de servicio y obtener más información sobre las combinaciones de tamaño, ediciones y objetivos de servicio, vea Niveles de servicio y niveles de rendimiento de la Base de datos SQL de Azure. Si EDITION no admite el valor SERVICE_OBJECTIVE especificado, se devuelve un error. Para cambiar el valor SERVICE_OBJECTIVE de un nivel a otro (por ejemplo, de S1 a P1), también debe cambiar el valor EDITION.

  • <db_update_option> ::=

    Se aplica a: Base de datos SQL de Azure

    Controla si se permiten las actualizaciones en la base de datos.

    { READ_ONLY | READ_WRITE }

    • READ_ONLY
      Los usuarios pueden leer datos de la base de datos, pero no pueden modificarlos.

    • READ_WRITE
      La base de datos está disponible para operaciones de lectura y escritura.

    Nota

    En las bases de datos federadas de Base de datos SQL, SET { READ_ONLY | READ_WRITE } está deshabilitado.

<delayed_durability_option> ::=

Se aplica a: SQL Server 2014 a SQL Server 2014.

Para obtener más información, vea Opciones de ALTER DATABASE SET (Transact-SQL) y Controlar la durabilidad de las transacciones.

<file_and_filegroup_options >::=

Para obtener más información, consulte Opciones File y Filegroup de ALTER DATABASE (Transact-SQL).

<set_database_options >::=

Para obtener más información, vea Opciones de ALTER DATABASE SET (Transact-SQL), Reflejo de la base de datos ALTER DATABASE (Transact-SQL), ALTER DATABASE SET HADR (Transact-SQL) y Nivel de compatibilidad de ALTER DATABASE (Transact-SQL).

Comentarios

Para quitar una base de datos, utilice DROP DATABASE.

Para reducir el tamaño de una base de datos, utilice DBCC SHRINKDATABASE.

La instrucción ALTER DATABASE se debe ejecutar en el modo de confirmación automática (modo de administración de transacciones predeterminado) y no se permite en una transacción explícita o implícita.

El estado de un archivo de base de datos (por ejemplo, en línea o sin conexión) se mantiene con independencia del estado de la base de datos. Para obtener más información, consulte Estados de los archivos. El estado de los archivos de un grupo de archivos determina la disponibilidad de todo el grupo de archivos. Para que un grupo de archivos esté disponible, todos los archivos del grupo de archivos deben estar en línea. Si un grupo de archivos se encuentra en modo sin conexión, todos los intentos de acceso al grupo de archivos por parte de una instrucción SQL generan un error. Al generar un plan de consulta para las instrucciones SELECT, el optimizador de consultas evita los índices no clúster y las vistas indizadas que residen en los grupos de archivos sin conexión. Esto permite que las instrucciones se ejecuten correctamente. No obstante, si el grupo de archivos sin conexión contiene el montón o el índice clúster de la tabla de destino, las instrucciones SELECT no funcionarán. Adicionalmente, cualquier instrucción INSERT, UPDATE o DELETE que modifique una tabla con cualquier índice en un grupo de archivos sin conexión no funcionará.

Si una base de datos se encuentra en estado RESTORING, se producirán errores en la mayoría de las instrucciones ALTER DATABASE. La excepción es el establecimiento de opciones de creación de reflejo de la base de datos. Es posible que una base de datos se encuentre en estado RESTORING durante una operación de restauración activa o cuando se produce un error en una operación de restauración de una base de datos o de un archivo de registro, debido a un archivo de copia de seguridad dañado.

La memoria caché de planes para la instancia de SQL Server se borra si se establece alguna de las opciones siguientes.

OFFLINE

READ_WRITE

ONLINE

MODIFY FILEGROUP DEFAULT

MODIFY_NAME

MODIFY FILEGROUP READ_WRITE

COLLATE

MODIFY FILEGROUP READ_ONLY

READ_ONLY

PAGE_VERIFY

Al borrar la memoria caché de planes, se provoca una nueva compilación de todos los planes de ejecución posteriores y puede ocasionar una disminución repentina y temporal del rendimiento de las consultas. Para cada almacén de caché borrado de la memoria caché de planes, el registro de errores de SQL Server contiene el siguiente mensaje informativo: "SQL Server ha detectado %d instancias de vaciado de almacén de caché '%s' (parte de la memoria caché de planes) debido a determinadas operaciones de mantenimiento de base de datos o reconfiguración". Este mensaje se registra cada cinco minutos siempre que se vacíe la memoria caché dentro de ese intervalo de tiempo.

La memoria caché de procedimientos también se vacía en los escenarios siguientes:

  • Una base de datos tiene la opción de base de datos AUTO_CLOSE establecida en ON. Cuando ninguna conexión de usuario hace referencia a la base de datos ni la usa, la tarea de segundo plano intenta cerrar la base de datos y apagarla de modo automático.

  • Ejecuta varias consultas con una base de datos que tiene opciones predeterminadas. Después, la base de datos se quita.

  • Se quita una instantánea de base de datos para una base de datos de origen.

  • Volvió a generar correctamente el registro de transacciones para una base de datos.

  • Restaura una copia de seguridad de una base de datos

  • Separa una base de datos.

Cambiar la intercalación de la base de datos

Antes de aplicar otra intercalación a una base de datos, asegúrese de que se cumplen las siguientes condiciones:

  1. Es el único usuario que utiliza actualmente la base de datos.

  2. Ningún objeto enlazado a un esquema depende de la intercalación de la base de datos.

    Si los objetos siguientes, que dependen de la intercalación de la base de datos, existen en la base de datos, la instrucción ALTER DATABASEdatabase_nameCOLLATE producirá un error. SQL Server devolverá un mensaje de error para cada objeto que bloquee la acción de ALTER:

    • Vistas y funciones definidas por el usuario creadas con SCHEMABINDING

    • Columnas calculadas

    • Restricciones CHECK

    • Funciones con valores de tabla que devuelven tablas con columnas de caracteres con intercalaciones heredadas de la intercalación predeterminada de la base de datos

    La información de dependencia de las entidades no vinculadas a esquemas se actualiza automáticamente si se cambia la intercalación de la base de datos.

Cambiar la intercalación de la base de datos no crea duplicados entre los nombres del sistema para los objetos de base de datos. Si se producen nombres duplicados en la intercalación cambiada, los siguientes espacios de nombres pueden provocar errores en el cambio de la intercalación de la base de datos:

  • Nombres de objetos, como un procedimiento, una tabla, un desencadenador o una vista

  • Nombres de esquemas.

  • Entidades de seguridad, como un grupo, rol o usuario

  • Nombres de tipo escalar, como los tipos definidos por el usuario y por el sistema

  • Nombres de catálogos de texto completo

  • Nombres de columnas o parámetros en un objeto

  • Nombres de índices en una tabla

Los nombres duplicados resultantes de la nueva intercalación provocarán que la acción de cambio no se ejecute correctamente y SQL Server devolverá un mensaje de error que especifica el espacio de nombres donde se ha encontrado el duplicado.

Ver la información de la base de datos

Se pueden utilizar vistas de catálogo, funciones del sistema y procedimientos almacenados del sistema para devolver información sobre bases de datos, archivos y grupos de archivos.

Permisos

SQL Server

Requiere el permiso ALTER en la base de datos.

Base de datos SQL de Azure

Solo el inicio de sesión principal de nivel de servidor (creado por el proceso de aprovisionamiento) o los miembros del rol de base de datos dbmanager pueden modificar una base de datos.

Nota de seguridadNota de seguridad

El propietario de la base de datos no puede modificarla a menos que sea miembro del rol dbmanager.

Ejemplos

A.Cambiar el nombre de una base de datos

En el ejemplo siguiente se cambia el nombre de la base de datos AdventureWorks2012 a Northwind.

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

B.Cambiar la intercalación de una base de datos

En el siguiente ejemplo se crea una base de datos denominada testdb con la intercalación SQL_Latin1_General_CP1_CI_AS, y luego se cambia la intercalación de la base de datos testdb a COLLATE French_CI_AI.

Se aplica a: SQL Server 2008 a 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

Vea también

Referencia

CREATE DATABASE (Transact-SQL de SQL Server)

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)

Conceptos

Bases de datos del sistema