Este artículo explica cómo crear grupos de archivos y mover índices a un diferente grupo de archivos, y cómo crear una copia de seguridad de base de datos sin índices con el propósito de reducir la cantidad de datos requeridos para realizar copias de seguridad, por tanto reduciendo el tiempo de la creación del respaldo y el espacio requerido.
¿Por qué usar este enfoque?
En desarrollo de bases de datos, una copia de seguridad de base de datos es usualmente creada para propósitos de verificación y pruebas del proceso de respaldo. Respaldar una base de datos completa requiere tiempo y espacio. También, cuando se implementa el transvase de registros el usuario tiene que hacer una copia de seguridad de la base de datos, transferirla a través de la red y restaurarla, lo cual es un proceso que consume tiempo.
Respaldar sólo datos sin índices reduciría el tiempo y espacio requerido para una copia de seguridad significativamente.
¿Cómo implementar este enfoque?
Para implementar este enfoque el usuario debería crear nuevos grupos de archivos y almacenar los datos e índices en grupos de archivos separados.
Cada base de datos tiene un grupo de archivos PRIMARIO que contiene el archivo de datos primario (la extensión recomendada de archivo del archivo primario es .mdf) y todos los otros archivos que no son puestos en grupos de archivos secundarios.
Los grupos de archivos pueden ser añadidos ya sea directamente desde SQL Server Management Studio o usando scripts T-SQL.
Para crear e indexar grupos de archivos, ejecute el siguiente script T-SQL:
ALTER DATABASE AdventureWorks2014 ADD FILEGROUP IndexesFG GO
Para revisar los grupos de archivos creados ejecute el siguiente script T-SQL:
USE AdventureWorks2014 SELECT name AS [Filegroups] FROM sys.filegroups WHERE type = 'FG'
Moviendo los índices a otro grupo de archivos
Para encontrar todos los índices no agrupados en una base de datos, use el siguiente script:
SELECT sys.tables.name AS TableName, sys.indexes.name AS IndexName, sys.indexes.is_primary_key AS PrimaryKey, sys.indexes.type_desc AS IndexType FROM sys.indexes, sys.tables WHERE sys.indexes.object_id = sys.tables.object_id AND sys.indexes.type = 2 ORDER BY TableName
Para encontrar todos los indices agrupados sólo cambie el valor AND sys.indexes.type = 2 value en AND sys.indexes.type = 1
Los índices agrupado pueden/deben estar en un grupo de archivos de datos y los no agrupados en uno separado si desea separar los datos y los índices porque si una tabla contiene un índice agrupado, mover el índice agrupado a un nuevo grupo de archivos también mueve la tabla al mismo grupo de archivos.
Otros tipos de índices son:
3 - Índices XML
4 - Índices espaciales
5 - Índices de almacén agrupados, que aplica sólo a SQL Server 2014 y SQL Server 2016
6 - Índices de almacén no agrupados, aplica a SQL Server 2012, SQL Server 2014 y SQL Server 2016
7 - Índices hash no agrupados, aplica a SQL Server 2014 y 2016
Mostraremos un ejemplo de mover un índice no agrupado a otro grupo de archivos, ya que no hay otra alternativa que codificar los índices, cambiar la cláusula ON, eliminar los índices y volver a correr el script nuevo. Hay muchos scripts gratis disponibles para lograr la codificación de todos los índices y moverlos a un grupo de archivos especificado y eso está fuera del alcance de este artículo.
Ejemplo:
/* Adds the IndexesFG file to the IndexesFG filegroup */ ALTER DATABASE AdventureWorks2014 ADD FILE ( NAME = TransIndexesFG, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SPOCK2014\MSSQL\DATA\IndexesFG.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP IndexesFG; GO /* Creates the IX_SpecialOfferProduct_ProductID index on the IndexesFG filegroup and drops the original IX_SpecialOfferProduct_ProductID index */ CREATE NONCLUSTERED INDEX IX_SpecialOfferProduct_ProductID ON Sales.SpecialOfferProduct (ProductID) WITH (DROP_EXISTING = ON) ON IndexesFG; GO
Cómo respaldar una base de datos sin los datos del índice usando SQL Server Management Studio
SQL Server Management Studio permite respaldar grupos de archivos específicos como una opción en una tarea de copia de seguridad de base de datos regular. Para respaldar una base de datos sin los datos de índice usando SQL Server Management Studio:
- Haga clic derecho en una base de datos y seleccione Tasks -> Backup:
-
En el diálogo Back Up Database seleccione la opción Files and filegroups y especifique un grupo de archivos deseado:
-
En el panel Object Explorer, haga clic derecho en el nodo Management y seleccione la opción New Maintenance Plan::
-
En el diálogo New Maintenance Plan nombre un nuevo plan:
-
Desde la barra de herramientas Maintenance Plan Tasks seleccione Back Up Task:
-
Haga doble clic en una tarea añadida, especifique una base de datos, seleccione la opción Files and filegroups, y especifique los grupos de archivos para respaldar:
-
Para programar Back Up Database Task use el programador de SQL Server Agent:
Este enfoque es directo, pero una desventaja es que no puede ser programado sin crear un script T-SQL y programarlo como un trabajo de SQL Server Agent.
Cómo respaldar una base de datos sin los datos de índice usando Planes de Mantenimiento
Respaldar los archivos y grupos de archivos deseados puede lograrse usando la Tarea Copia de seguridad de la base de datos.
Para crear una copia de seguridad de una base de datos sin los datos de índice usando Planes de Mantenimiento:
Aunque los Planes de Mantenimiento ofrecen una opción de programación, la barra de herramientas de Maintenance Plan Tasks no contiene una tarea de restauración, así que tener una solución completa requiere usar Execute T-SQL Statement Task.
Traductor: Daniel Calbimonte
agosto 16, 2016
