Estrategias de recuperación de datos en SQL Server Francisco Otero Otero

ESTRATEGIAS DE RECUPERACIÓN DE DATOS EN SQL SERVER Nivel: Intermedio por Francisco Otero Otero

A pesar de los años que el sistema de backup y restoring de SQL Server lleva sin sufrir cambios en sus opciones básicas, es incomprensiblemente frecuente encontrar sistemas en producción con datos sensibles funcionando en el modelo de recuperación simple.

Es cierto, no todos los sistemas necesitan un nivel de protección total: existen aplicativos donde un mecanismo que asegure la recuperación de la base de datos a una versión de hace 24h es aceptable.

En la mayoría de casos tener que volver a una versión de hace un día significará cómo mínimo la pérdida del trabajo de ese día. No pensemos en pérdida de datos como consecuencia de un cataclismo o una desgracia natural. Basta un desafortunado despiste en la ejecución de una consulta en el servidor para poder echar a perder una tabla entera.

La correcta configuración de nuestras copias de seguridad nos puede evitar todos estos riesgos, sin apenas contraindicaciones.

El objetivo de este manual es describir de forma precisa y resumida los procedimientos de actuación ante los siguientes supuestos:

Recuperación de una base de datos a una fecha y hora concreta de cualquier día de la semana en curso.

Recuperación de una base de datos a una fecha y hora concreta del día actual.

Marcado de transacciones en el servidor de base de datos y recuperación a una transacción con nombre.

Sistema de Backup Una base de datos puede estar en dos modos de recuperación: simple y completa. En el modelo de recuperación simple las copias de seguridad nos permiten volver a puntos concretos en el tiempo. Es decir, con una estrategia de backup completo diario, guardando la de los últimos siete días podemos volver a los puntos dónde se han hecho dichos backups.

En un sistema de recuperación completa, la protección ante desastres es total, lo cual se visualiza en este gráfico que representa la “exposición a pérdida de datos” en un sistema con copias de seguridad completas, diferenciales y de log de transacciones:

El modelo puede ser tan complejo cómo necesitemos: existen modelos con copias de seguridad de log de transacciones cada minuto.

Para frecuencias inferiores recurriríamos a otros sistemas de alta disponibilidad, como puede ser LogShipping o Mirroring.

El modelo que se presenta en este artículo es mucho más sencillo: consta de un único log backup justo antes de cada full backup. Ambas copias de seguridad se harán diariamente con el nombre del día de la semana en el que se ejecutan, y dentro del mismo trabajo. Se utilizarán archivos distintos para facilitar la administración, asignando como nombre de archivo el nombre del día de la semana con extensión .bak para el full backup y .trn para el del log de transacciones.

El gráfico representa en qué momento se guardan los datos. Con las copias de seguridad completas (.bak) podríamos volver a puntos concretos en el tiempo; en este caso a los momentos en que se realizan las copias de seguridad. En el sistema propuesto programaríamos las copias de seguridad para ejecutarse una vez al día a las 4:00 am, que es una hora de poca actividad en el servidor.

Para volver a un punto en el tiempo distinto a las 4:00, tenemos que utilizar el log de transacciones (archivos .trn) correspondiente. Por ejemplo, para volver al jueves a las 15:30 necesitaríamos utilizar el BBDD1_Mi.bak para retroceder hasta el jueves a las 4:00 y después aplicar el BBDD1_Ju.trn para avanzar hasta las 15:30.

Si el punto al que queremos volver aún no está respaldado en ninguna copia de log de transacciones, siempre podremos generar uno cuando lo necesitemos. Se trata del archivo que en el gráfico aparece cómo tail.trn. Este archivo nos permite partir del backup completo del viernes a las 4:00 y avanzar hasta el punto actual o cualquiera intermedio.

Recuperación – casos prácticos

Punto en el tiempo pasado Supongamos que hoy es viernes, a las 11 de la mañana y necesitamos restaurar una base de datos al lunes a las 14:30.

1. Abrimos el menú de restauración de BBDD. Seleccionamos el backup completo más reciente anterior al punto de restauración deseado. En este caso será el archivo BBDDx_Lu.bak, que restaura la BBDD al lunes 4:00 am.

2. Seleccionamos un nuevo nombre para la base de datos restaurada, para no sobrescribir la actual. Es aconsejable esta técnica para poder comparar las versiones de la base de datos y recuperar sólo lo que nos interese mediante una consulta, en lugar de revertir toda la base de datos y perder todos los cambios existentes.

3. Puesto que esta restauración todavía no dejará a la base de datos en el punto deseado, marcamos la segunda opción en las opciones del estado de recuperación: “Dejar la base de datos no operativa...” (ver siguiente figura). Esto nos permite que la base de datos quede en un estado de restauración para seguir con el proceso. Al efectuar esta operación sobre una nueva base de datos no tendremos ninguna incidencia, en cambio si se trata de hacer esta restauración sobre una base de datos en uso necesitaríamos antes obtener acceso exclusivo.

4. Ahora la base de datos está exactamente igual que estaba en el punto que hicimos el backup, incluyendo las transacciones sin confirmar, de ahí que no esté operativa. Para avanzar hasta la hora que nos interesaba tenemos que recurrir al log de transacciones

del día correspondiente. Botón derecho sobre la base de datos, y en el menú contextual vamos a Tasks·Restore·Transaction Log (en español Tareas·Restaurar·Log de Transacciones), como se muestra en la figura siguiente:

5. Puesto que estamos en el lunes a las 4:00 y queremos avanzar hasta el lunes a las 15:30 utilizamos el archivo de logs del martes: BBDD1_Ma.trn. No necesitamos restaurar todo el archivo, sino sólo hasta la hora que queremos.

6. En esta ocasión dejamos la opción por defecto “dejar la base de datos lista para su uso revirtiendo las transacciones no confirmadas”, ya que alcanzamos el punto deseado.

¡Listo!

Acabo de ejecutar un Delete sin Where, ¿qué hago? El sistema anterior nos permite volver a un punto anterior que esté “cubierto” partiendo de un log de transacciones y un backup completo previo. Si acabamos de hacer algo indebido, o queremos volver a un punto anterior en el día actual, tenemos un backup completo previo de la última noche, pero nos falta un log de transacciones que cubra las transacciones del día en curso.

1. Podemos crearlo siempre que queramos, lo único que hay que hacer es copia de seguridad del log de transacciones y marcar la opción “copy_only” (“Copia de seguridad de sólo copia”), de forma que esta copia no nos influya en modo alguno en la serie de backups programados diariamente.

A continuación procedemos de la misma forma que antes: restauración del último backup completo y restauración de este registro de transacciones hasta un punto concreto.

Siendo previsores: transacciones con nombre Cuando accedemos al servidor de bases de datos para lanzar alguna consulta de forma manual, deberíamos siempre tomar ciertas precauciones. Entre ellas la de anotar la fecha y hora exacta en la que lanzamos nuestra consulta, de forma que en caso de error sepamos restaurar la base de datos al punto exacto para dejarlo todo como estaba.

Si la base de datos está en uso, nuestra “hora exacta” puede no ser la de ejecución de la consulta, sino que es fácil que se hayan colado otras transacciones antes o después de lo que anotamos. Existe una forma más cómoda de identificar una transacción en el log para volver a ella cuando queramos: las transacciones con nombre.

1. Para crear una de estas transacciones utilizaremos la opción WITH MARK acompañando a la apertura de la transacción.

2. Para restaurar a una transacción con nombre utilizamos el procedimiento habitual:

restauramos a copia completa dejando la base de datos en estado de recuperación, y al restaurar el registro de transacciones elegimos la opción Transacción marcada en lugar de a un momento dado.

Referencias

Backup Under the Full Recovery Model

Backup Under the Simple Recovery Model

Recovery Model Overview

Using Marked Transactions (Full Recovery Model)

Copy-Only Backups

Acerca del autor Fran Otero es Ingeniero Industrial e instructor certificado de Microsoft. Lleva varios años impartiendo formación presencial de certificación en programación web, escritorio y SQL Server en Krasis. Puedes visitar su blog en http://geeks.ms/blogs/franotero

Acerca de campusMVP CampusMVP te ofrece la mejor formación en tecnología Microsoft a través de nuestros cursos online y nuestros libros especializados, impartidos y escritos por conocidos profesionales de Microsoft. Visita nuestra página en www-campusmvp.com

Reconocimiento - NoComercial - CompartirIgual (by-nc-sa): No se permite un uso comercial de este documento ni de las posibles obras derivadas, la distribución de las cuales se debe hacer con una licencia igual a la que regula esta obra original. Se debe citar la fuente.