Modos de Recuperación: Guía Completa

Uno de los aspectos más cruciales de las bases de datos SQL Server y, a menudo, complejos para los que se están iniciando son los modos de recuperación. En este artículo, voy a tratar de profundizar en los detalles de los modos de recuperación de SQL Server, detallando sus características, usos y mejores prácticas para optimizar el rendimiento y la seguridad de nuestras bases de datos. Espero que tanto si estáis empezando a trabajar con bases de datos como si sois ya experimentados podáis aprender algo nuevo.

¿Qué son los Modos de Recuperación en SQL Server?

Empecemos por el principio, en SQL Server, los modos de recuperación determinan cómo se gestiona el log de transacciones y cómo se pueden restaurar las bases de datos tras un fallo. Comprender estos modos es esencial para garantizar la integridad de los datos y minimizar el tiempo de inactividad en caso de incidentes. En este sentido, vamos a poder trabajar con tres modos distintos, simple, full (completo) o bulk-logged (registro masivo).

Tipos de Modos de Recuperación

Como acabamos de ver existen tres modos principales de recuperación en SQL Server: Simple, Full y Bulk-Logged. Cada uno de estos modos tiene sus propias características y escenarios de uso.

Modo de Recuperación Simple

El modo de recuperación simple es el más básico de los tres. En este modo, el espacio utilizado por el registro de transacciones se reutiliza automáticamente después de cada punto de control (checkpoint), lo que significa que no se requiere una gestión detallada del log.

Esto tiene una serie de ventajas como la simplicidad de no requerir una administración exhaustiva del log de transacciones o un menor tamaño del log al reutilizar el espacio. Siempre se mantiene el tamaño del log relativamente pequeño mientras no hagamos transacciones descomunales. Sin embargo, también tiene sus contras. Como desventajas nos vamos a encontrar con una menor capacidad de copias de seguridad ya que solo vamos a poder hacer copias completas o diferenciales. Esto, por supuesto, se traduce en una menor capacidad de recuperación a la hora de necesitar una restauración. En estos casos es probable que suframos una mayor pérdida de datos. En caso de fallo, los datos desde el último respaldo hasta el momento del fallo se perderán, esto es así siempre pero, en el caso de otros modos de recuperación con copias de logs, este tiempo suele ser menor.

Como habrás podido comprobar este modo es ideal para bases de datos que no necesitan un alto grado de recuperación de datos, como bases de datos de desarrollo o pruebas o entornos de producción sin gran cantidad de cambios o sin mucha criticidad.

Modo de Recuperación Full

El modo de recuperación completo, el modo por defecto de todas las nuevas bases de datos de SQL Server y Azure, ofrece el mayor nivel de protección para los datos. En este modo, cada transacción se registra completamente, y se puede realizar una restauración a cualquier punto en el tiempo. Para lograr esto el log de transacciones almacena las transacciones sin borrarlas incluso cuando han terminado y solo se borran cuando ya han sido salvadas en una copia de seguridad de log.

Como ventajas a este comportamiento podemos encontrar una capacidad de copias y por tanto de recuperación más granular. Una base de datos en modo de recuperación Full nos va a permitir la restauración de la base de datos a un punto concreto en el tiempo, minimizando la pérdida de datos. Por supuesto esta mayor capacidad de copias tiene otra ventaja añadida y es la flexibilidad a la hora de diseñar nuestra solución de backups. Al soportar backups completos, diferenciales y de log de transacciones vamos a poder adaptar nuestro plan de copias a nuestros procesos de manera que no interfieran en el rendimiento.

No todo es tan bonito, claro, a cambio tendremos una gestión de los log más compleja que requerirá más cuidado y vigilancia por nuestra parte para evitar que crezca demasiado.

Esto puede ser más complicado de administrar debido a la necesidad de realizar backups de log regulares.

Como ves, este modo es el indicado para bases de datos de producción críticas donde la pérdida de datos debe ser mínima y disponen de un administrador de base de datos que diseña y supervisa el plan de copias de seguridad para evitar incidencias. 

Modo de Recuperación por Bulk-Logged

El modo de recuperación de registro masivo, es una opción intermedia entre el Modo Simple y el Modo Completo. Está diseñado para optimizar las operaciones masivas de carga de datos. Este modo minimiza el uso del espacio del log de transacciones cuando se ejecutan operaciones masivas como BULK INSERT, SELECT INTO o CREATE INDEX. Funciona de manera similar al Modo de Recuperación Completo, con la excepción de que los registros de transacciones se registran mínimamente durante las operaciones masivas. Esta forma de registro mínimo ayuda a mantener el log más pequeño al no registrar tanta información.

Gracias a estas optimizaciones, mejora la eficiencia en las operaciones masivas como la carga de datos con BULK INSERT que serán más rápidas y generarán menos registros en el log. Todo esto lo consigue sin perder la flexibilidad ya que ofrece algunas capacidades de recuperación punto en el tiempo, similares al modo full. Es importante remarcar ese algunas capacidades ya que cuando estamos haciendo una operación de carga masiva de datos no vamos a tener registros en el log de transacciones de esa operación lo que va a afectar a la posibilidad de recuperar en ese punto en el tiempo en concreto. Además, aún  requiere una gestión adecuada del log de transacciones igual que el modo full. 

A tener en cuenta

A pesar de que las recuperaciones punto en el tiempo pueden realizarse en algunas situaciones, si la base de datos se daña mientras se realiza una operación de carga masiva, solo se puede recuperar hasta el último backup del log de transacciones creado antes de la operación masiva. Si no se realizan operaciones de carga masiva mientras se utiliza este modo, entonces se puede realizar una restauración punto en el tiempo de manera similar al modo de recuperación completo. Para que podamos minimizar la pérdida de datos al realizar operaciones de carga masiva, es recomendable realizar un backup del log de transacciones justo antes de la operación masiva y otro inmediatamente después de que la operación haya finalizado. De esta manera, se puede realizar una recuperación punto en el tiempo utilizando los backups del log de transacciones tomados antes y después de la operación de carga masiva.

Elección del Modo de Recuperación Adecuado

La elección del modo de recuperación adecuado depende de varios factores, incluyendo los requisitos de recuperación de datos, la frecuencia de cambios en los datos y la capacidad de gestionar los backups del log, como ya hemos comentado anteriormente.

• Requisitos de Recuperación: Si necesitamos la capacidad de recuperar la base de datos hasta un punto específico en el tiempo, el modo full es imprescindible. Si la pérdida de algunos datos es aceptable, el modo simple puede ser suficiente y nos va a simplificar mucho la tarea.
• Frecuencia de Cambios: Las bases de datos con cambios frecuentes pueden beneficiarse del modo completo, mientras que aquellas con menos cambios pueden utilizar el modo simple o el Modo en Bulk-Logged.
• Capacidad de Gestión: La capacidad de gestionar backups regulares y el tamaño del log de transacciones también influye en la elección. El modo completo requiere una mayor gestión, mientras que el modo simple es más fácil de manejar.

Mejores Prácticas para la Gestión de Modos de Recuperación

Como con todos los temas importantes, existen una serie de buenas prácticas que no debemos descuidar. En el caso de los modos de recuperación estas recomendaciones pasan por:

1. Realizar Backups Regulares: Independientemente del modo de recuperación, es crucial realizar backups completos de manera regular. Estos se podrán complementar con otros backups como diferenciales o log en función de las necesidades y del modo de recuperación elegido.
2. Monitorizar el Tamaño del Log: Especialmente en el modo full y Bulk-Logged, es importante monitorear y gestionar el tamaño del log de transacciones para evitar que crezca descontroladamente.
3. Probar Restauraciones: Realizar pruebas de restauración periódicas para asegurar que los backups son funcionales y los datos se pueden recuperar según lo planeado. Esto, que muchas veces se pasa por alto pues un backup no testeado puede dejarnos tirados cuando más lo necesitemos.
4. Documentar Procedimientos: Mantener una documentación detallada de los procedimientos de backup y restauración para asegurar una respuesta rápida y efectiva en caso de fallo es clave, como ya vimos en el post sobre la recuperación ante desastres.

¿Qué modo estoy usando? ¿Cómo lo cambio?

Existen varios métodos para averiguar el modo de recuperación actual de una base de datos y poder cambiarlo. El primero y más sencillo es a través de la interfaz gráfica del SSMS. Para ello haremos clic derecho sobre una base de datos y abriremos sus propiedades. Ya en las propiedades nos dirigiremos a las opciones para encontrar el modo de recuperación actual y poder cambiarlo si lo deseamos. 

Otra forma de verlo es consultando la vista de sistema sys.databases donde vamos a encontrar una columna llamada recovery_mode_desc con esta información.

SELECT
   name,
   recovery_model_desc
FROM sys.Databases

Si deseamos modificar el modo de recuperación por código T-SQL usaremos estas sintaxis para el modo simple, full o bulk-logged respectivamente

USE master
GO
ALTER DATABASE NombreBaseDatos SET RECOVERY SIMPLE
GO

USE master 
GO
ALTER DATABASE NombreBaseDatos SET RECOVERY FULL
GO

USE master
GO
ALTER DATABASE NombreBaseDatos SET RECOVERY BULK_LOGGED
GO

Conclusión

Los modos de recuperación en SQL Server son un aspecto clave en la estrategia de administración de bases de datos. Elegir el modo adecuado y gestionarlo de manera efectiva pueden marcar la diferencia entre una recuperación exitosa y una pérdida de datos importante. Comprender las ventajas y desventajas de cada modo y aplicar las mejores prácticas nos asegurará la integridad y disponibilidad de nuestros datos, garantizando así un rendimiento óptimo y una respuesta eficiente ante cualquier incidente.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!