ALTER DATABASE (Transact-SQL)

  • Artículo

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.

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

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> ::=
  <external_access_option> ::=
  <FILESTREAM_options> ::=
  <HADR_options> ::=  
  <parameterization_option> ::=
  <recovery_option> ::= 
  <service_broker_option> ::=
  <snapshot_option> ::=
  <sql_option> ::= 
  <termination> ::=

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
    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
    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 SQL, consulte COLLATE (Transact-SQL).

<file_and_filegroup_options >::=

Para obtener más información, vea 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.

En SQL Server 2005 o posterior, 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, vea 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 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 generan un error. Además, todas las instrucciones INSERT, UPDATE o DELETE que modifican una tabla con un índice en un grupo de archivos sin conexión generan un error.

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é del plan 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é del plan, se provoca una recompilació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é del plan, el registro de errores de SQL Server contendrá el siguiente mensaje informativo: "SQL Server ha detectado %d instancias de vaciado del almacén de caché '%s' (parte de la memoria caché del plan) debido a determinadas operaciones de mantenimiento de base de datos o reconfiguración". Este mensaje se registra cada cinco minutos siempre y cuando la memoria caché se vacíe 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 información de 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

Requiere el permiso ALTER en la base de datos.

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.

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)

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