Backups y restauraciones en SQL Server (III): T-SQL básico para la realización de backups

Vamos a ver en este artículo algo de transact SQL para la realización de los distintos tipos de backups.


Backups completos.    

Es necesario establecer un punto de referencia inicial, independientemente del modo de recuperación que vayamos a emplear. Para ello, crearemos un backup completo de nuestra BB.DD. La cláusula en T-SQL es:

BACKUP DATABASE Nombre_BBDD TO DISK = 'Unidad:\ruta\ficherobackup.bak' WITH INIT

Con el parámetro WITH INIT nos aseguraremos de que el fichero de backup contiene una única copia de seguridad ya que, por defecto, el comando BACKUP lo añade al fichero existente. De esta forma nos aseguramos que el fichero se sobreescribe.
En los modos Full-Recovery o Bulk-Logged Recovery, los backups de los logs son esenciales, no sólo por el propósito de recuperación, sino también para controlar el tamaño del registro de transacciones activo. El modo de recuperación simple es el único que elimina las transacciones periódicamente.
Si nunca se realiza un respaldo, el registro de transacciones de una BBDD en modo Full-Recovery o Bulk-Logged Recovery continuará creciendo hasta consumir todo el espacio disponible en disco. Y si el disco se queda sin espacio, la BB.DD. se parará.
El comando para realizar el backup del fichero de log es:

BACKUP LOG Nombre_Log_BBDD TO DISK = 'Unidad:\ruta\ficherobackup.trn'

Cada acción contra la BB.DD. se asigna a un Log Sequence Number (LSN). Para restaurar a un punto específico en el tiempo, debemos tener un continuo registro de LSNs.




Backups diferenciales.
La restauración de los bakups de transacciones tiende a ser una operación lenta, especialmente si nuestro backup completo es semanal, o incluso superior en su programación en el tiempo. Los backups diferenciales intentan decrementar el tiempo de recuperación. La cláusula T-SQL es:

BACKUP DATABASE Nombre_BBDD TO DISK = 'Unidad:\ruta\ficherobackup.dif' WITH DIFFERENTIAL, INIT

El backup "ficherobackup.dif" contiene todos los cambios realizados desde el último backup completo. Podemos utilizarlo durante el proceso de restauración en combinación a los backups del registro de transacciones. En primer lugar, restaurando el backup completo, seguido de la restauración del último diferencial, y a continuación restaurando cualquier log de transacciones posterior.
Consideraremos los siguientes elementos cuando hagamos uso de backups diferenciales:

• Si no realizamos backups completos frecuentemente, los backups diferenciales crecerán significativamente en tamaño para una BB.DD. operativa. Recordemos que un backup diferencial contiene todos los cambios desde el backup completo más reciente. Cuanto mayor sea el tiempo entre backups completos, más cambios recogerán en los diferenciales.
• Un backup diferencial está directamente ligado a un específico backup completo. La realización de un backup completo fuera del calendario habitual de backups puede hacer inservible una copia diferencial.
• A través del examen de la frecuencia de backups diferenciales, se puede establecer un plan de copias de seguridad. Cuando la BB.DD. sufre cambios frecuentemente, los backups diferenciales pueden consumir bastante espacio. Deberemos encontrar el equilibrio entre la velocidad de recuperación necesaria con respecto al espacio disponible.
  
   

NOTA: no debemos confundir los backups diferenciales con los incrementales. Los diferenciales incluyen todos los datos que han cambiado desde el último backup completo. Mientras, el incremental incluye todos los datos que han cambiado desde el último diferencial o completo. SQL Server no dispone de ningún equivalente a los incrementales.




Verificación de errores.
El proceso de backup puede realizar una verificación de los datos mientras se están respaldando ya sea verificando páginas dañadas o validando por checksums.
Debemos habilitar cualquier opción que deseemos en el nivel de la base de datos. La opción "Verificación de páginas" nos permitirá descubrir e informar sobre transacciones de E/S incompletas debidas a errores de E/S de disco. Podremos elegir entre las opciones "Ninguna", "checksum" y "TornPageDetection".

 

La opción "TornPagesDetection" (detección de páginas dañadas) verifica simplemente cada página de datos para ver si un proceso de escritura se ha completado en su totalidad. Si encuentra una página que ha sido sólo parcialmente escrita (debido a algún tipo de fallo hardware) simplemente se marca como "dañado".
La validación por comprobación de sumas ("Checksum") es una técnica de verificación de páginas que añade un valor para cada página de datos, esencialmente identificando el tamaño exacto en bytes de cada página. El proceso de backup puede validar por checksum comparando el valor almacenado en la base de datos con el valor asociado con la página de datos escrita en disco. Sin embargo, no lo hace por defecto. Si la validación por checksums está habilitada, podemos forzar el proceso de backup para realizar está validación.

BACKUP DATABASE Nombre_BBDD TO DISK = 'unidad:\ruta\fichero.back' WITH CHECKSUM

Cuando se encuentre con un error durante la validación por checksum, SQL Server escribirá un registro a MSDB..SUSPECT_PAGE. El comportamiento por defecto es STOP_ON_ERROR, permitiendo corregir el problema y continuar, lanzando el mismo comando con la adicción de RESTART.
La otra opción de validación por checksum es CONTINUE_ON_ERROR. Cuando está habilitada, el backup simplemente escribe el error en la tabla MSDB..SUSPECT_PAGE y continua. Sin embargo, esta tabla tiene un límite de 1000 filas, y si se alcanza, el backup fallará.
El habilitar la validación por checksum obviamente tiene un impacto en el rendimiento del proceso de backup, por lo que deberemos tener un ventana suficientemente grande para realizar las copias de seguridad.
Examinaremos la tabla MSDB..SUSPECT_PAGE para buscar el enfoque adecuado para hacer frente a los errores.

Backups divididos (striped).
Algunas BB.DD.s son demasiado grandes para crear un backup completo en una única cinta LTO o en un array de discos. En estos casos, podemos hacer uso de los backup striped, también denominados multiplexados. La ventaja es que casa dispositivo utiliza la totalidad de su capacidad para crear el backup. Su desventaja es que, en caso de fallo, todas las cintas o ficheros se necesitarán para completar una restauración.
Para crear un backup striped utilizaremos:

BACKUP DATABASE Nombre_BBDD TO DISK = 'unidad1:\ruta1\fichero1.bak' , 'unidad2:\ruta2\fichero2.bak', 'unidad3:\ruta3\fichero3.bak' WITH INIT, CHECKSUM, CONTINUE_ON_ERROR

El backup será "extendido" a través de todos los ficheros indicados.




Backups en espejo (mirrored).
Los backups en espejo son una característica incorporada desde la versión 2005 de SQL Server, y que nos permite escribir el mismo fichero de backup en múltiples ubicaciones. El comando para crear un backup en espejo es

BACKUP DATABASE Nombre_BBDD TO DISK = 'unidad1:\ruta1\fichero1.bak' MIRROR TO DISK = 'unidad2:\ruta2\fichero2.bak' MIRROR TO DISK = 'unidad3:\ruta3\fichero3.bak' WITH INIT, CHECKSUM, CONTINUE_ON_ERROR

La única restricción para realizar respaldos en múltiples localizaciones es que los dispositivos utilizados deben ser idénticos. En particular, múltiples dispositivos de cinta deben ser del mismo modelo del mismo fabricante.




Backups Sólo_copia (copy_only).
Podemos utilizar backup para propósitos distintos a los de recuperación en caso de desastre. Por ejemplo, un uso típico es utilizar un backup para mover una copia de la BBDD a un entorno de desarrollo. Como ya hemos indicado, los backups diferenciales están asociados directamente a un único backup completo. Desde SQL Server 2005 existe una característica, el backup "copy-only", que no resetea la cadena de backups. Cualquier copia de seguridad realizada fuera del esquema de los backups estándar debería ser realizada como un backup "only-copy":

BACKUP DATABASE Nombre_BBDD TO DISK = 'unidad:\ruta\ficheroBackup.bk' WITH INIT, CHECKSUM, COPY_ONLY

Si existe un calendario de respaldos en SQL Server, para cada respaldo creado existe un número de secuencia o LSN. Entonces si creamos un backup completo sin hacer uso de la opción "copy-only", esa secuencia se verá afectada y tendremos problemas para recuperar un respaldo diferencial realizado posteriormente. Necesitaremos contar con el respaldo completo que se realizó en medio de la secuencia entonces no podremos recuperar la información.
Si queremos conocer el número de secuencia podemos lanzar esta query:

SELECT database_name, backup_start_date, is_copy_only, first_lsn FROM msdb..backupset WHERE database_name = ''Nombre_BBDD' ORDER BY backup_start_date DESC