ALTER DATABASE (Transact-SQL)

Modifica ciertas opciones de configuración de una base de datos.

En este artículo se proporciona la sintaxis, argumentos, comentarios, permisos y ejemplos para cualquier producto SQL que elija.

Para más información sobre las convenciones de sintaxis, vea Convenciones de sintaxis de Transact-SQL.

Selección de un producto

En la fila siguiente, seleccione el nombre del producto que le interese; de esta manera, solo se mostrará la información de ese producto.

* SQL Server *  

SQL Database

Instancia administrada de SQL

Azure Synapse
Análisis

Analytics Platform
System (PDW)

Microsoft Fabric

 

Introducción: SQL Server

En SQL Server, esta instrucción modifica una base de datos, o los archivos y grupos de archivos asociados a la base de datos. ALTER DATABASE 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 ALTER DATABASE se divide en varios artículos.

Artículo	Descripción
ALTER DATABASE	En el artículo actual se proporciona la sintaxis e información relacionada para cambiar el nombre y la intercalación de una base de datos.
Opciones File y Filegroup de ALTER DATABASE	Proporciona la sintaxis e información relacionada 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 de ALTER DATABASE SET	Proporciona la sintaxis e información relacionada 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 e información relacionada 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 e información relacionada 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 e información relacionada de las opciones SET de ALTER DATABASE relacionadas con los niveles de compatibilidad de la base de datos.
ALTER DATABASE SCOPED CONFIGURATION	Proporciona la sintaxis relacionada con las configuraciones con ámbito de base de datos utilizadas para la configuración del nivel de base de datos individual, como los comportamientos relacionados con la optimización y la ejecución de consultas.

Sintaxis

-- SQL Server Syntax
ALTER DATABASE { database_name | CURRENT }
{
    MODIFY NAME = new_database_name
  | COLLATE collation_name
  | <file_and_filegroup_options>
  | SET <option_spec> [ ,...n ] [ WITH <termination> ]
}
[;]

<file_and_filegroup_options>::=
  <add_or_modify_files>::=
  <filespec>::=
  <add_or_modify_filegroups>::=
  <filegroup_updatability_option>::=

<option_spec>::=
{
  | <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>
  | <query_store_options>
  | <recovery_option>
  | <service_broker_option>
  | <snapshot_option>
  | <sql_option>
  | <termination>
  | <temporal_history_retention>
  | <data_retention_policy>
  | <compatibility_level>
      { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 }
}

Argumentos

database_name

Es el nombre de la base de datos que se va a modificar.

Nota:

Esta opción no está disponible en una base de datos independiente.

CURRENT
Válido para : SQL Server 2012 (11.x) y versiones posteriores.

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

MODIFY NAME = new_database_name

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

Nota:

La intercalación no se puede cambiar una vez creada la base de datos en Azure SQL Database.

Al crear bases de datos con una intercalación diferente de la predeterminada, los datos de la base de datos siempre respetan la intercalación especificada. Para SQL Server, al crear una base de datos independiente, la información de catálogo interno se mantiene mediante la intercalación predeterminada de SQL Server, Latin1_General_100_CI_AS_WS_KS_SC.

Para más información sobre los nombres de intercalación de Windows y de SQL, consulte COLLATE.

<delayed_durability_option> ::=

Válido para : SQL Server 2014 (12.x) y versiones posteriores.

Para obtener más información, vea Opciones alter DATABASE SET y Controlar la durabilidad de las transacciones.

<file_and_filegroup_options>::=

Para más información, consulte Opciones File y Filegroup de ALTER DATABASE.

Observaciones

Para quitar una base de datos, use DROP DATABASE.

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

La ALTER DATABASE instrucción debe ejecutarse en modo de confirmación automática (el 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 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 de archivos deben estar en línea. Si un grupo de archivos está sin conexión, cualquier intento de acceder al grupo de archivos mediante una instrucción SQL produce 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. Además, se produce un error en cualquier INSERTinstrucción , UPDATEo DELETE que modifique una tabla con cualquier índice de un grupo de archivos sin conexión.

Cuando una base de datos está en estado RESTORE, se produce un error en la mayoría ALTER DATABASE de las instrucciones. La excepción es el establecimiento de opciones de creación de reflejo de la base de datos. Una base de datos puede estar en estado RESTORE 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 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.

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

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 caché de planes, el registro de errores de SQL Server contiene el siguiente mensaje informativo: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. Este mensaje se registra cada cinco minutos siempre que se vacíe la memoria caché dentro de ese intervalo de tiempo.

La caché de planes 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:

• Es el único usuario que utiliza actualmente la base de datos.
• 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, se produce un error en la ALTER DATABASE database_name COLLATE instrucción . SQL Server devuelve un mensaje de error para cada objeto que bloquea la ALTER acción:

• Vistas y funciones definidas por el usuario creadas con SCHEMABINDING
• Columnas calculadas
• CHECK, restricciones
• 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 los nombres duplicados resultan de la intercalación modificada, los siguientes espacios de nombres pueden provocar el error de un cambio de intercalación de 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 hacen que se produzca un error en la acción de cambio y SQL Server devuelve un mensaje de error que especifica el espacio de nombres donde se encontró el duplicado.

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

Debe tener el permiso ALTER para la base de datos.

Ejemplos

A. Cambio del nombre de una base de datos

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

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

B. Cambio de 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 (10.0.x) y versiones posteriores.

USE master;
GO

CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

Contenido relacionado

• CREATE DATABASE
• DATABASEPROPERTYEX
• DROP DATABASE
• SET TRANSACTION ISOLATION LEVEL
• EVENTDATA
• sp_configure
• sp_spaceused
• sys.databases
• sys.database_files
• sys.data_spaces
• sys.filegroups
• sys.master_files
• Bases de datos del sistema