Cómo crear una copia de seguridad de una base de datos sin el índice de datos

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. Nosotros también mostraremos cómo usar la solución ApexSQL Backup para copias de seguridad de grupos de archivos como una primera parte de la serie en la cual mostraremos cómo respaldar y restaurar una base de datos sin índices, y crear los índices después de una restauración.

¿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:

  1. Haga clic derecho en una base de datos y seleccione Tasks -> Backup:

  2. En el diálogo Back Up Database seleccione la opción Files and filegroups y especifique un grupo de archivos deseado:

  3. 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:

    1. En el panel Object Explorer, haga clic derecho en el nodo Management y seleccione la opción New Maintenance Plan::

    2. En el diálogo New Maintenance Plan nombre un nuevo plan:

    3. Desde la barra de herramientas Maintenance Plan Tasks seleccione Back Up Task:

    4. 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:

    5. Para programar Back Up Database Task use el programador de SQL Server Agent:

    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.

    Cómo respaldar una base de datos sin los datos de índice usando ApexSQL Backup

    Para usar una opción de programación sin código adicional, y poder tener una solución completa sin complicaciones adicionales cuando se respalde y restaure grupos de archivos, lo cual será el tópico de nuestro siguiente artículo, ApexSQL Backup es una herramienta de respaldos SQL que permite manejar y automatizar los trabajos de copias de seguridad; adicionalmente, ofrece automatización de copias de seguridad de Archivos y grupos de archivos de bases de datos.

    Para respaldar una base de datos sin los datos de índice usando ApexSQL Backup:

    1. Seleccione la opción Backup del menú principal:

    2. En el diálogo Backup Wizard establezca la instancia SQL Server y seleccione una base de datos para respaldar. Seleccione la opción Filesgroups:

    3. Seleccione “…” que se mostrará después de selecciona la opción Filegroups y establecer los grupos de archivos deseados para una copia de seguridad:

    4. El siguiente paso es establecer un nombre y un destino para la copia de seguridad:

    5. Seleccione las opciones deseadas para la copia de seguridad de los grupos de archivos de la base de datos:

    6. ApexSQL Backup ofrece opciones para programar un trabajo de copia de seguridad de la base de datos o ejecutar la acción inmediatamente. Para programar un trabajo de copia de seguridad seleccione la opción Schedule, establezca la frecuencia deseada y haga clic en el botón Create:

    7. Todas las actividades programadas serán mostradas haciendo clic en el botón Schedules en la pestaña View:

    8. Seleccionando la vista Schedules el usuario puede ver todos los programas creados:

    Traductor: Daniel Calbimonte

    agosto 16, 2016