Opciones File y Filegroup de ALTER DATABASE (Transact-SQL)

Modifica los archivos y los grupos de archivos asociados a la base de datos. Agrega o quita archivos y grupos de archivos de una base de datos, cambia los atributos de una base de datos o sus archivos y grupos de archivos. Para obtener información acerca de otras opciones de ALTER DATABASE, vea ALTER DATABASE (Transact-SQL).

Convenciones de sintaxis de Transact-SQL

Sintaxis

ALTER DATABASE database_name 
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ] 
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ] 
  | REMOVE FILE logical_file_name 
  | MODIFY FILE <filespec>
}

<filespec>::= 
(
    NAME = logical_file_name  
    [ , NEWNAME = new_logical_name ] 
    [ , FILENAME = {'os_file_name' | 'filestream_path' } ] 
    [ , SIZE = size [ KB | MB | GB | TB ] ] 
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 
    [ , OFFLINE ]
) 

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name 
        [ CONTAINS FILESTREAM ]
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option> 
        | DEFAULT
        | NAME = new_filegroup_name 
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE } 
    | { READ_ONLY | READ_WRITE }
}

Argumentos

<add_or_modify_files>::=

Especifica el archivo que se va a agregar, quitar o modificar.

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

• ADD FILE
  Agrega un archivo a la base de datos.

  • TO FILEGROUP { filegroup_name }
    Especifica el grupo de archivos al que se agrega el archivo especificado. Para mostrar los grupos de archivos actuales y qué grupo de archivos es el predeterminado, utilice la vista de catálogo sys.filegroups.

• ADD LOG FILE
  Agrega un archivo de registro a la base de datos especificada.

• REMOVE FILE logical_file_name
  Quita la descripción del archivo lógico de una instancia de SQL Server y elimina el archivo físico. El archivo no se puede quitar a menos que esté vacío.

  • logical_file_name
    Es el nombre lógico utilizado en SQL Server cuando se hace referencia al archivo.

• MODIFY FILE
  Especifica el archivo que se debe modificar. Sólo se puede cambiar una propiedad <especificaciónDeArchivo> cada vez. NAME se debe especificar siempre en <especificaciónDeArchivo> para identificar el archivo que se va a modificar. Si se especifica SIZE, el nuevo tamaño debe ser mayor que el tamaño actual del archivo.

  Para modificar el nombre lógico de un archivo de datos o de un archivo de registro, especifique el nombre del archivo lógico que se va a cambiar en la cláusula NAME y especifique el nombre lógico nuevo para el archivo en la cláusula NEWNAME. Por ejemplo:

  MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 
  

  Para mover un archivo de datos o un archivo de registro a otra ubicación, especifique el nombre del archivo lógico actual en la cláusula NAME y especifique la ruta y el nombre del archivo del sistema operativo nuevos en la cláusula FILENAME. Por ejemplo:

  MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )
  

  Si mueve un catálogo de texto completo, especifique la ruta nueva en la cláusula FILENAME. No especifique el nombre del archivo del sistema operativo.

  Para obtener más información, vea Mover archivos de base de datos.

  Para un grupo de archivos FILESTREAM, NAME se puede modificar en línea. Aunque FILENAME se puede modificar en línea, el cambio no surtirá efecto hasta que el contenedor se haya reubicado físicamente y se haya apagado y reiniciado el servidor.

  Puede establecer un archivo FILESTREAM en OFFLINE. Cuando un archivo FILESTREAM está sin conexión, su grupo de archivos principal se marcará internamente como sin conexión; por consiguiente, se producirá un error en cualquier intento de acceso a los datos FILESTREAM situados dentro de ese grupo de archivos.

<filespec>::=

Controla las propiedades del archivo.

• NAME logical_file_name
  Especifica el nombre lógico del archivo.

  • logical_file_name
    Es el nombre lógico utilizado en una instancia de SQL Server al hacer referencia al archivo.

• NEWNAME new_logical_file_name
  Especifica un nombre lógico nuevo para el archivo.

  • new_logical_file_name
    Es el nombre que reemplaza el nombre del archivo lógico existente. El nombre debe ser único en la base de datos y debe cumplir las mismas reglas que los identificadores. El nombre puede ser una constante Unicode o de caracteres, un identificador regular o un identificador delimitado. Para obtener más información, vea Usar identificadores como nombres de objeto.

• FILENAME { 'os_file_name' | 'filestream_path' }
  Especifica el nombre de archivo (físico) del sistema operativo.

  • ' os_file_name '
    Para un grupo de archivos estándar (ROWS), se trata de la ruta de acceso y el nombre de archivo que utiliza el sistema operativo al crear el archivo. El archivo debe residir en el servidor donde esté instalado SQL Server. La ruta especificada debe existir antes de ejecutar la instrucción ALTER DATABASE.

    Los parámetros SIZE, MAXSIZE y FILEGROWTH no se pueden establecer si se ha especificado una ruta UNC para el archivo.

    Nota

    Las bases de datos del sistema no pueden residir en directorios de recursos compartidos UNC.

    Los archivos de datos no se pueden utilizar en los sistemas de archivos comprimidos a menos que sean archivos secundarios de solo lectura o si la base de datos es de solo lectura. Los archivos de registro no se pueden utilizar en sistemas de archivos comprimidos. Para obtener más información, vea Grupos de archivos de sólo lectura y compresión.

    Si el archivo se encuentra en una partición sin formato, os_file_name solo debe indicar la letra de unidad de una partición sin formato existente. En cada partición sin formato solo se puede colocar un archivo.

  • 'filestream_path'
    Para un grupo de archivos FILESTREAM, FILENAME hace referencia a la ruta de acceso donde se almacenarán los datos FILESTREAM. La ruta de acceso hasta la última carpeta debe existir y la última carpeta no debe existir. Por ejemplo, si especifica la ruta de acceso C:\MyFiles\MyFilestreamData, C:\MyFiles debe existir antes de ejecutar ALTER DATABASE, pero la carpeta MyFilestreamData no debe existir.

    El grupo de archivos y el archivo (<filespec>) se deben crear en la misma instrucción. Sólo puede haber un archivo, <filespec>, para un grupo de archivos FILESTREAM.

    Las propiedades SIZE, MAXSIZE y FILEGROWTH no se aplican a un grupo de archivos FILESTREAM.

• SIZE size
  Especifica el tamaño del archivo. SIZE no se aplica a los grupos de archivos FILESTREAM.

  • size
    Es el tamaño del archivo.

    Cuando se especifica con ADD FILE, size es el tamaño inicial del archivo. Si se especifica con MODIFY FILE, size es el nuevo tamaño de archivo y debe ser mayor que el tamaño de archivo actual.

    Cuando no se suministra size para el archivo principal, SQL Server utiliza el tamaño del archivo principal de la base de datos model. Cuando se especifica un archivo de datos secundario o un archivo de registro, pero no se especifica el argumento size para ese archivo, el Motor de base de datos hace que el tamaño del archivo sea de 1 MB.

    Se pueden utilizar los sufijos KB, MB, GB y TB para especificar kilobytes, megabytes, gigabytes o terabytes. El valor predeterminado es MB. Especifique un número entero y no incluya decimales. Para especificar una fracción de un megabyte, convierta el valor en kilobytes multiplicando el número por 1024. Por ejemplo, especifique 1536 KB en lugar de 1,5 MB (1,5 x 1024 = 1536).

• MAXSIZE { max_size| UNLIMITED }
  Especifica el tamaño máximo que puede alcanzar el archivo. MAXSIZE no se aplica a los grupos de archivos FILESTREAM.

  • max_size
    Es el tamaño máximo del archivo. Se pueden utilizar los sufijos KB, MB, GB y TB para especificar kilobytes, megabytes, gigabytes o terabytes. El valor predeterminado es MB. Especifique un número entero y no incluya decimales. Si no se especifica max_size, el tamaño de archivo aumenta hasta que el disco esté lleno.

  • UNLIMITED
    Especifica que el tamaño del archivo aumenta hasta que el disco esté lleno. En SQL Server, si se especifica un crecimiento ilimitado para un archivo de registro, su tamaño máximo será de 2 TB, y para un archivo de datos será de 16 TB.

• FILEGROWTH growth_increment
  Especifica el incremento de crecimiento automático del archivo. El valor FILEGROWTH de un archivo no puede superar el valor MAXSIZE. FILEGROWTH no se aplica a los grupos de archivos FILESTREAM.

  • growth_increment
    Es la cantidad de espacio que se agrega al archivo siempre que se necesita más espacio.

    El valor se puede especificar en MB, KB, GB, TB o como porcentaje (%). Si se especifica un número sin los sufijos MB, KB o %, el valor predeterminado es MB. Cuando se especifica %, el incremento de crecimiento es el porcentaje especificado del tamaño del archivo en el momento en que tiene lugar el incremento. El tamaño especificado se redondea al múltiplo de 64 KB más cercano.

    El valor 0 indica que el aumento automático se establece en OFF y no se permite ningún espacio adicional.

    Si no se especifica FILEGROWTH, el valor predeterminado es 1 MB para los archivos de datos y el 10% para los archivos de registro, y el valor mínimo es 64 KB.

    Nota

    A partir de SQL Server 2005, el incremento de crecimiento predeterminado para los archivos de datos ha cambiado del 10% a 1 MB. El valor predeterminado del 10% del archivo de registro no varía.

• OFFLINE
  Establece el archivo en modo sin conexión e impide el acceso a todos los objetos del grupo de archivos.

  Advertencia
  Utilice esta opción solo si el archivo está dañado y se puede restaurar. Un archivo establecido en OFFLINE solo se puede establecer como en línea mediante la restauración del archivo a partir de una copia de seguridad. Para obtener más información acerca de cómo restaurar un solo archivo, vea RESTORE (Transact-SQL).

<add_or_modify_filegroups>::=

Agrega, modifica o quita un grupo de archivos de la base de datos.

• ADD FILEGROUP filegroup_name
  Agrega un grupo de archivos a la base de datos.

• CONTAINS FILESTREAM
  Especifica que el grupo de archivos almacena objetos binarios grandes (BLOB) de FILESTREAM en el sistema de archivos.

• REMOVE FILEGROUP filegroup_name
  Quita un grupo de archivos de la base de datos. El grupo de archivos no se puede quitar a menos que esté vacío. Quita todos los archivos del primer grupo de archivos. Para obtener más información, vea "REMOVE FILE logical_file_name", anteriormente en este tema.

• MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME = new_filegroup_name }
  Modifica el grupo de archivos al establecer el estado en READ_ONLY o READ_WRITE, lo que hace que el grupo de archivos se convierta en predeterminado para la base de datos o que se cambie el nombre del grupo de archivos.

  • <filegroup_updatability_option>
    Establece la propiedad de solo lectura o solo lectura/escritura para el grupo de archivos.

  • DEFAULT
    Cambia el grupo de archivos predeterminado de la base de datos a filegroup_name. Sólo un grupo de archivos de la base de datos puede ser el grupo de archivos predeterminado. Para obtener más información, vea Descripción de archivos y grupos de archivos.

  • NAME = new_filegroup_name
    Cambia el nombre del grupo de archivos a new_filegroup_name.

<filegroup_updatability_option>::=

Establece la propiedad de solo lectura o solo lectura/escritura para el grupo de archivos.

• READ_ONLY | READONLY
  Especifica que el grupo de archivos es de solo lectura. No se permite la actualización de los objetos del mismo. El grupo de archivos principal no puede ser de solo lectura. Para cambiar este estado, debe tener acceso exclusivo a la base de datos. Para obtener más información, vea la cláusula SINGLE_USER.

  Una base de datos de solo lectura no permite realizar modificaciones en los datos:

  • Se omite la recuperación automática cuando se inicia el sistema.

  • No es posible reducir la base de datos.

  • No se produce ningún bloqueo en las bases de datos de solo lectura. Esto puede acelerar el rendimiento de las consultas.

  Nota

  La palabra clave READONLY se quitará en una versión futura de Microsoft SQL Server. Evite el uso de READONLY en los nuevos trabajos de desarrollo y piense en modificar las aplicaciones que utilizan READONLY actualmente. Utilice READ_ONLY en su lugar.

• READ_WRITE | READWRITE
  Especifica que el grupo es READ_WRITE. Pueden realizarse actualizaciones en los objetos del grupo de archivos. Para cambiar este estado, debe tener acceso exclusivo a la base de datos. Para obtener más información, vea la cláusula SINGLE_USER.

  Nota

  La palabra clave READWRITE se quitará en una versión futura de Microsoft SQL Server. Evite el uso de READWRITE en los nuevos trabajos de desarrollo y piense en modificar las aplicaciones que utilizan READWRITE actualmente. Utilice READ_WRITE en su lugar.

Puede determinar el estado de estas opciones si examina la columna is_read_only de la vista de catálogo sys.databases o la propiedad Updateability de la función DATABASEPROPERTYEX.

Comentarios

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 pueden especificarse hasta 32.767 archivos y 32.767 grupos de archivos.

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 consultas 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. 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á.

Mover archivos

En SQL Server 2005 o posterior, 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

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.

Ejemplos

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

USE master;
GO
ALTER DATABASE AdventureWorks2008R2 
ADD FILE 
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\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 AdventureWorks2008R2 y se agregan dos archivos de 5 MB al grupo de archivos.

USE master
GO
ALTER DATABASE AdventureWorks2008R2
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2008R2 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\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 AdventureWorks2008R2.

USE master;
GO
ALTER DATABASE AdventureWorks2008R2 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\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 AdventureWorks2008R2
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 AdventureWorks2008R2 
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.

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 AdventureWorks2008R2 en OFFLINE y después en ONLINE para implementar el cambio.

USE master;
GO
ALTER DATABASE AdventureWorks2008R2
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 reinicie el servicio, tempdb continúa funcionando en su ubicación existente.

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

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

3. Detenga y reinicie la instancia de SQL Server.

4. Compruebe el cambio de los archivos.

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   

5. 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 AdventureWorks2008R2 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2008R2 
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

I. Agregar un grupo de archivos mediante ALTER DATABASE

En el ejemplo siguiente se agrega un FILEGROUP que contiene la cláusula FILESTREAM a la base de datos FileStreamPhotoDB.

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause to
--the FileStreamPhotoDB database.
ALTER database FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM
GO

--Add a file for storing database photos to FILEGROUP 
ALTER database FileStreamPhotoDB
ADD FILE
(
    NAME= 'PhotoShoot1',
    FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot
GO

Vea también

Referencia

CREATE DATABASE (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DROP DATABASE (Transact-SQL)

sp_spaceused (Transact-SQL)

sys.databases (Transact-SQL)

sys.database_files (Transact-SQL)

sys.data_spaces (Transact-SQL)

sys.filegroups (Transact-SQL)

sys.master_files (Transact-SQL)

Otros recursos

Diseñar e implementar almacenamiento FILESTREAM