Modelos de recuperación en SQL Server

Resumen: en este tutorial, aprenderás sobre el modelo de recuperación en SQL Server, incluido el registro simple, completo y masivo.

Introducción al modelo de recuperación de SQL Server

El modelo de recuperación de una base de datos en SQL Server es una propiedad fundamental que determina cómo se registran y protegen las transacciones. En este artículo, exploraremos los tres modelos de recuperación disponibles: Simple, Completo y Registro masivo, y te guiaremos en la comprensión de cada uno de ellos y su impacto en la gestión de tus bases de datos.

A través de este recorrido, descubrirás cómo elegir el modelo de recuperación adecuado según las necesidades de tu aplicación y los requisitos de seguridad. Aprenderás las ventajas y desventajas de cada modelo, así como las mejores prácticas para garantizar la integridad de tus datos y la eficiencia de tus operaciones de respaldo y recuperación.

¡Prepárate para adentrarte en el fascinante mundo de los modelos de recuperación de SQL Server y maximizar el rendimiento y la seguridad de tus bases de datos!

Un modelo de recuperación es una propiedad de una base de datos. Un modelo de recuperación controla lo siguiente:

• Cómo SQL Server registra las transacciones para la base de datos.
• Si el registro de transacciones de la base de datos requiere una copia de seguridad.
• Qué tipo de operaciones de restauración están disponibles para restaurar la base de datos.

SQL Server le proporciona tres modelos de recuperación:

• Simple
• Completo
• Registro masivo

Cuando creas una nueva base de datos, SQL Server usa la base de datos modelo para establecer el modelo de recuperación predeterminado de la nueva base de datos.

Crear una base de datos de muestra para la demostración

Primero, crea una nueva base de datos llamada HR:

CREATE DATABASE HR;

En segundo lugar, cambia la base de datos actual a HR:

USE HR;

En tercer lugar, crea una nueva tabla People en la base de datos de recursos humanos:

CREATE TABLE People (
  Id int IDENTITY PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL
);

Finalmente, inserta algunas filas en la tabla People:

INSERT INTO People (FirstName, LastName)
  VALUES ('John', 'Doe'),
  ('Jane', 'Doe'),
  ('Upton', 'Luis'),
  ('Dach', 'Keon');

A continuación, se muestra el script completo:

CREATE DATABASE HR;
GO

USE HR;

CREATE TABLE People (
  Id int IDENTITY PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL
);

INSERT INTO People (FirstName, LastName)
  VALUES ('John', 'Doe'),
  ('Jane', 'Doe'),
  ('Upton', 'Luis'),
  ('Dach', 'Keon');

SELECT * FROM People;

Visualización del modelo de recuperación de una base de datos

Para ver el modelo de recuperación de la base de datos de HR, utiliza la siguiente consulta:

SELECT
  name,
  recovery_model_desc
FROM master.sys.databases
WHERE name = 'HR';

Resultado:

La base de datos HR tiene el modelo de recuperación FULL.

Para ver el modelo de recuperación de todas las bases de datos en el servidor actual, utiliza la siguiente consulta:

SELECT
  name,
  recovery_model_desc
FROM master.sys.databases
ORDER BY name;

Cambiar el modelo de recuperación

Para cambiar el modelo de recuperación a otro, utilice la siguiente instrucción ALTER DATABASE:

ALTER DATABASE database_name
SET RECOVERY recovery_model;

En esta declaración:

• Primero, se especifica el nombre de la base de datos que deseas cambiar el modelo de recuperación después de la palabra clave ALTER DATABASE.
• En segundo lugar, especifica el modelo de recuperación después de las palabras clave SET RECOVERY. El modelo de recuperación puede ser uno de los siguientes: SIMPLE, FULL y BULK_LOGGED.

El siguiente ejemplo cambia el modelo de recuperación de la base de datos de recursos humanos de FULL a SIMPLE:

ALTER DATABASE HR
SET RECOVERY SIMPLE;

Veamos cada modelo de recuperación en detalle.

Modelo de recuperación SIMPLE

En el modelo de recuperación SIMPLE, SQL Server elimina los registros de transacciones de los archivos de registro de transacciones en cada punto de control. Esto da como resultado archivos de registro de transacciones relativamente pequeños.

Además, en el modelo de recuperación SIMPLE, los registros de transacciones no almacenan los registros de transacciones. Por lo tanto, no podrás utilizar estrategias de copia de seguridad avanzadas para minimizar la pérdida de datos.

En la práctica, utilizar el modelo de recuperación SIMPLE para la base de datos que podría recargarse desde otras fuentes, como bases de datos, para fines de generación de informes.

Modelo de recuperación FULL

En el modelo de recuperación FULL, SQL Server mantiene los registros de transacciones en los archivos de registro de transacciones hasta que se ejecuta la instrucción BACKUP LOG. En otras palabras, la instrucción BACKUP LOG elimina los registros de transacciones de los archivos de registro de transacciones.

Si no ejecuta la instrucción BACKUP LOG regularmente, SQL Server conserva todos los registros de transacciones en los archivos de registro de transacciones hasta que los archivos de registro de transacciones estén llenos y no se pueda acceder a la base de datos. Esta es la razón por la que debe ejecutar la instrucción BACKUP LOG a intervalos regulares para evitar que los archivos de registro de transacciones se llenen.

En resumen, el modelo de recuperación FULL permite restaurar la base de datos en cualquier momento.

Modelo de recuperación BULK_LOGGED

El modelo de recuperación BULK_LOGGED tiene casi los mismos comportamientos que el modelo de recuperación FULL excepto por las operaciones de registro masivo. Por ejemplo, la BULK INSERT de archivos planos en tablas se describe brevemente en los archivos de registro de transacciones.

El modelo de recuperación BULK_LOGGED no permite restaurar la base de datos en ningún momento. Un escenario práctico de la recuperación BULK_LOGGED es el siguiente:

• Antes de la carga periódica de datos, establece el modelo de recuperación en BULK_LOGGED
• Cargar los datos en la base de datos.
• Vuelve a establecer el modelo de recuperación en FULL después de completar la carga de datos
• Copia de seguridad de la base de datos

La siguiente tabla muestra las características de todos los modelos de recuperación:

Resumen

• Un modelo de recuperación es una propiedad de una base de datos que controla cómo se registran las transacciones.
• Un modelo de recuperación puede ser uno de los siguientes: SIMPLE, FULL y BULK_LOGGED.
• Utiliza el modelo de recuperación SIMPLE para las bases de datos cuyos datos se pueden recargar desde otras fuentes.
• Utiliza el modelo de recuperación FULL si deseas recuperar la base de datos en cualquier momento.
• Utilice el modelo de recuperación BULK_LOGGED para las operaciones de registro masivo como BULK INSERT.