SQL

Modelos de recuperación en SQL Server

Estrada Web Group
Estrada Web Group
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

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.

Vamos a 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:

name recovery_model_desc
HR FULL

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:

Modelo de recuperación Descripción Pérdida de datos Punto de Recuperación
Simple Sin copias de seguridad de registros Se pierden los cambios desde la copia de seguridad más reciente Puedes recuperar solo hasta el final de una copia de seguridad.
Full Genera copias de seguridad de registros Ninguna perdida Puedes recuperar hasta un punto específico en el tiempo con la suposición de que las copias de seguridad están completas hasta ese punto en el tiempo.
Bulk Logged Genera copias de seguridad de registros

Si el registro está dañado o se produjeron operaciones de registro masivo desde la última copia de seguridad del registro, se deben volver a realizar los cambios desde la última copia de seguridad.

De lo contrario, no se pierde ningún trabajo.

Puede recuperar hasta el final de cualquier copia de seguridad.
No se admite la recuperación de un momento dado.

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.
Compartir artículo:

Más artículos geniales

Columnas calculadas en SQL Server

Columnas calculadas en SQL Server

Resumen: en este tutorial, aprenderá a usar las columnas calculadas de SQL Server para reutilizar la lógica de cálculo en varias consultas.

Ver artículo completo
Cómo crear índices en SQL Server con la instrucción CREATE INDEX

Cómo crear índices en SQL Server con la instrucción CREATE INDEX

Resumen: en este tutorial, aprenderás a usar la instrucción CREATE INDEX de SQL Server para crear índices para tablas.

Ver artículo completo
¿Cómo modificar Schema en SQL Server con ALTER SCHEMA?

¿Cómo modificar Schema en SQL Server con ALTER SCHEMA?

Resumen: en este tutorial, aprenderás a usar ALTER SCHEMA de SQL Server para cambia la definición de un esquema existente. Utiliza este comando para cambiar un objeto de un esquema a otro.

Ver artículo completo

Manténgase actualizado

Obtenga excelente contenido en su bandeja de entrada todas las semanas.
Solo contenido excelente, no compartimos su correo electrónico con terceros.
Subir al inicio de la pantalla