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 bases de datos no se pueden modificar. Para modificar las opciones de base de datos asociadas a la réplica, utilice sp_replicationdboption.
Para quitar una base de datos, utilice DROP DATABASE.
Para cambiar el nombre de una base de datos, utilice la opción MODIFY NAME = new_database_name con ALTER DATABASE.
Para reducir el tamaño de una base de datos, utilice DBCC SHRINKDATABASE.
No se puede agregar o quitar un archivo mientras se está ejecutando una instrucción BACKUP.
Para cada base de datos se puede especificar un máximo de 32.767 archivos y 32.767 grupos de archivos.
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. Para obtener más información, vea Transacciones de confirmación automática.
En SQL Server 2005, el estado de un archivo de base de datos (por ejemplo, con conexión 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 tener conexión. Si un grupo de archivos no tiene 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 consultas para las instrucciones SELECT, el optimizador de consultas evita los índices no agrupados 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 agrupado 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 base de datos. Una base de datos puede encontrarse 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. Para obtener más información, vea Responder a errores de restauración de SQL Server provocados por copias de seguridad dañadas.
Configurar opciones
Para recuperar la configuración actual de las opciones de base de datos, utilice la vista de catálogo sys.databases o DATABASEPROPERTYEX. Para obtener una lista de los valores predeterminados asignados a la base de datos durante su creación, vea Configurar las opciones de la base de datos.
Una vez configurada una opción de la base de datos, la modificación surte efecto de inmediato.
Para cambiar los valores predeterminados de cualquiera de las opciones de las bases de datos que se acaban de crear, cambie la opción adecuada en la base de datos model.
No todas las opciones de base de datos utilizan la cláusula WITH <termination> o se pueden especificar en combinación con otras opciones. En la siguiente tabla se incluyen estas opciones, su estado y el estado de terminación.
|
Categoría de opciones
|
Se puede especificar con otras opciones
|
Puede utilizar la cláusula WITH <termination>
|
|---|
|
<db_state_option>
|
Sí
|
Sí
|
|
<db_user_access_option>
|
Sí
|
Sí
|
|
db_update_option>
|
Sí
|
Sí
|
|
<external_access_option>
|
Sí
|
No
|
|
<cursor_option>
|
Sí
|
No
|
|
<auto_option>
|
Sí
|
No
|
|
<sql_option>
|
Sí
|
No
|
|
<recovery_option>
|
Sí
|
No
|
|
<database_mirroring_option>
|
No
|
No
|
|
ALLOW_SNAPSHOT_ISOLATION
|
No
|
No
|
|
READ_COMMITTED_SNAPSHOT
|
No
|
Sí
|
|
<service_broker_option>
|
Sí
|
No
|
|
DATE_CORRELATION_OPTIMIZATION
|
Sí
|
Sí
|
|
<parameterization_option>
|
Sí
|
Sí
|
La 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
|
|
Al borrar la caché del plan, 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. En el Service Pack 2 de SQL Server 2005, para cada almacén de caché borrado de la 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 caché del plan) debido a determinadas operaciones de mantenimiento de base de datos o reconfiguración". Este mensaje se registra cada cinco minutos siempre que se vacía la caché dentro de ese intervalo de tiempo.
Mover archivos
En SQL Server 2005, puede mover los archivos de sistema, de datos definidos por el usuario o de registro si especifica la ubicación nueva en FILENAME. Esto puede resultar útil en los siguientes escenarios:
-
Recuperación de errores. Por ejemplo, la base de datos está en modo de sospecha o cerrada debido a un error del hardware.
-
Reubicación planeada.
-
Reubicación para mantenimiento de disco programado.
Para obtener más información, vea Mover archivos de base de datos.
Inicializar archivos
De forma predeterminada, los archivos de datos y registro se inicializan mediante el relleno de los archivos con ceros al realizar una de las siguientes operaciones:
-
Crear una base de datos
-
Agregar archivos a una base de datos existente
-
Aumentar el tamaño de un archivo existente
-
Restaurar una base de datos o un grupo de archivos
En SQL Server 2005, los archivos de datos se pueden inicializar de forma instantánea. Esto permite la ejecución rápida de estas operaciones con los archivos. Para obtener más información, vea Inicialización de archivos de 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 siguientes objetos, los cuales dependen de la intercalación de la base de datos, existen en la base de datos, la instrucción ALTER DATABASE database_name COLLATE genera un error. SQL Server devuelve un mensaje de error para cada objeto que bloquee la acción ALTER:
-
Vistas y funciones definidas por el usuario creadas con SCHEMABINDING
-
Columnas calculadas
-
Restricciones CHECK
-
Funciones de valores de tabla que devuelven tablas con columnas de caracteres con intercalaciones heredadas de la intercalación predeterminada 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.
Los siguientes espacios de nombres pueden provocar errores en el cambio de la intercalación de la base de datos si se producen nombres duplicados en la intercalación cambiada:
-
Nombres de objetos, como un procedimiento, tabla, desencadenador o vista
-
Nombres de esquemas
-
Entidades de seguridad, como un grupo, función o usuario
-
Nombres de tipo escalar, como los tipos definidos por el usuario y por el sistema
-
Nombres de catálogos de texto
-
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
Puede utilizar vistas de catálogo, funciones del sistema y procedimientos almacenados del sistema para devolver información sobre las bases de datos, los archivos y los grupos de archivos. Para obtener más información, vea Ver los metadatos de una base de datos.
A. Agregar un archivo a una base de datos
En el siguiente ejemplo se agrega un archivo de datos de 5 MB a la base de datos AdventureWorks.
USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks
ADD FILE
(
NAME = Test1dat2,
FILENAME = '''+ @data_path + 't1dat2.ndf'',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)'
);
GO
B. Agregar a una base de datos un grupo de archivos con dos archivos
En el siguiente ejemplo se crea el grupo de archivos Test1FG1 en la base de datos AdventureWorks y se agregan dos archivos de 5 MB al grupo de archivos.
USE master
GO
ALTER DATABASE AdventureWorks
ADD FILEGROUP Test1FG1;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks
ADD FILE
(
NAME = test1dat3,
FILENAME = '''+ @data_path + 't1dat3.ndf'',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
),
(
NAME = test1dat4,
FILENAME = '''+ @data_path + 't1dat4.ndf'',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1'
);
GO
C. Agregar dos archivos de registro a una base de datos
En el siguiente ejemplo se agregan dos archivos de registro de 5 MB a la base de datos AdventureWorks.
USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks
ADD LOG FILE
(
NAME = test1log2,
FILENAME = '''+ @data_path + 'test2log.ldf'',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
),
(
NAME = test1log3,
FILENAME = '''+ @data_path + 'test3log.ldf'',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)'
);
GO
D. Quitar un archivo de una base de datos
En el siguiente ejemplo se quita uno de los archivos agregados en el ejemplo B.
USE master;
GO
ALTER DATABASE AdventureWorks
REMOVE FILE test1dat4;
GO
E. Modificar un archivo
En el siguiente ejemplo aumenta el tamaño de uno de los archivos agregados en el ejemplo B.
USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB);
GO
F. Mover un archivo a otra ubicación
En el siguiente ejemplo se mueve el archivo Test1dat2 creado en el ejemplo A a otro directorio.
.gif) Nota: |
|---|
|
Debe mover físicamente el archivo al directorio nuevo antes de ejecutar este ejemplo. A continuación, detenga e inicie la instancia de SQL Server o establezca la base de datos AdventureWorks en OFFLINE y después en ONLINE para implementar el cambio.
|
USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(
NAME = Test1dat2,
FILENAME = N'c:\t1dat2.ndf'
);
GO
G. Mover tempdb a otra ubicación
En el siguiente ejemplo se mueve tempdb de su ubicación actual en el disco a otra ubicación del disco. Puesto que tempdb se vuelve a crear cada vez que se inicia el servicio MSSQLSERVER, no es necesario mover físicamente los archivos de datos y de registro. Los archivos se crean cuando se reinicia el servicio en el paso 3. Hasta que se reinicia, tempdb seguirá funcionando en su ubicación existente.
-
Determine los nombres de los archivos lógicos de la base de datos
tempdb y su ubicación actual en el disco.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
-
Cambie la ubicación de cada archivo con
ALTER DATABASE.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO
-
Detenga y reinicie la instancia de SQL Server.
-
Compruebe el cambio de los archivos.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
-
Elimine los archivos tempdb.mdf y templog.ldf de su ubicación original.
H. Establecer un grupo de archivos como predeterminado
En el siguiente ejemplo, el grupo de archivos Test1FG1 creado en el ejemplo B se establece como predeterminado. A continuación, el grupo de archivos predeterminado se restablece al grupo de archivos PRIMARY. Tenga en cuenta que PRIMARY se debe delimitar con corchetes o comillas.
USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO
I. Configurar opciones en una base de datos
En el siguiente ejemplo se establece el modelo de recuperación y las opciones de comprobación de páginas de datos para la base de datos de ejemplo AdventureWorks .
USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO
J. Establecer la base de datos en READ_ONLY
El cambio de una base de datos o un grupo de archivos a READ_ONLY o READ_WRITE requiere el acceso exclusivo a la base de datos. En el siguiente ejemplo la base de datos se establece en el modo SINGLE_USER para tener acceso exclusivo. A continuación, el ejemplo establece el estado de la base de datos AdventureWorks en READ_ONLY y devuelve el acceso a la base de datos a todos los usuarios.
| Nota: |
|---|
En este ejemplo se utiliza la opción de terminación WITH ROLLBACK IMMEDIATE en la primera instrucción ALTER DATABASE. Todas las transacciones incompletas se revierten y el resto de las conexiones a la base de datos de ejemplo AdventureWorks se desconectan de inmediato.
|
USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO
K. Habilitar el asilamiento de instantánea en una base de datos
En el siguiente ejemplo se habilita la opción del marco de aislamiento de instantánea para la base de datos AdventureWorks.
USE AdventureWorks;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO
USE master;
GO
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check again.
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO
El conjunto de resultados muestra que el marco de aislamiento de instantánea está habilitado.
name snapshot_isolation_state description
--------------- ------------------------ -----------
AdventureWorks 1 ON
L. Crear una sesión de creación de reflejo de la base de datos con un testigo
La configuración de la creación de reflejo de la base de datos con un testigo requiere configurar la seguridad y preparar la base de datos reflejada además de utilizar ALTER DATABASE para configurar los asociados. Para obtener un ejemplo del proceso de configuración completo, vea Configurar la creación de reflejo de la base de datos.
M. Conmutación por error manual en una sesión de creación de reflejo de la base de datos
La conmutación por error manual se puede iniciar desde cualquier asociado de creación de reflejo de la base de datos. Antes de llevar a cabo la conmutación por error, debe comprobar si el servidor principal actual es realmente el servidor principal. Por ejemplo, para la base de datos AdventureWorks, ejecute la siguiente consulta en la instancia del servidor que crea que es el servidor principal actual:
SELECT db.name, m.mirroring_role_desc
FROM sys.database_mirroring m
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'AdventureWorks'
GO
Si la instancia del servidor es la entidad de seguridad, el valor de mirroring_role_desc es Principal. Si esta instancia del servidor se corresponde con el servidor reflejado, la instrucción SELECT debe devolver Mirror.
En el siguiente ejemplo se da por supuesto que el servidor es la entidad de seguridad actual.
-
Realice una conmutación por error manual al asociado de creación de reflejo de la base de datos:
ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;
GO
-
Para comprobar los resultados de la conmutación por error en el reflejo nuevo, ejecute la siguiente consulta:
SELECT name, mirroring_role_desc
FROM sys.databases WHERE name = N'AdventureWorks';
GO
El valor actual de mirroring_role_desc es ahora Mirror.